升级执行计划比对

简介: --大概的思路是将生产和回放的执行计划放到临时表里面,然后比对执行计划的关键字段,计算相等行数,看看是否与生产执行计划的行数和回放执行计划的行数一样。--如果一样则认为执行计划相等。 --下面例子是:将SPA回放与生产抓取的STS的性能数据及执行计划比对的完整记录create table ...

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

--下面例子是:将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 )  
目录
相关文章
|
7月前
|
存储 自然语言处理 Cloud Native
云数据仓库ADB问题之全文索引检索字段过长时条件会失效如何解决
云数据仓库AnalyticDB是阿里云提供的一种高性能、弹性扩展的云原生数据仓库解决方案;本合集将深入探讨ADB的架构、性能调优、数据管理和应用场景等,以及如何解决在使用过程中可能出现的问题,提高数据仓库的使用效率。
136 4
|
7月前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
1116 0
|
4月前
|
SQL 数据处理 数据库
SQL进阶之路:深入解析数据更新与删除技巧——掌握批量操作、条件筛选、子查询和事务处理,提升数据库维护效率与准确性
【8月更文挑战第31天】在数据库管理和应用开发中,数据的更新和删除至关重要,直接影响数据准确性、一致性和性能。本文通过具体案例,深入解析SQL中的高级更新(UPDATE)和删除(DELETE)技巧,包括批量更新、基于条件的删除以及使用子查询和事务处理复杂场景等,帮助读者提升数据处理能力。掌握这些技巧能够有效提高数据库性能并确保数据一致性。
85 0
|
SQL 存储 算法
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(二)|学习笔记
快速学习PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(二)
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(二)|学习笔记
|
SQL Arthas druid
MyBtais 批量插入慢排查及分析(后续)
MyBtais 批量插入慢排查及分析(后续)
185 0
|
SQL 移动开发 关系型数据库
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记
快速学习PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记
|
SQL XML JSON
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(一)|学习笔记
快速学习PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(一)
PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(一)|学习笔记
|
SQL 关系型数据库 MySQL
mysql聚合统计数据查询缓慢优化方案
在我们日常操作数据库的时候,比如订单表、访问记录表、商品表的时候。 经常会处理计算数据列总和、数据行数等统计问题。 随着业务发展,这些表会越来越大,如果处理不当,查询统计的速度也会越来越慢,直到业务无法再容忍。 所以,我们需要先了解、思考这些场景知识点,在设计之初,便预留一些优化空间支撑业务发展。
749 0
|
存储 Oracle 关系型数据库
FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素
FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素
|
SQL 存储 缓存
慢查询优化,终于在生产踩到了这个坑!!.md
之前看了饿了么团队写的一篇博客:等等!这两个 Spring-RabbitMQ 的坑我们已经替你踩了。深受启发,一定要取个能吸引读者眼球的标题,当然除了响当当的标题以外,内容也要是干货。为什么会想取这样一个标题,因为看了理论上的慢查询优化,今天!!!终于在生产上实战了
152 0
慢查询优化,终于在生产踩到了这个坑!!.md