I modified my query of DBA_HIST_SQLSTAT that I use for query tuning to include plan_hash_value. This is nice because you can see whether a particular plan corresponds to a longer run time. Here a link the the modified script(updated).
Here is edited output for a real issue I’m working on today:
PLAN_HASH_VALUE END_INTERVAL_TIME Elapsed Average ms --------------- --------------------- ------------------ 127033930 14-JUL-12 03.00.45 AM 3.72306098 4129337110 17-JUL-12 03.00.25 AM 9.39056636 127033930 18-JUL-12 03.00.09 AM 4.85851429 127033930 21-JUL-12 03.00.23 AM 2.58389897 4129337110 22-JUL-12 04.00.53 PM 305.116467 4129337110 23-JUL-12 03.00.15 AM 42.6378382 4129337110 24-JUL-12 03.01.00 AM 11.6052238 127033930 25-JUL-12 03.00.16 AM 3.65477356 127033930 26-JUL-12 03.00.31 AM 4.30130391 127033930 27-JUL-12 03.00.15 AM 5.08272086 127033930 28-JUL-12 03.00.26 AM 5.76550411 4129337110 29-JUL-12 03.00.39 AM 106.969158 2097624419 30-JUL-12 03.00.49 AM 1333.82567 2097624419 31-JUL-12 03.00.10 AM 262.40561 4129337110 01-AUG-12 03.00.24 AM 10.2028517 2739482096 02-AUG-12 03.00.39 AM 1878.88872 2739482096 02-AUG-12 04.00.52 AM 1897.64979 2739482096 03-AUG-12 03.00.53 AM 2079.60304 127033930 03-AUG-12 03.00.53 AM 18.854426 2739482096 04-AUG-12 03.00.05 AM 453.994259 4129337110 05-AUG-12 03.00.19 AM 46.4180495 4129337110 05-AUG-12 05.00.19 PM 148.220406
127033930 and 4129337110 are the good plans. 2097624419 and 2739482096 are the bad plans. The column with the heading “Elapsed Average ms” is the average run time in milliseconds for the given plan for that hour’s AWR snapshot.
– Bobby
Pingback: Quickly built new Python graph SQL execution by plan | Bobby Durrett's DBA Blog