How many times have you received user’s phone call stating that the DB is slow?, well sometimes this has nothing to do with the DB’s overall performance itself, but with a user’s query.
Now the question is, is this query really slow?, the best way to find this out is comparing the actual execution time with the ones in the past. Here’s a SQL statement which search inside AWR repository (Caution!!! a special license is required!!!), it compares the execution plan of all SQL_ID’s against the previous snapshot.
awr_planchanges.sql
prompt enter the number of days in the past to scan
SET LINES 500
SELECT A.SNAP_ID,
BEGIN_INTERVAL_TIME,
SQL_ID,
HASH_VALUE1,
HASH_VALUE2,
VALUE1 as “ELAPSED TIME PER EXEC 1” ,
VALUE2 as “ELAPSED TIME PER EXEC 2”,
ROUND (CHANGE_PERCENT) as “CHANGE PERCENT”
FROM ( SELECT SNAP_ID,
SQL_ID,
SUM (pvalue1) HASH_VALUE1,
SUM (pvalue2) HASH_VALUE2,
SUM (value1) VALUE1,
SUM (value2) VALUE2,
(SUM (VALUE1) + 1) * 100 / (SUM (VALUE2) + 1)
AS CHANGE_PERCENT
FROM (SELECT snap_id,
0 AS snap2,
sql_id,
plan_hash_value AS pvalue1,
0 AS pvalue2,
ROUND (
elapsed_time_delta / executions_delta / 1000000,
3)
AS value1,
0 AS value2
FROM dba_hist_sqlstat sql
WHERE executions_delta > 0
UNION
SELECT snap_id + 1,
snap_id AS snap2,
sql_id,
0 AS pvalue1,
plan_hash_value AS pvalue2,
0 AS vaule,
ROUND (
elapsed_time_delta / executions_delta / 1000000,
3)
AS value2
FROM dba_hist_sqlstat sql
WHERE executions_delta > 0)
GROUP BY SNAP_ID, SQL_ID) A,
dba_hist_snapshot B
WHERE A.SNAP_ID = B.SNAP_ID
AND HASH_VALUE1 > 0
AND HASH_VALUE2 > 0
AND HASH_VALUE1 != HASH_VALUE2
AND BEGIN_INTERVAL_TIME > SYSDATE – (&days)
ORDER BY A.SNAP_ID
/
This is the output of the script:
SNAP_ID BEGIN_INTERVAL_TIME SQL_ID HASH_VALUE1 HASH_VALUE2 SEC PER EXE 1 SEC PER EXE 2 CHANGE %
——– ———————– ————- ———– ———– ————- ————- ——–
9547 01-OCT-12 01.00.07.841 AM 4urszd9dt9fjv 4862331523 891004645 .015 .007 101
9585 02-OCT-12 03.00.41.798 PM 4urszd9dt9fjv 4862331523 891004645 .009 .003 101
9586 02-OCT-12 04.00.36.393 PM 4urszd9dt9fjv 891004645 4862331523 .006 .009 100
9587 02-OCT-12 05.00.25.306 PM 1k30v0pyg32vu 414828074 878600859 .179 .157 102
9587 02-OCT-12 05.00.25.306 PM dsm86bzuqtd2r 2452407222 3005749068 .048 1.37 44
9616 03-OCT-12 10.00.34.499 PM 4urszd9dt9fjv 4862331523 891004645 .018 .023 100
9621 04-OCT-12 03.00.06.979 AM 4urszd9dt9fjv 891004645 4862331523 .03 .027 100
9640 04-OCT-12 09.00.40.250 PM 4urszd9dt9fjv 4862331523 891004645 .042 .012 103
9641 04-OCT-12 10.00.22.954 PM dfmu8nm1cscx7 3810296266 4308029399 .938 .703 114
9710 07-OCT-12 07.00.09.269 PM 4urszd9dt9fjv 4862331523 891004645 .046 .015 103
9750 09-OCT-12 11.00.59.162 AM 1k30v0pyg32vu 1634868183 414828074 .16 .155 100
9758 09-OCT-12 07.00.10.659 PM b70xavb9wv27v 1111647858 4256287279 5.937 6.475 93
12 rows selected.
You can clearly see how the HASH_VALUE changed and the execution time as well. If the CHANGE % is above 100% means that execution time decreased, on the other hand CHANGE % below 100% means the execution time increased.
ORAganism blog is having a really good script which searches by SQL_ID.
http://oraganism.wordpress.com/2011/12/14/a-dba_hist_sqlstat-query-that-i-am-very-fond-of/
Happy troubleshooting,
Alfred
Same post in Spanish here!