Added plan_hash_value to DBA_HIST_SQLSTAT query

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.

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply