Monday, May 23, 2016

Query to view plan hash value over time for a sql id

This query uses the AWR history tables. So you would need a license to be able to use it.

It will return something like this:

select snap_id,begin_interval_time,end_interval_time,sql_id,plan_hash_value,parsing_schema_name
from dba_hist_sqlstat
join dba_hist_snapshot
using (snap_id,instance_number)
where sql_id='&sql_id'
and parsing_schema_name='&schema'
order by snap_id desc;



SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              SQL_ID        PLAN_HASH_VALUE SCHEMA   
------- ------------------------------ ------------------------------ ------------- --------------- ----------
   6219 5/19/2016 7:30:05.359 AM       5/19/2016 8:00:10.533 AM       cu2zgt871kzsv      2820428760 NICO     
   6219 5/19/2016 7:30:05.359 AM       5/19/2016 8:00:10.533 AM       cu2zgt871kzsv      4084779347 NICO     
   6218 5/19/2016 7:00:00.935 AM       5/19/2016 7:30:05.359 AM       cu2zgt871kzsv      4084779347 NICO     
   6183 5/18/2016 1:30:20.839 PM       5/18/2016 2:00:25.344 PM       cu2zgt871kzsv      4084779347 NICO     
   6182 5/18/2016 1:00:16.638 PM       5/18/2016 1:30:20.839 PM       cu2zgt871kzsv      4084779347 NICO     
   6181 5/18/2016 12:30:12.766 PM      5/18/2016 1:00:16.638 PM       cu2zgt871kzsv      4084779347 NICO     
   5669 5/7/2016 8:30:06.532 PM        5/7/2016 9:00:21.666 PM        cu2zgt871kzsv      2820428760 NICO     
   5329 4/30/2016 6:30:27.380 PM       4/30/2016 7:00:32.868 PM       cu2zgt871kzsv      2820428760 NICO     
   5284 4/29/2016 8:00:34.962 PM       4/29/2016 8:30:41.139 PM       cu2zgt871kzsv      2820428760 NICO     
   5258 4/29/2016 7:00:28.038 AM       4/29/2016 7:30:34.104 AM       cu2zgt871kzsv      2820428760 NICO     
   4996 4/23/2016 8:00:34.871 PM       4/23/2016 8:30:42.130 PM       cu2zgt871kzsv      2820428760 NICO     
   2382 2/29/2016 8:00:19.203 AM       2/29/2016 8:30:25.207 AM       cu2zgt871kzsv      1221363908 NICO     
   2381 2/29/2016 7:30:15.311 AM       2/29/2016 8:00:19.203 AM       cu2zgt871kzsv      1221363908 NICO     
   2380 2/29/2016 7:00:10.910 AM       2/29/2016 7:30:15.311 AM       cu2zgt871kzsv      1221363908 NICO     
   2199 2/25/2016 12:30:23.410 PM      2/25/2016 1:00:26.904 PM       cu2zgt871kzsv       661146938 NICO     
   1912 2/19/2016 12:54:50.237 PM      2/19/2016 1:30:16.031 PM       cu2zgt871kzsv      2820428760 NICO

So now I can see that the plan has changed in the last few days.

No comments :

Post a Comment