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.
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>
