An AWR report is good for viewing the entire system’s performance and identifying the top resource-consuming SQL queries. Run the following script to generate an AWR report:
SQL> @?/rdbms/admin/awrrpt
You can run the AWR reports from the PDB or CDB, root container. The reports will be from PDB using AWR_PDB views in that PDB, or if you use AWR_ROOT views (which are like the DBA_HIST views), they show the AWR data stored only on a CDB root.
From the AWR output, you can identify top resource-consuming statements by examining the “SQL Ordered by Elapsed Time” or “SQL Ordered by CPU Time” section of the report.
Oracle will automatically take a snapshot of your database once an hour and populate the underlying AWR tables that store the statistics. By default, 7 days of statistics are retained.
You can also generate an AWR report for a specific SQL statement by running the awrsqrpt.sql report. When you run the following script, you will be prompted for the SQL_ID of the query of interest:
SQL> @?/rdbms/admin/awrsqrpt.sql
Using ADDM
The ADDM report provides useful information on which SQL statements are candidates for tuning. Use the following SQL script to generate an ADDM report:
SQL> @?/rdbms/admin/addmrpt
The report will recognize that the database is a pluggable if run from inside the PDB container.
Look for the section of the report labeled “SQL Statements Consuming Significant Database Time.” Here is some sample output:
FINDING 2: 29% impact (65043 seconds)
SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 6.7% benefit (14843 seconds)
ACTION: Investigate the SQL statement with SQL_ID “46cc3t7ym5sx0” for The ADDM report analyzes data in the AWR tables to identify potential bottlenecks and high resource-consuming SQL queries.
Using ASH
The ASH report allows you to focus on short-lived SQL statements that have been recently run and that may have executed only briefly. Run the following script to generate an ASH report:
SQL> @?/rdbms/admin/ashrpt
Search the output for the section labeled “Top SQL.” Here is some sample output:
Top SQL with Top Events DB/Inst: MMDB23C/mmdb23c (Jul 29 22:27 to 22:42) SQL ID FullPlanhash Planhash
14.29 CPU + Wait for CPU 14.29 SELECT STATEMENT 14.29
The previous output indicates that the query is waiting for CPU resources. In this scenario, the problem may be that another query is consuming the CPU resources.
When is the ASH report more useful than the AWR or ADDM report? The AWR and ADDM output shows top-consuming SQL in terms of total database time. If the SQL performance problem is transient and short-lived, it may not appear on the AWR and ADDM reports. In these situations, an ASH report is more useful.
Using Statspack
If you do not have a license to use the AWR, ADDM, and ASH reports, the free Statspack utility can help you identify poorly performing SQL statements. Run the following script as SYS to install Statspack in the PDB:
SQL> @?/rdbms/admin/spcreate.sql SQL> grant create job to PERFSTAT;
The prior script creates a PERFSTAT user that owns the Statspack repository. Once crated, then connect as the PERFSTAT user, and run this script to enable the automatic gathering of Statspack statistics:
SQL> @ ?/rdbms/admin/spauto.sql
After some snapshots have been gathered, you can run the following script as the PERFSTAT user to create a Statspack report:
SQL> @?/rdbms/admin/spreport.sql
Tip see the $ORACLE_HOME/rdbms/admin/spdoc.txt file for the statspack documentation.
Leave a Reply