ADD_COLORED_SQL Procedure

From the Oracle supplied documentation:

This procedure adds a colored SQL ID. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities (so that it does not have to be a TOP SQL). Capture occurs if the SQL is found in the cursor cache at snapshot time.

This can be very useful in the interests of fault finding or diagnosing problems.


SQL> execute dbms_workload_repository.add_colored_sql('6rj1tt5s6n497');

To confirm the SQL_ID has been colored, run the following SQL statement:
SQL> select * from sys.WRM$_COLORED_SQL

This will give you:

fred SQL> select * from sys.WRM$_COLORED_SQL
  2  /
      DBID SQL_ID        OWNER CREATE_TIME
---------- ------------- ----- --------------------
 413071501 6rj1tt5s6n497     1 11-Dec-2014 19:02:51
1 row selected.


and the following SQL will remove the color:
SQL> execute dbms_workload_repository.remove_colored_sql('6rj1tt5s6n497');

See also

AWR Colored SQL 11g

Oracle Support document
How to Determine the Execution Plan for a SQL Statement for a Range of AWR snapshots? (Doc ID 795204.1)

Oracle Support document
Information Center: SQL Query Performance Overview (Doc ID 1516494.2)



Keywords: oracle sql_id