Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Monitor one SQL using AWR

with 7 comments

Sometimes we want to keep an eye on a SQL statement that may not be part of any of the Top-SQL as per AWR reports. Use API below passing just the SQL_ID and from now on this SQL will be captured by AWR as long as the SQL is in memory at the time of the snapshot.


This API is available on 11.1 and higher.


Written by Carlos Sierra

June 29, 2012 at 5:01 pm

Posted in SQL Monitor

7 Responses

Subscribe to comments with RSS.

  1. Cool blog Carlos. Is there a reversal method?

    Rich Headrick

    July 1, 2012 at 5:12 pm

    • Yes Rich. That would be the REMOVE_COLORED_SQL API, passing also the SQL_ID.

      Carlos Sierra

      July 2, 2012 at 6:47 am

      • I do not know the name of the table where this set is stored. I suggest:
        1. Flush shared pool
        2. Turn trace
        3. Execute one of the 2 APIs
        4. Look in trace for table name
        I would do that myself, but I was out last week and I am still catching up with email and posts

        Carlos Sierra

        July 18, 2012 at 5:10 pm



        July 27, 2012 at 11:23 am

      • Thanks!

        Carlos Sierra

        July 27, 2012 at 1:07 pm

  2. How do I know which SQLs are ‘colored’ in the past? Is there a query I can run to find all SQLs that are colored in a particular database?


    July 17, 2012 at 4:41 pm

  3. Nice, did not know about this feature.

    Vishal Gupta

    August 8, 2012 at 12:23 pm

Leave a Reply

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

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