Monitor one SQL using AWR
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.
Carlos Sierra's Tools and Tips 
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
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
Nice, did not know about this feature.
Vishal Gupta
August 8, 2012 at 12:23 pm