How to color (mark) SQL for AWR snapshots

Are all SQL statements you’re looking for in your AWR repository ? If not, there is a simple way to ‘ask’ Oracle to include list of SQL_ID in every snapshot. All you need to do is color it. No, not using marker on the screen with Grid Control Performance tab but using DBMS_WORKLOAD_REPOSITORY. ADD_COLORED_SQL  procedure. Since you mark sql_id as colored it will be included in every AWR snapshot even if it is not one of the top SQL's.
All colored sql_id together with creation date could be checked in the following view - DBA_HIST_COLORED_SQL. After all work there is a simple way to uncolor sql_id (make it white ? ). There is a procedure called DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL.

Simple example:

- script used to display SQL statistics from AWR repository

$ cat sql_stats.sql
col exetime format 99999999999.99
col begin_interval_time format a30
select begin_interval_time, PLAN_HASH_VALUE, ELAPSED_TIME_DELTA/EXECUTIONS_DELTA exetime, EXECUTIONS_DELTA, CPU_TIME_DELTA, ELAPSED_TIME_DELTA, DISK_READS_DELTA from dba_hist_sqlstat ss, dba_hist_snapshot s where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id;

Checking AWR repository for particular SQL_ID.

SQL> @sql_stats.sql
Enter value for sql_id: 6zfggtprazcvb
old   2: where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
new   2: where s.snap_id = ss.snap_id and sql_id = '6zfggtprazcvb' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
BEGIN_INTERVAL_TIME            PLAN_HASH_VALUE         EXETIME EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA DISK_READS_DELTA 
------------------------------ --------------- --------------- ---------------- -------------- ------------------ ---------------- 
16-JUN-11 04.00.43.639 PM            927619989     90627544.40               20       12879044         1812550888           125728 
16-JUN-11 04.15.51.312 PM            927619989    282777061.76              151      279247551         4.2699E+10            72065 
16-JUN-11 04.30.57.081 PM           1587981875      1308903.00                7         203969            9162321             1894 
16-JUN-11 09.46.00.195 PM           1587981875       889464.06               16         760882           14231425             2463 
17-JUN-11 12.45.49.596 AM           1587981875      1390759.44               16         778881           22252151             2973 
17-JUN-11 04.45.38.168 AM           1587981875       621587.94               16         682897            9945407             2320 
17-JUN-11 06.45.18.396 AM           1587981875       415950.50               16         692895            6655208             1774 
17-JUN-11 07.45.35.975 AM           1587981875       865986.07               15         642901           12989791             1228 

8 rows selected.

SQL has been executed in last hour a few times but was not included in AWR snapshot

SQL> select sample_time, session_id, sql_id, sql_plan_hash_value, sql_child_number  from v$active_session_history where sql_id = '6zfggtprazcvb' and sample_time > sysdate - 1/24 order by sample_time;

SAMPLE_TIME                   SESSION_ID SQL_ID        SQL_PLAN_HASH_VALUE SQL_CHILD_NUMBER
----------------------------- ---------- ------------- ------------------- ----------------
17-JUN-11 03.21.43.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.44.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.45.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.46.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.47.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.48.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.49.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.50.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.51.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.52.419 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.42.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.43.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.44.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.49.37.819 PM           1724 6zfggtprazcvb          1587981875               10
17-JUN-11 03.49.38.819 PM           1724 6zfggtprazcvb          1587981875               10

SQL_ID has been colored (marked) using DBMS_WORKLOAD_REPOSITORY procedure

SQL> exec dbms_workload_repository.add_colored_sql('6zfggtprazcvb');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

      DBID SQL_ID        CREATE_TIME
---------- ------------- -------------------
1878812188 6zfggtprazcvb 2011-06-17 15:55:45

Now this SQL_ID has been included in AWR snapshot even if it has been executed once - see last row

SQL> @sql_stats.sql
Enter value for sql_id: 6zfggtprazcvb
old   2: where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
new   2: where s.snap_id = ss.snap_id and sql_id = '6zfggtprazcvb' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id

BEGIN_INTERVAL_TIME            PLAN_HASH_VALUE         EXETIME EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA DISK_READS_DELTA 
------------------------------ --------------- --------------- ---------------- -------------- ------------------ ---------------- 
16-JUN-11 04.00.43.639 PM            927619989     90627544.40               20       12879044         1812550888           125728 
16-JUN-11 04.15.51.312 PM            927619989    282777061.76              151      279247551         4.2699E+10            72065 
16-JUN-11 04.30.57.081 PM           1587981875      1308903.00                7         203969            9162321             1894 
16-JUN-11 09.46.00.195 PM           1587981875       889464.06               16         760882           14231425             2463 
17-JUN-11 12.45.49.596 AM           1587981875      1390759.44               16         778881           22252151             2973 
17-JUN-11 04.45.38.168 AM           1587981875       621587.94               16         682897            9945407             2320 
17-JUN-11 06.45.18.396 AM           1587981875       415950.50               16         692895            6655208             1774 
17-JUN-11 07.45.35.975 AM           1587981875       865986.07               15         642901           12989791             1228 
17-JUN-11 03.45.11.552 PM           1587981875      1818946.00                1          12998            1818946              226 

At the end of this example SQL_ID has been uncolored (unmarked).

SQL> exec dbms_workload_repository.remove_colored_sql('6zfggtprazcvb');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

no rows selected

SQL>