- Information must be used only after minimum 6 hours of normal usage of the database
- Helpful for customized, specific and granule level of Performance Tuning
- Is the main source for all other sources.
- Example view
- v$systat
- v$system_event
- v$librarycache
- v$rowcache
- v$sga_dynamic_components
- v$SQL_Plan
- Example view
UTLBSTAT and UTLESTAT
- For backward compatibility
- Used to gather performance statistics of peak period of the database usage
- Used to Create the Objects to Maintain the Performance Information
- Generate a Report File and Drop the Objects
- Generates a file report.txt that contains the Performance Information
- Use scripts files to build and drop the schema objects
- $ORACLE_HOME/rdbms/admin is the location of the script files
- To Build: UTLBStat:
- ◦ File: utlbstat.sql
- To Drop: UTLEStat:
- ◦ File: utlestat.sql
- Report File: report.txt
- Path : pwd (present / parent working directory)
STATSPACK
- Introduced to overcome the drawback of the UTLBSTAT and UTLESTAT utilities
- A separate schema must be created to maintain the performance statistics and when not required can be dropped
- Can automate gathering of statistics in the form of snapshots and can maintain the snapshots for any duration
- Can customize it to collect statistics of performance
- Generate report file by comparing the snapshots of two different period of time involving the peak period of the database
- The report generate will have additional and descriptive information compared to report of UTLBSTAT and UTLESTAT utilities
- Use scripts files to build and drop the schema, automate and purge snapshots.
- $ORACLE_HOME/rdbms/admin is the location of the script files
- To Build Schema:
- ◦ File: spcreate.sql
▪ Used to create the Statspack schema.
▪ Prompts for: Password for perfstat user, default tablespace and temporary tablespace.
To Automate Snapshots creation:
◦ File: spauto.sql
▪ Used to automate capturing of snapshots.
To Generate Report:
◦ File: spreport.sql
▪ Used to generate a report based on the statspack snapshots.
▪ Prompts for: Start SnapID, End SnapID and Report FileName.
To Purge the Snapshots
◦ File: sppurge
▪ Prompts for: Start SnapID and End SnapID
To Drop Schema:
◦ File: spdrop.sql
▪ Used to drop the statspack
AWR – Automatic Workload Repository
- Configured and Enabled by default
- Influenced by STATISTICS_LEVEL initialization parameter
- The performance statistics is captured and stored in the form of Snapshots
- Unlike STATSPACK, The performance statistics of AWR is the source for
- ◦ Administrators
- ◦ Automatic Features
- ◦ Advistors
- ◦ ADDM
- Whereas STATSPACK statistics is used by administrators
- Report generated is in either html report file or text report file format
- Can have baseline for Moving Window or Fixed Period
- Can have baseline templates for capturing baseline for future in time
- The baseline templates is for either single time or repeated
- Can customize the AWR settings, Create and Manage Baselines, Create and Manage Baseline template using DBMS_* packages
- Can generate a report based on the snapshots captured and stored in AWR
- Use scripts files to generate reports
$ORACLE_HOME/rdbms/admin is the location of the script file
Execute File: awrrpt.sql
◦ Prompts for
▪ Type of report file: html / text
▪ Start Snap ID, End Snap ID and Name for the report file
ADDM – Automatic Database Diagnostic Manager
- Is an advanced feature.
- ADDM pin points the area of problem and give 4 piece information.
- Runs immediately after capturing of AWR snapshot and stores its result in AWR
- ADDM results and report depends on AWR and also the Time Model Statistics
- The 4 piece information is
- ◦ Description of the problem
- ◦ Root cause of the problem
- ◦ Possible Solutions for the problem
- ◦ Recommendation of a solution
- Can generate a report based on the snapshots captured and stored in AWR
- Use scripts files to generate reports
$ORACLE_HOME/rdbms/admin is the location of the script file
Execute File: addmrpt.sql
◦ Prompts for: Start Snap ID, End Snap ID and Name for the report file
Advisors
- Advisors depends on AWR and also take help of ADDM
- Influenced by the initialization parameter STATISTICS_LEVEL
- Provides advice for performance tuning
- Few important advisors
- ◦ SQL Tuning Advisor, for Manual or Automatic SQL Tuning
- ◦ DB Cache Advisor, for Buffer Cache Tuning
- ◦ SGA Advisor, for Auto SGA Tuning
- ◦ PGA Advisor, for Auto PGA Tuning
- ◦ Segment Advisor, for Table, Undo
- ◦ MTTR Advisor, for Redologfile sizing
