Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

A low resource SQL, still want it in the AWR … ‘Color Your SQL’

Posted by FatDBA on March 29, 2022

Recently one of the customer asked me about their critical SQL on a production system running on Oracle 12cR2, if there is a way to pin it in the AWR. I mean the query was already super fast, gets completed very fast and doesn’t consume any system resources, or isn’t a ‘Top SQL’. He still want to track this low resource SQL historically to check few stats, the guys was coming from an Oracle DBA background, and got the impression that the AWR only captures statements considered to be Top N (DBA_HIST_WR_CONTROL.TOPNSQL) which is by default 30. So, If you require any SQL to be always available in the top SQL, then you need to modify the default value to store those many SQLs. But this will create a AWR snapshot stress and that he don’t want.

The answer was ‘Yes’, there is a way how this can be achieved, and that’s too quite easily, and is even present since 11gR1 without making any modifications to your snapshot settings of topnsql. This is by using DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL API to pin any SQL into the AWR report as long as the SQL is in memory. So, if you mark your SQL or color it, it will be captured in every snapshot and it doesn’t have to be a TOP-N SQL to present in an AWR.

Let’s do a demo to understand how we can do that. I am going to run a test SQL to count number of rows into the table.

SQL> select count(*) from TABLE_GROWTH_MB;

  COUNT(*)
----------
      2868

SQL> select sql_id, plan_hash_value, sql_text from  v$sqlarea where sql_text like '%TABLE_GROWTH_MB%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------
4n3x6w1vhn1k3      1134407048  select count(*) from TABLE_GROWTH_MB

Okay, so we have executed the SQL and we have its SQL_ID with us now. Lets try to use awr_sql_report_text function that displays an Automatic Workload Repository (AWR) SQL report as plain text to see if the SQL is present under the latest snapshot range.

SQL>
SQL> select output from table (dbms_workload_repository.awr_sql_report_text(l_dbid => 2592296819 , l_inst_num => 2, 
l_bid => 36708, l_eid => 36718, l_sqlid => '4n3x6w1vhn1k3'));

OUTPUT
--------------------------------------------------------------------------------
WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
FATDBACA      2592296819 FATDBACA    PRIMARY          EE      12.2.0.1.0 YES NO

Instance     Inst Num Startup Time
------------ -------- ---------------
FATDBACA2           2 26-Oct-21 23:10

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     36708 10-Mar-22 16:00:37       190       2.5
  End Snap:     36718 10-Mar-22 18:30:06       181       2.6
   Elapsed:              149.48 (mins)
   DB Time:               91.13 (mins)

SQL Summary                    DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36718

                  No data exists for this section of the report.
          -------------------------------------------------------------

SQL ID: 4n3x6w1vhn1k3          DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36718

                  No data exists for this section of the report.
          -------------------------------------------------------------

Full SQL Text

                  No data exists for this section of the report.
SQL>
SQL>
SQL>
SQL>

And the report has no data about the SQL as the SQL was pretty fast, and hardly took few milliseconds to finish and is expected as it didn’t qualified as a TOP-SQL hence found no place in the AWR report. I am now going to color the SQLID and will re-run the SQL and check if its there in AWR now or not …

-- to PIN the SQLID in question
SQL> exec dbms_workload_repository.add_colored_sql('4n3x6w1vhn1k3');

PL/SQL procedure successfully completed.

SQL>

-- To UNPIN or remove the COLOR SQLID from repo
SQL> exec dbms_workload_repository.remove_colored_sql('<SQL_ID>');

-- Query below view to see list of all colored SQLs
SQL>  select * from DBA_HIST_COLORED_SQL;

   DB Id    SQL_ID        CREATE_TI     CON_ID
----------- ------------- --------- ----------
 2592296819 4n3x6w1vhn1k3 10-MAR-22          0


-- Re ran the SQL
SQL> select count(*) from TABLE_GROWTH_MB;

  COUNT(*)
----------
      2868



-- Let me generate a manual snap to capture fresh data
SQL>
SQL>
SQL> var n_snapid number
set feed off term on head off
set serveroutput on size unlimited
begin
        :n_snapid := dbms_workload_repository.create_snapshot();
        dbms_output.put_line('snap_id: ' || to_char(:n_snapid));
end;
/
snap_id: 36719
SQL> 


-- Let me retry again to see if the SQLID details are there in the AWR after I have colored it
SQL> SQL> select output from table (dbms_workload_repository.awr_sql_report_text(l_dbid => 2592296819 , l_inst_num => 2, 
l_bid => 36718, l_eid => 36719, l_sqlid => '4n3x6w1vhn1k3'));

OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
FATDBACA      2592296819 FATDBACA    PRIMARY          EE      12.2.0.1.0 YES NO

Instance     Inst Num Startup Time
------------ -------- ---------------
FATDBACA2           2 26-Oct-21 23:10

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     36708 10-Mar-22 16:00:37       190       2.5
  End Snap:     36719 10-Mar-22 18:39:40       177       2.6
   Elapsed:              159.06 (mins)
   DB Time:               93.43 (mins)

SQL Summary                    DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719

                Elapsed
   SQL Id      Time (ms)
------------- ----------

 Container DB
 ------------
4n3x6w1vhn1k3          0
Module: SQL*Plus
select count(*) from TABLE_GROWTH_MB
   2592296819

          -------------------------------------------------------------

SQL ID: 4n3x6w1vhn1k3          DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select count(*) from TABLE_GROWTH_MB

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1134407048                      0             1         36719          36719
          -------------------------------------------------------------


Plan 1(PHV: 1134407048)
-----------------------

Plan Statistics                DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                                 0            0.2     0.0
CPU Time (ms)                                     0            0.2     0.0
Executions                                        1            N/A     N/A
Buffer Gets                                      22           22.0     0.0
Disk Reads                                        0            0.0     0.0
Parse Calls                                       1            1.0     0.0
Rows                                              1            1.0     N/A
User I/O Wait Time (ms)                           0            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                        0            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 19            N/A     N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |    10 (100)|          |
|   1 |  SORT AGGREGATE    |                 |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TABLE_GROWTH_MB |  2624 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------



Full SQL Text

SQL ID        SQL Text
------------- -----------------------------------------------------------------
4n3x6w1vhn1k3 select count(*) from TABLE_GROWTH_MB

Perfect, the SQLID is there in the AWR now, and we can see all runtime stats, execution plan, SQL Text etc.

Hope It Helped!
Prashant Dixit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: