--大概的思路是将生产和回放的执行计划放到临时表里面，然后比对执行计划的关键字段，计算相等行数，看看是否与生产执行计划的行数和回放执行计划的行数一样。
--如果一样则认为执行计划相等。
--下面例子是：将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 )