《暴力破解Oracle数据库性能问题》由会员分享，可在线阅读，更多相关《暴力破解Oracle数据库性能问题（37页珍藏版）》请在金锄头文库上搜索。
1、暴力破解 oracle性能问题,从一个性能故障案例开始,某客户系统突然CPU飙升，大量活动会话积压,大量以前毫秒级的SQL, 突然变成平均执行3-5秒,执行计划异变,from,SQL不复杂： select count(distinct(pany) V_xxxx_USER t1, V_xxxx_APPLY t2,where t1.id = t2.idand t2.cert_no = :1 and t2.insert_time sysdate 90,好的执行计划，cost很低,差的执行计划，cost极高,访问的是视图,两个视图： V_xxxx_USER: selectID,. from c_app
2、ly_user,union all,select 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_APPLY,union all,select a.app_no ID, . from xxxx_MAIN,执行计划最大的区别是 UNION ALL PUSHED PREDICATE,select count(distinct(pany) from V_xxxx_
3、USER t1, V_xxxx_APPLY t2 where t1.id = t2.id View中两个表id列都有相应索引，且选择性极高 and t2.cert_no = :1 View中两个表cert_no列都有相应索引，且选择性极高 and t2.insert_time sysdate 90 UNION ALL PUSHED PREDICATE不生效是关键,尝试解决,尝试 SQL Profile 和 SPM进行绑定 DECLARE clsql_text CLOB; BEGIN SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE
4、 sql_id = / 竟然都很不稳定，时而生效，时而不生效 10053、MOS文档均无法迅速定位,问题关键,优化器选择执行计划的基本原则： 选COST低的执行计划 异变/异常的通常现象： 执行计划评估错误，导致差的执行计划COST比好的执行计划低 为何选择了一个几十万COST的执行计划而不选择COST几百的执行计划？ SQL Profile/SPM 为何不生效?,暴力解决思路,Oracle里充满了各种探针、flag、开关,Oracle 数据库 12.2，它有近 2500 万行 C 代码。 这有多恐怖，简直难以想象！你无法在不破坏成千上万个现有测试的情况下更改产品中的单行代码。好几代程序 员在
5、有限的项目期限内编写了这些代码，其中充斥着大量的垃圾代码。 非常复杂的逻辑、内存管理、上下文切换等，这些都用数千个 flag 连接起来。整个代码充斥着神秘的宏命令， 如果不拿出笔记本，并且手动去展开相关的宏命令，就无法理清楚这些命令。甚至可能需要一两天才能真正理解 某个宏命令的作用。 有时你需要理顺 20 个不同 flag 的值和效果来预测代码在不同情况下的行为方式，有时多达数百个 flag ！这一 点也不夸张。 这个产品仍然存活并且仍然可用的唯一原因是数百万次的测试！,引用自某 Oracle研发,你知道优化器里有多少个参数吗？,以Oracle 12.2.0.1 为例 Select count
6、(*) from v$sys_optimizer_env; 64 Select count(*) from x$ksppi where ksppinm like %optimizer% 201 Alter session set events 10053 trace name context forever,level 1; 550,特殊的参数：_fix_control,Oracle 10.2.0.2 新引入的一个参数 目的是回退某个bug带来的行为 相当于一个短路开关 不启用某个bug的fix Alter session set “_fix_control”=“bugno:off”; Alt
7、er session set “_fix_control”=“bugno:0”; 启用某个bug的fix Alter 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_fix_control,Select * from v$system_fix_control; Bugno: BUG
8、号 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代码示例 Declare Target_value number; begin For bugs in (select * from v$session_fix_control where sid
9、 = (select sid from v$mystat where rownum=1) ) loop If(bugs.value=1 ) then Target_value :=0; Else Target_value:=1; End if; execute immediate alter session set _fix_control=|bugs.bugno|:|target_value|; -探测 execute immediate alter session set _fix_control=|bugs.bugno|:|bugs.value|; ,修改fix_control的PLSQ
10、L代码示例 begin For bugs in (select bugno,value,decode(value,1,0,0,1) target_value from v$session_fix_control where sid = (select sid from v$mystat where rownum=1) and value in (0,1) ) loop execute immediate alter session set _fix_control=|bugs.bugno|:|bugs.target_value|; -探测 execute immediate alter ses
11、sion set _fix_control=|bugs.bugno|:|bugs.value|; ,修改优化器参数的PLSQL代码示例 选择v$sys_optimizer_env/x$ksppi 还是10053作为输入？ 10053最全面，但需要人为load数据。 只考虑可简单开关的参数: value in (TRUE,FALSE) begin For para in (select name,value,decode(value,TRUE,FALSE,FALSE,TRUE) target_value from where value in (TRUE,FALSE) ) loop execut
12、e immediate alter session set | para.name|=|para.target_value; -探测 execute immediate alter session set | para.name|=|para.value; ,如何探测是否成功？,1500-2000个开关，直接执行显然太慢 对于多数SQL性能问题，问题的关键是执行计划走错 第一步：找到没问题的执行计划。,第二步： 找到不一样的关键步骤,如何探测是否成功？,第三步： 使用explain plan 生成执行计划，注意set statement_id execute immediate alter s
13、ession set | para.name|=|para.target_value; Execute immediate explain plan set statement_id = | bug.bugno | for ; execute immediate alter session set | para.name|=|para.value; 第四步：从PLAN_TABLE中找到生效的Bug Fix开关或者优化器参数开关 Select statement_id from plan_table Where operation = UNION ALL PUSHED PREDICATE 698
14、7082,BUG 6987082,Bug 6987082Overestimated Multicolumn Selectivity/ Join Predicate Not Pushed into UNION View Description This Fix addresses 2 different unrelated problems Cost-based Join Predicate Push Down (JPPD) in union view does not occur if index is promoted in a single branch due to the restri
15、ctions imposed by bug 4127058. Incorrect Multi-Column Join Selectivity is disabled (possibly 1.0) This fix is disabled by default. To enable : alter session set _fix_control=6987082:on;,altersystemset_fix_control=6987082:on;,BUG 4127058,select * from v$system_fix_control where bugno = 4127058; - no
16、rows selected,案例2：奇怪的报错,客户升级到12c, 通过DB Link的某SQL出现报 selecta.start_date,a.user_id,a.product_id frompdw.K_xxx_TYPE a, pdw.K_xxx_USER b wherea.product_mode=:a and a.user_id = b.user_id; ORA-00932: inconsistent datatypes: expected got ,背景,SQL涉及两个对象均为两个远程DB Link表的union Create view . As Select id,start_date,productcode From t1dblink Union Select id,start_date,productcode From t2dblink 11
