HOW TO SET CUSTOMIZED THRESHOLD TO INCLUDE SQL IN AWR REPORT

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

Leave a Reply