Ever troubled to capture a particular SQL statement which is found to be consuming significantly potential database resources, wanted to keep a record of its trend or behaviour over times upon various tuning attempts. AWR snapshot does not include all SQLs in its report unless it is identified to be a TOP SQL Statement. In such cases, it never used to be possible to capture a desired SQL Statement to monitor its trend over various tuning attempts.
To give DBA the ability to capture such desired SQL Statements, Oracle has introduced a procedure called "add_colored_sql()" with the package "dbms_workload_repository" in 11g that inturn marks the SQL as "colored", so that SQL ID which is marked as colored will be captured in every AWR snapshot, It does not need to be a top SQL Statement.
To color the SQL Statement,
begindbms_workload_repository.add_colored_sql( sql_id => 'f93g1utkcbzy1'
To remove the colored SQL Statement from AWR snapshots,
begin dbms_workload_repository.remove_colored_sql( sql_id => 'f93g1utkcbzy1'
Note : We need the SQL_ID inorder to color it
Following dictionary views/tables can be queries to find the status,
SQL> SELECT TNAME FROM TAB WHERE TNAME LIKE '%COLOR%';
To check the status of any colored SQL Statements,
SQL> select * from DBA_HIST_COLORED_SQL
DBID SQL_ID CREATE_TI
---------- ------------- ---------
223864828 f93g1utkcbzy1 13-APR-13
To check if the colored SQL ID is captured, please take the AWR Report over its next interval