Oracle Scratchpad

October 19, 2018

add_colored_sql

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 3:08 pm GMT Oct 19,2018

The following request appeared recently on the Oracle-L mailing list:

I have one scenario related to capturing of sql statement in history table..  Like dba_hist_sqltext capture the queries that ran for 10 sec or more..  How do I get the sql stmt which took less time say in  millisecond..  Any idea please share.

An AWR snapshot captures statements that (a) meet some workload criteria such as “lots of executions” and (b) happen to be in the library cache when the snapshot takes place; but if you have some statements which you think are important or interesting enough to keep an eye on that don’t do enough work to meet the normal workload requirements of the AWR snapshots it’s still possible to tell Oracle to capture them by “coloring” them.  (Apologies for the American spelling – it’s necessary to avoid error ‘PLS_00302: component %s must be declared’.)

Somewhere in the 11gR1 timeline the package dbms_workload_repository acquired the following two procedures:


PROCEDURE ADD_COLORED_SQL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT

PROCEDURE REMOVE_COLORED_SQL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT

You have to be licensed to use the workload repository, of course, but if you are you can call the first procedure to mark an SQL statement as “interesting”, after which its execution statistics will be captured whenever it’s still in the library cache at snapshot time. The second procedure lets you stop the capture – and you will probably want to use this procedure from time to time because there’s a limit (currently 100) to the number of statements you’re allowed to color and if you try to exceed the limit your call will raise Oracle error ORA-13534.


ORA-13534: Current SQL count(100) reached maximum allowed (100)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 751
ORA-06512: at line 3

If you want to see the list of statements currently marked as colored you can query table wrm$_colored_sql, exposed through the views dba_hist_colored_sql and (in 12c) cdb_hist_colored_sql. (Note: I haven’t yet tested whether the limit of 100 statements is per PDB or summed across the entire CDB [but see comment #2 below] – and the answer may vary with version of Oracle, of course).


SQL> select * from sys.wrm$_colored_sql;

      DBID SQL_ID             OWNER CREATE_TI
---------- ------------- ---------- ---------
3089296639 aedf339438ww3          1 28-SEP-18

1 row selected.

If you’ve had to color a statement to force the AWR snapshot to capture it the statement probably won’t appear in the standard AWR reports; but it will be available to the “AWR SQL” report (which I usually generate from SQL*Plus with a call to $ORACLE_HOME/rdbms/admin/awrsqrpt./sql).

Footnote

If the statement you’re interested in executes very infrequently and often drops out of the library cache before it can be captured in an AWR snapshot then an alternative strategy is to enable system-wide tracing for that statement so that you can capture every execution in a trace file.

 

5 Comments »

  1. As an Englishman, I find it not credible that Jonathan wrote this article.
    After all, as every educated Englishman knows, the word ‘colour’ has a ‘u’ in it.

    Comment by Martin ROSE — October 19, 2018 @ 5:08 pm GMT Oct 19,2018 | Reply

  2. Hi Jonathan!
    Seems like the limit of 100 colored sql_ids is for a single PDB. Take a look:

    SQL> alter session set container = rabbix;
    
    Session altered.
    
    SQL> select count(*) from wrm$_colored_sql;
    
      COUNT(*)
    ----------
           100
    
    SQL> alter session set container = rabbix2;
    
    Session altered.
    
    SQL> select count(*) from wrm$_colored_sql;
    
      COUNT(*)
    ----------
             0
    

    And it obviously lets me add new colored SQL_IDs in rabbix2, while in rabbix I get ORA-13534: Current SQL count(100) reached maximum allowed (100).
    I’ve tested it in 12.2.0.1.0 and 18.3.0.0.0, thus can’t be certain about 12.1.

    Thank you.
    Viacheslav

    Comment by Вячеслав Анджич — October 20, 2018 @ 6:36 pm GMT Oct 20,2018 | Reply

    • Viacheslav,

      Thanks for doing the tests and letting us know the results.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — October 21, 2018 @ 8:39 am GMT Oct 21,2018 | Reply

      • Hi Jonathan,
        I just noticed, I wrote “can’t be certain about 11”, when, I suspect, it meant to be 12.1. I was probably tired enough to imagine multi-tenant in 11g ))
        Also, Google+ seem to peek my name in Cyrillic. Looks odd, I need to change it )
        Thank you
        Viacheslav

        Comment by Вячеслав Анджич — October 21, 2018 @ 6:49 pm GMT Oct 21,2018 | Reply

        • Viacheslav,

          I failed to notice that it didn’t make any sense to worry about 11. It’s strange what little errors the mind can miss when focused on a bigger idea. I’ve edited the original.

          Comment by Jonathan Lewis — October 22, 2018 @ 4:40 pm GMT Oct 22,2018


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google+ photo

You are commenting using your Google+ 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.