Saturday, June 18, 2011

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>

Friday, June 10, 2011

Rolling partition and global index - do we need to rebuild ?

Some time ago (arrghhh - three month ago) I blogged about partition dropping when table has a global index - see. There was a example based on my tests. Now it is time to show some real figures. Both examples are sharing same scenario - about 2 years without touching indexes, 7 partitions are drop once per week. Partition drop is taking up to 6 - 7 hours. This time coalesce was not a option due to free up space requirement but it should help in future to avoid similar situations.
Example number 1
Table has around 562.000.000 rows and one partition has around 13.000.000 rows. Primary key index based on one number column growth to 140 GB and has been rebuilt to 13.9 GB. It is around 10 times less.
Example number 2 - bigger scale
Table has around 2.130.000.000 rows and one partition has around 45.000.000 rows.Primary key index based on one number column growth to 360 GB and has been rebuilt to 40 GB. It is 9 times less.
I put both examples here to show you that running coalesce for global indexes after partition drop can save you a lot of space and if done regularly prevent you from more resource consuming index rebuild.To be honest this is one of a few situations when I have seen any improvement (space wise this time) after index rebuild. But keep in mind that this is a special situation and for most of other cases you don't need to rebuild you indexes - if you think you need read Niall or Richard blog first.
regards,
Marcin