AWR helps to identify the problematic SQL which are reported as per the given snapshots. But, how does AWR determine the SQL to be printed ?
Not all the SQL are considered to be the nominees to be included in the AWR or ASH or ADDM report. Sometimes, even if a SQL has consumed lot of elapsed time, they are nowhere in the performance report. Often DBA’s have this misconception that if AWR does not include Top SQL for a PDB or ADDM doesnot include any recommendations or advise for any SQL or ADDM report shows this message ‘There was no significant database activity to run the ADDM. ‘, then this is not mostly a bug or incorrect report but expected behavior.
Activity During the Analysis Period
-----------------------------------
Total database time was 99 seconds.<============= DB time is only 99 sec which is not significant
The average number of active sessions was .01. <============= number of average active session is .01 which is too low
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 99% of the analysis
period.
There is an option to influence these parameters with dbms_workload_repository.awr_set_report_thresholds
Below are the parameters and its values
Parameters
top n events - the number of the most important wait events that will be considered
number of the most active files to be included, top n files
Number of the most active segments to be featured, top n segments
number of the most active services to be featured, top n services
top n sql is the number of the most important SQL statements that will be used.
Top n sql max is the maximum number of SQL statements that will be included if their activity exceeds the top sql pct threshold.
Between top n sql and top n max sql, top sql pct is the significance threshold for SQL statements.
shared memory low threshold, shmem threshold
versions threshold - low threshold for plan version count
A simple test case to demonstrate the behavior. This test case provides a sample
Enable AWR snapshots in PDB
SQL> startup;
ORACLE instance started.
Total System Global Area 3221222296 bytes
Fixed Size 9143192 bytes
Variable Size 654311424 bytes
Database Buffers 2550136832 bytes
Redo Buffers 7630848 bytes
Database mounted.
Database opened.
SQL> alter system set awr_pdb_autoflush_enabled=true;
System altered.
SQL> show parameter offset
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
awr_snapshot_time_offset integer 0
SQL> alter system set awr_snapshot_time_offset=1000000;
System altered.
Connect to the PDB
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB2 MOUNTED
SQL> alter pluggable database PDB2 open;
Pluggable database altered.
SQL> alter session set container=PDB2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB2
Specify the threshold for a parameter with sql threshold which contribute to x% of database performance, and x value is specified as 1% for easy simulation
exec dbms_workload_repository.awr_set_report_thresholds(top_sql_pct => 1); -- here i specified to include SQL which consume 1 % of the total database activity. Similarly, as per your database requirements, this percentage value can be modified.
Create a snapshot at beginning and end.
Execute a sample query with high parallelism and check if the SQL is included in the report
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed.
SQL> select /*+parallel(32) */ count(*) from dba_objects do, dba_source ds where do.owner = ds.owner;
COUNT(*)
----------
1.0262E+10
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/addmrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1769171242 eXhydra 1 eXhydra
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1769171242 1 TARKM2 TARKM2 celclnx42.us
Using 1769171242 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
eXhydra eXhydra 1 31 Oct 2022 12:13 1
2 31 Oct 2022 12:14 1
3 31 Oct 2022 12:15 1
4 31 Oct 2022 13:31 1
5 31 Oct 2022 13:40 1
6 31 Oct 2022 13:43 1
7 31 Oct 2022 13:53 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 7
End Snapshot Id specified: 7
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_1_7.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name addmrpt_1_1_7.txt
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ...
ADDM Report for Task 'TASK_28'
------------------------------
Analysis Period
---------------
AWR snapshot range from 1 to 7.
Time period starts at 31-OCT-22 12.13.52 PM
Time period ends at 31-OCT-22 01.53.16 PM
Analysis Target
---------------
Database 'Localhost' with DB ID 1769171242.
Database version 19.0.0.0.0.
ADDM performed an analysis of instance Localhost, numbered 1 and hosted at
celclnx42.us.oracle.com.
ADDM detected that the system is a PDB.
Activity During the Analysis Period
-----------------------------------
Total database time was 498 seconds.
The average number of active sessions was .08.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
------------------ ------------------- ---------------
1 Top SQL Statements .08 | 95.56 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .08 active sessions, 95.56% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is .08 active sessions, 95.56% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"1tf1karxnuxvz".
Related Object
SQL statement with SQL_ID 1tf1karxnuxvz.
select /*+parallel(32) */ count(*) from dba_objects do, dba_source ds
where do.owner = ds.owner
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "1tf1karxnuxvz" was executed 1 times and had
an average elapsed time of 454 seconds.
Rationale
At least one execution of the statement ran in parallel.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the DB on this instance.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
End of Report
