Wednesday, May 07, 2008

Check current sql execution plan and historical plan

get sql_id from v$sqlarea

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

--for current execution plan
select operation,object_name,id,parent_id, position,cost,timestamp from v$sql_plan where sql_id='6y9n667q3wgb6';

--for historical plan
select operation,object_name,id,parent_id, position,cost,timestamp from dba_hist_sql_plan where sql_id ='01y6hmraf8kz6' order by timestamp, id,parent_id,position;

--based on count(distinct(timestamp))>1 , you should be alerted that execution plan is changed.

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select sql_id, count(distinct(timestamp)) from dba_hist_sql_plan where object_owner not in ('SYS','SYSMAN','MGMT_BSLN','DBSNMP') group by sql_id having count(distinct(timestamp))>1;