升级执行计划比对

--大概的思路是将生产和回放的执行计划放到临时表里面，然后比对执行计划的关键字段，计算相等行数，看看是否与生产执行计划的行数和回放执行计划的行数一样。
--如果一样则认为执行计划相等。

--下面例子是：将SPA回放与生产抓取的STS的性能数据及执行计划比对的完整记录

create table ly_compare_result_sts as
insert into ly_compare_result_sts
Select *
From (Select b.*, dbms_lob.substr(st.sql_text, 3000) sql_text

      From SYS.Wrh$_Sqltext st,
           (Select TASK_NAME,
                   sql_id,
                   bf_executions,
                   af_executions,
                   detal_buffer_gets,
                   bf_buffer_gets,
                   af_buffer_gets,
                   bf_plan_hash_value,
                   bf_ELAPSED_TIME,
                   af_ELAPSED_TIME,
                   bf_rows_processed,
                   af_plan_hash_value,
                   af_ROWS_PROCESSED
              From (Select TASK_NAME,
                           sql_id,
                           bf_executions,
                           af_executions,
                           bf_ELAPSED_TIME,
                           af_ELAPSED_TIME,
                           round(af_buffer_gets / af_EXECUTIONS) -
                           round(bf_buffer_gets / bf_executions) detal_buffer_gets,
                           round(bf_buffer_gets / bf_executions) bf_buffer_gets,
                           round(af_buffer_gets / af_EXECUTIONS) af_buffer_gets,
                           bf_plan_hash_value,
                           bf_rows_processed / bf_executions bf_rows_processed,
                           af_plan_hash_value,
                           af_ROWS_PROCESSED / af_EXECUTIONS af_ROWS_PROCESSED
                      From (Select bf.sqlset_name task_name ,
                                   bf.sql_id,
                                   bf.executions bf_executions,
                                   af.EXECUTIONS af_executions,
                                   af.ELAPSED_TIME af_ELAPSED_TIME,
                                   bf.ELAPSED_TIME bf_ELAPSED_TIME,
                                   bf.plan_hash_value bf_plan_hash_value,
                                   bf.buffer_gets bf_buffer_gets,
                                   bf.rows_processed bf_rows_processed,
                                   af.plan_hash_value af_plan_hash_value,
                                   af.BUFFER_GETS af_BUFFER_GETS,
                                   af.ROWS_PROCESSED af_ROWS_PROCESSED
                              From dba_sqlset_statements bf,
                                   dba_advisor_sqlstats af
                             Where af.execution_name = 'spa11g'
                               And af.task_name = 'TASK_11G'
                               And bf.sqlset_name = 'STS_UP11_0721'
                               ---and bf.task_name = 'TASK_10G_0719v1'
                               And bf.sql_id = af.sql_id))
             Where detal_buffer_gets > 0) b
     Where st.sql_id = b.sql_id
     Order By detal_buffer_gets Desc)

Where upper(sql_text) Not Like '%Analyze(%'
And upper(sql_text) Not Like '%SELECT / DS_SVC /%'
And upper(sql_text) Not Like '%/ OPT_DYN_SAMP /%'
and upper(sql_text) Not Like '%V$%'
and upper(sql_text) Not Like '%SYS.%'
and upper(sql_text) Not Like '%SYSTEM.%'
and upper(sql_text) Not Like '%FROM DUAL%'
and upper(sql_text) Not Like '%DBA_%'
and upper(sql_text) Not Like '%EXPLAIN PLAN%'

and upper(sql_text) Not Like '%FROM DUAL%';

delete from ly_compare_result_STS
where upper(sql_text) like '%ALL_SYNONYMS%'

or upper(sql_text) like  '%ALL_TABLES%'  ;

delete from ly_compare_result_STS s1
where EXISTS (
SELECT SS.sql_id FROM dba_sqlset_statements ss
where s1.sql_id = ss.sql_id
and ss.module = 'PL/SQL Developer' ) ;

commit；

-------------执行计划比对-----------------------------------------------------

create table ly_prod_plans as
select distinct p.sql_id, p.PLAN_HASH_VALUE , p.OPERATION, p.OPTIONS , p.OBJECT_OWNER , p.OBJECT_NAME ,p.OBJECT_ALIAS , p.OBJECT_TYPE ,

   p.ID, p.PARENT_ID , p.DEPTH , p.POSITION  

from v$sql_plan p
where p.PLAN_HASH_VALUE in ( 1425307179 ) ;

create index ind_ly_001 on ly_prod_plans (plan_hash_value, id);

select * from ly_prod_plans where 1=0 for update

select distinct t.bf_plan_hash_value ||',' from ly_compare_result_sts t

where not exists ( select 1 from ly_prod_plans t1 where t1.PLAN_HASH_VALUE = t.bf_plan_hash_value )

----1. 按照Sql_id, plan_hash_value导入生产的执行计划
----2. 对应不能按照sql_id导入的，可以单独按照plan_hash_value导入,sql_id is null
DROP TABLE ly_spa_plans PURGE ;
create table ly_spa_plans
as
--insert into ly_spa_plans
select distinct t1.sql_id, t1.plan_hash plan_hash_value ,

   p.OPERATION, p.OPTIONS , p.OBJECT_OWNER , p.OBJECT_NAME ,p.OBJECT_ALIAS , p.OBJECT_TYPE , 
   p.ID, p.PARENT_ID , p.DEPTH , p.POSITION  ---,  p.* 

from sys.wri$_adv_sqlt_plan_hash t1 , sys.WRI$_ADV_SQLT_PLANS p
where /*exec_name = 'spa10g_2'
and */t1.plan_id = p.plan_id
-- and t1.plan_hash = 294662549
and t1.sql_id in(
select sql_id from ly_compare_result_sts
)
order by t1.sql_id , p .id

create index ind_ly_002 on ly_spa_plans (plan_hash_value, id);

select * from ly_compare_result_sts ;

--drop table ly_compare_plans_result purge ;
-- truncate table ly_compare_plans_result ;

delete from ly_compare_plans_result where val <> prod_val ;

create table ly_compare_plans_result as
insert into ly_compare_plans_result
select bb.* ,cc.val prod_val ,'AO' remarks
from (
select s.sql_id, p.plan_hash_value sts_plan_hash_value, s.plan_hash_value spa_plan_hash_value ,count(1) val
from ly_prod_plans p , ly_spa_plans s
where p.operation = s.operation

and  nvl(p.options,'0') = nvl(s.options,'0')
--and  nvl(p.object_owner,'0') =nvl( s.object_owner ,'0')
and  nvl(p.object_name,'0')= nvl(s.object_name,'0')
--and  nvl(p.object_alias,'0') = nvl( s.object_alias,'0')
and  nvl(p.object_type,'0') = nvl( s.object_type,'0')
and  nvl(p.ID,-1) = nvl(s.id,-1) 
and  nvl(p.PARENT_ID,-1)  = nvl(s.PARENT_ID,-1) 
and  p.DEPTH = s.DEPTH  

-- and p.POSITION = s.POSITION
group by p.plan_hash_value , s.plan_hash_value , s.sql_id
) bb , (
select plan_hash_value , count(1) val from ly_prod_plans p
group by plan_hash_value
) cc ,ly_compare_result_STS1 t
where bb.sts_plan_hash_value = cc.plan_hash_value
and t.sts_plan_hash = bb.sts_plan_hash_value (+)
and t.spa_plan_hash = bb.spa_plan_hash_value (+)
and t.sql_id = bb.sql_id (+)
and not exists (

  select 1 from ly_compare_plans_result tt 
  where/* t.sql_id = tt.sql_id and*/ tt.sts_plan_hash_value = bb.sts_plan_hash_value and  tt.spa_plan_hash_value = bb.spa_plan_hash_value ) ;
   

update ly_compare_result_STS1 t1
set t1.ppcompr = 'Same'
where exists ( select 'Same' from ly_compare_plans_result t
where / t.sql_id = t1.sql_id and/ t.sts_plan_hash_value = t1.sts_plan_hash and t.spa_plan_hash_value = t1.spa_plan_hash and t.val = t.prod_val ) ;

select t.sqlset_name , t.sql_id, t.sts_plan_hash sts_plan_hash_value , t.spa_plan_hash spa_plan_hash_value ,

  b.val , ( select count(1) from ly_prod_plans  p where p.plan_hash_value = t.sts_plan_hash ) sts_val 

from (
select p.plan_hash_value sts_plan_hash_value, s.sql_id ,s.plan_hash_value spa_plan_hash_value /, p.operation/ ,count(1) val
from ly_prod_plans p , ly_spa_plans s
where p.operation = s.operation
and nvl(p.options,'0') = nvl(s.options,'0')
--and nvl(p.object_owner,'0') =nvl( s.object_owner ,'0')
and nvl(p.object_name,'0')= nvl(s.object_name,'0')
--and nvl(p.object_alias,'0') = nvl( s.object_alias,'0')
and nvl(p.object_type,'0') = nvl( s.object_type,'0')
and nvl(p.ID,-1) = nvl(s.id,-1)
and nvl(p.PARENT_ID,-1) = nvl(s.PARENT_ID,-1)
and p.DEPTH = s.DEPTH
group by p.plan_hash_value , s.plan_hash_value , s.sql_id) b ,ly_compare_result_STS t
where t.sts_plan_hash = b.sts_plan_hash_value (+)
and t.spa_plan_hash = b.spa_plan_hash_value (+)
and t.sql_id = b.sql_id (+)
and t.sql_id ='f704udb3va1v9' ;

select * from ly_compare_result_STS t where sql_id ='f704udb3va1v9' ;

select p.OPERATION, p.OPTIONS , p.OBJECT_OWNER , p.OBJECT_NAME ,p.OBJECT_ALIAS , p.OBJECT_TYPE ,

   p.ID, p.PARENT_ID , p.DEPTH   from ly_prod_plans   p where plan_hash_value in (2256942569 ,  2872682413 ) 

union all
select distinct p.OPERATION, p.OPTIONS , p.OBJECT_OWNER , p.OBJECT_NAME ,p.OBJECT_ALIAS , p.OBJECT_TYPE ,

   p.ID, p.PARENT_ID , p.DEPTH  from ly_spa_plans p where  plan_hash_value in   (2256942569 ,  2872682413 )