暴力破解Oracle数据库性能问题

1、暴力破解 oracle性能问题从一个性能故障案例开始某客户系统突然CPU飙升，大量活动会话积压大量以前毫秒级的SQL, 突然变成平均执行3-5秒执行计划异变fromSQL不复杂：select count(distinct(pany) V_xxxx_USER t1, V_xxxx_APPLY t2where t1.id = t2.idand t2.cert_no = :1 and t2.insert_time sysdate 90好的执行计划，cost很低差的执行计划，cost极高访问的是视图两个视图： V_xxxx_USER:selectID,. from c_apply_userunion

2、allselect b.app_no ID,. from xxxx_p_info b, xxxx_info xxxx_info where b.app_no = e1.app_no and b.app_no = e2.app_no V_xxxx_APPLY:select ID,. from C_xxxx_APPLYunion allselect a.app_no ID, . from xxxx_MAIN执行计划最大的区别是 UNION ALL PUSHED PREDICATEselect count(distinct(pany)from V_xxxx_USER t1, V_xxxx_APPLY

3、 t2where t1.id = t2.id View中两个表id列都有相应索引，且选择性极高and t2.cert_no = :1 View中两个表cert_no列都有相应索引，且选择性极高 and t2.insert_time sysdate 90UNION ALL PUSHED PREDICATE不生效是关键尝试解决尝试 SQL Profile 和 SPM进行绑定 DECLAREclsql_text CLOB; BEGINSELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = &SQL_ID; DBMS_SQLT

4、UNE.IMPORT_SQL_PROFILE(sql_text =clsql_text,profile = sqlprof_attr(.),name = PROFILE_&SQL_ID,force_match = TRUE ); END;/竟然都很不稳定，时而生效，时而不生效10053、MOS文档均无法迅速定位问题关键优化器选择执行计划的基本原则： 选COST低的执行计划异变/异常的通常现象：执行计划评估错误，导致差的执行计划COST比好的执行计划低为何选择了一个几十万COST的执行计划而不选择COST几百的执行计划？ SQL Profile/SPM 为何不生效?暴力解决思路Oracle里充满

5、了各种探针、flag、开关Oracle 数据库 12.2，它有近 2500 万行 C 代码。这有多恐怖，简直难以想象！你无法在不破坏成千上万个现有测试的情况下更改产品中的单行代码。好几代程序 员在有限的项目期限内编写了这些代码，其中充斥着大量的垃圾代码。非常复杂的逻辑、内存管理、上下文切换等，这些都用数千个 flag 连接起来。整个代码充斥着神秘的宏命令， 如果不拿出笔记本，并且手动去展开相关的宏命令，就无法理清楚这些命令。甚至可能需要一两天才能真正理解 某个宏命令的作用。有时你需要理顺 20 个不同 flag 的值和效果来预测代码在不同情况下的行为方式，有时多达数百个 flag ！这一 点也

6、不夸张。这个产品仍然存活并且仍然可用的唯一原因是数百万次的测试！引用自某 Oracle研发你知道优化器里有多少个参数吗？以Oracle 为例Select count(*) from v$sys_optimizer_env;64Select count(*) from x$ksppi where ksppinm like %optimizer%201Alter session set events10053 trace name context forever,level 1;550特殊的参数：_fix_controlOracle 新引入的一个参数目的是回退某个bug带来的行为 相当于一个短路开

7、关不启用某个bug的fixAlter session set “_fix_control”=“bugno:off”; Alter session set “_fix_control”=“bugno:0”;启用某个bug的fixAlter session set “_fix_control”=“bugno:on”; Alter session set “_fix_control”=“bugno:1”;指定多个：Alter session set “_fix_control”=“bug1:0”,”bug2:1”,”bugn:off;v$system_fix_control/v$session_fi

8、x_controlSelect * from v$system_fix_control; Bugno: BUG 号Value: 1开启，0未开启Optimizer_Feature_enable: 跟随某个Optimizer_Feature_enable参数生效暴力解决思路这么多开关，逐一开启和关闭测试一下效果。对于优化器参数把FALSE 改为 TRUE 把TRUE改为FALSE对于Bug Fix把 1/on 改为 0/off 把 0/off 改为 1/on修改fix_control的PLSQL代码示例DeclareTarget_value number; beginFor bugs in (s

9、elect * from v$session_fix_controlwhere sid = (select sid from v$mystat where rownum=1) loop If(bugs.value=1 ) then Target_value :=0; ElseTarget_value:=1; End if;execute immediate alter session set _fix_control=|bugs.bugno|:|target_value|; -探测execute immediate alter session set _fix_control=|bugs.bu

10、gno|:|bugs.value|;修改fix_control的PLSQL代码示例beginFor bugs in (select bugno,value,decode(value,1,0,0,1) target_value from v$session_fix_controlwhere sid = (select sid from v$mystat where rownum=1) and value in (0,1) loopexecute immediate alter session set _fix_control=|bugs.bugno|:|bugs.target_value|; -

11、探测execute immediate alter session set _fix_control=|bugs.bugno|:|bugs.value|;修改优化器参数的PLSQL代码示例选择v$sys_optimizer_env/x$ksppi 还是10053作为输入？10053最全面，但需要人为load数据。只考虑可简单开关的参数: value in (TRUE,FALSE)beginFor para in (select name,value,decode(value,TRUE,FALSE,FALSE,TRUE) target_value from where value in (TRU

12、E,FALSE) loopexecute immediate alter session set | |=|para.target_value; -探测execute immediate alter session set | |=|para.value;如何探测是否成功？1500-2000个开关，直接执行显然太慢对于多数SQL性能问题，问题的关键是执行计划走错 第一步：找到没问题的执行计划。第二步： 找到不一样的关键步骤如何探测是否成功？第三步： 使用explain plan 生成执行计划，注意set statement_idexecute immediate alter session s

13、et | |=|para.target_value; Execute immediate explain plan set statement_id = | bug.bugno | for ;execute immediate alter session set | |=|para.value;第四步：从PLAN_TABLE中找到生效的Bug Fix开关或者优化器参数开关 Select statement_id from plan_tableWhere operation = UNION ALL PUSHED PREDICATE6987082BUG 6987082Bug 6987082Over

14、estimated Multicolumn Selectivity/ Join Predicate Not Pushed into UNION ViewDescription This Fix addresses 2 different unrelated problemsCost-based Join Predicate Push Down (JPPD) in union view does not occur if index is promoted in a single branch due to the restrictions imposed by bug 4127058.Inco

15、rrect Multi-Column Join Selectivity is disabled (possibly1.0)This fix is disabled by default.To enable : alter session set _fix_control=6987082:on;altersystemset_fix_control=6987082:on;BUG 4127058select * from v$system_fix_control where bugno = 4127058; - no rows selected案例2：奇怪的报错客户升级到12c, 通过DB Link

16、的某SQL出现报selecta.start_date,a.user_id,duct_id frompdw.K_xxx_TYPE a, pdw.K_xxx_USER b whereduct_mode=:aand a.user_id = b.user_id;ORA-00932: inconsistent datatypes: expected got 背景SQL涉及两个对象均为两个远程DB Link表的union Create view .AsSelect id,start_date,productcode From t1dblink UnionSelect id,start_date,produ

17、ctcode From t2dblink11g可以正常运行各种尝试均告失败确认date类型栏位数据, 未发现异常数据修改语句，直接拿null 或者 sysdate 替换该列数据，依然抱错 Create view .AsSelect id,cast (null as date) start_date,productcode From t1dblink UnionSelect id,null start_date,productcode From t2dblink10046、10053、errorstack trace 均无特殊发现只好再次暴力出击执行计划未发现异常, 且执行计划主体在远程数据库，

18、无法通过explainplan 判断出错时每次执行不到一秒就报错不出错是每次执行100多秒加快分析速度的小技巧由于是升级出来的错误，可以只看12相关的bugs.Select * from V$xxx_fixed_controlwhere optmizer_feature_enable in (,) 可以大幅减少探测次数。顺利找到问题beginFor bugs in (select bugno,value,decode(value,1,0,0,1) target_value from v$session_fix_controlwhere sid = (select sid from v$myst

19、at where rownum=1)andoptimizer_feature_enablelike12%) loopexecute immediate alter session set _fix_control=|bugs.bugno|:|bugs.target_value|; BeginExecute immediate SQL; Exception when others thenNull; - 有报错不管End;Dbms_output.put_line(bugs.bugno| |bugs.target_value); -输出能正常执行的bug fixexecute immediate

20、alter session set _fix_control=|bugs.bugno|:|bugs.value|;BUG: 20232513altersystemset_fix_control=6987082:off;其他暴力？有时候有些等待事件异常高涨但无法定位其原因需要寻找关联Oracle有多个数据源可供搜索AWR裸数据中的变化规律是否存在哪些指标变化规律与其相关？ 那也许就是问题的根因？AWR裸数据中的变化规律指标太多，逐个检查超出人力范围Oracle采集了数千个性能指标 DBA_HIST_SYSSTAT DBA_HIST_SYSMETRIC_SUMMARY DBA_HIST_LATCH_SUMMRY DBA_HIST_ROWCACHE_SUMMARY.DBA_HIST_UNDOSTAT难道都画成图肉眼检查？暴力的统计函数 corrCorr是Oracle内置的一个对两组数据求相关性的一个函数，变化规律 越相近，返回值越接近1。如果规律越相反，则会越接近-1指标获取select e.snap_id, -对应快照e.instance_number, event_name,