Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

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.

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('98n7902rytpxn');

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

      • DBA_HIST_COLORED_SQL

        guest

        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?

    guest

    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:

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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 940 other followers

%d bloggers like this: