如何“暴力破解”Oracle性能优化的极端问题（附精彩案例解读）

云和恩墨大咖系列报道

2019数据技术嘉年华于11月16日在京落下了帷幕。大会历时两天，来自全国各地上千名学术精英、数据库领袖人物、数据库专家、技术爱好者在这里汇聚一堂，围绕“开源 • 智能 • 云数据 － 自主驱动发展 创新引领未来”的大会主题，共享"开源自研，云和数据，智能运维，智能业务，数据前沿，用户实践"六大主题盛宴。

近期，小编为大家精心准备了“2019数据技术嘉年华——云和恩墨大咖系列”分享。在此次嘉年华大会上，云和恩墨专家聚焦行业热点，分析典型案例，探索数据价值，共论智能未来。

让我们跟随云和恩墨专家团一同回顾嘉年华的精彩瞬间，领略他们眼中的数据之美吧~

2019数据技术嘉年华 罗海雄先生现场演讲图

本期，我们请到云和恩墨性能优化专家 罗海雄 先生，罗老师是一名 Oracle ACE-A，还是ITPUB论坛数据库管理版版主，2012 ITPUB全国SQL大赛冠军得主；资深的架构师和性能优化专家，对 SQL 优化和理解尤其深入；作为业内知名的技术传播者之一，经常出席各类技术分享活动；从开发到性能管理，有着超过10年的企业级系统设计和优化经验；曾服务于甲骨文公司，组织和主讲过多次《甲骨文技术开发人员日》和《Oracle圆桌会议》，在任职甲骨文公司之前，还曾经服务于大型制造企业中芯国际，具备丰富的制造行业系统架构经验。

本次嘉年华他带来题为：Oracle性能优化之暴力破解 主题分享。下面，让我们跟随罗老师的文字，一同回顾属于他的嘉年华精彩瞬间。

在网上曾经有一位前Oracle公司员工描述过：Oracle 数据库 12.2，它有近 2500 万行 C 代码。

这有多恐怖，简直难以想象！你无法在不破坏成千上万个现有测试的情况下更改产品中的单行代码。好几代程序员在有限的项目期限内编写了这些代码，其中充斥着大量的垃圾代码。

非常复杂的逻辑、内存管理、上下文切换等，这些都用数千个 flag 连接起来。整个代码充斥着神秘的宏命令，如果不拿出笔记本，并且手动去展开相关的宏命令，就无法理清楚这些命令。甚至可能需要一两天才能真正理解某个宏命令的作用。

有时你需要理顺 20 个不同 flag 的值和效果来预测代码在不同情况下的行为方式，有时多达数百个 flag ！这一点也不夸张。

这个产品仍然存活并且仍然可用的唯一原因是数百万次的测试！

看起来确实很恐怖，但这正是Oracle设计缜密的地方。Oracle在代码中埋入了很多性能探针，还添加了很多可选择的代码旁路开关。

性能探针给了使用者很多的有用信息，对于数据库的优化和诊断来说，可以获益不少。而各种代码执行的旁路开关，也可以让使用者在特定的情况下，选择合适自己环境的路径。

 

在Oracle 10.2 之后，Oracle对很多bug fix添加了旁路开关，允许通过设置初始化参数或者会话级参数，跳过或启用某个bug fix引入的代码逻辑。

这个参数就是 “_fix_control”。

它就是一个旁路开关。下面，我简单介绍下使用方法：

在会话中不启用某个bug的fix：

Alter session set “_fix_control”=“bugno:off”;Alter session set “_fix_control”=“bugno:0”;

在会话中启用某个bug的fix：

Alter session set “_fix_control”=“bugno:on”;Alter session set “_fix_control”=“bugno:1”;

在会话中指定多个_fix_control参数：

Alter session set “_fix_control”=“bug1:0”,”bug2:1”…,”bugn:off;

当然，把alter session改为alter system就变成系统级生效了。

 

正常情况下，我们都是已知一个bug, 然后通过控制这个bug的fix_control参数来关闭或启用。但有时候，我们在不知道哪个bug影响了系统时，也可以通过“暴力破解”的方式来发现具体的bug。

下面从一个性能故障案例来说明如何进行“暴力破解”。

 

某客户系统突然CPU飙升，大量活动会话积压

大量以前毫秒级的SQL, 突然变成平均执行3-5秒

SQL并不复杂：

select count(distinct(t1.company)) from V_xxxx_USER t1, V_xxxx_APPLY t2 where t1.id = t2.id and t1.cert_no = :1 and t2.insert_time > sysdate – 90

其中有两个视图：

V_xxxx_USER:

select ID,... from c_xxx_userunion allselect b.app_no ID,... from xxxx_p_info b, xxxx_info xxxx_infowhere b.app_no = e1.app_no and b.app_no = e2.app_no

视图中的2个表，都有很好选择度的索引：

c_xxx_user: cert_noxxxx_p_info: cert_no

V_xxxx_APPLY：

select ID,... from C_xxxx_APPLYunion all select a.app_no ID, ... from xxxx_MAINC_xxxx_APPLY: id

视图中的2个表，也都有很好选择度的索引：

xxxx_MAIN: id

C_xxxx_APPLY: id

 

看看执行计划，出现3个新的执行计划，性能非常差，而好的执行计划单次仅为0.7ms：

细看执行计划，好的执行计划cost 372：

差的3个执行计划，cost都是286k:

 

好和差的执行计划最大的区别是，好的走了UNION ALL PUSHED PREDICATE。

而差的执行计划没走UNION ALL PUSHED PREDICATE，也就导致没有用上视图里两个表的id索引，走了其他索引或者干脆全表扫，因此性能变得非常差。

 

尝试 SQL Profile 和 SPM进行绑定：

DECLAREclsql_text CLOB;BEGINSELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = ‘&SQL_ID’; DBMS_SQLTUNE.IMPORT_SQL_PROFILE( sql_text => clsql_text, profile => sqlprof_attr(….),name => 'PROFILE_&SQL_ID', force_match => TRUE );END;/

但都无法稳定生效，时而复发。

优化器选择执行计划的基本原则是选COST低的执行计划。而异变/异常的通常现象是执行计划评估cost错误，导致差的执行计划COST比好的执行计划低。显然，这次的问题，没有选择cost低的执行计划。

 

这时候，开始怀疑是某个优化器参数或者某个bug fix导致的问题，于是开始考虑尝试测试一些优化器参数或者_fix_control参数。

由于一开始不清楚是哪个优化器参数或者_fix_control参数，手动逐个测试显然太慢，只能考虑用自动代码来遍历。

 

所以，思路就是：

1. 遍历优化器相关参数+遍历_fix_control参数

2. 修改改参数

3. 检查问题有没解决

显然，由于单次执行很慢，直接通过执行SQL的方式来检查，耗时太大，不可行。考虑到问题的关键是执行计划中没有用到UNION ALL PUSHED PREDICATE，可以通过仅仅生成执行计划，并检查执行计划中是否存在UNION ALL PUSHED PREDICATE步骤的方式来判断。

 

优化器相关参数的来源有3个：

v$sys_optimizer_env或者v$sess_optimizer_env

x$ksppi+x$ksppcv (v$parameter的基表)

10053 trace里的内容

 

......

 

_fix_control参数来源：

v$system_fix_control或者 v$session_fix_control;

示例代码：

DeclareTarget_value number;beginFor bugs in (select * from v$session_fix_controlwhere sid = (select sid from v$mystat where rownum=1)) loopIf(bugs.value=1 ) thenTarget_value :=0;ElseTarget_value:=1;End if;// fix_controlexecute immediate 'alter session set "_fix_control"='‘’||bugs.bugno||':’||target_value||''‘’;Execute immediate ‘explain plan set statement_id = ‘|| bug.bugno ||‘ for… <有问题的SQL> …’; execute immediate 'alter session set "_fix_control"='‘’||bugs.bugno||':’||bugs.value||''‘’;end Loop;For para in (select name,value,decode(value,’TRUE’,’FALSE’,’FALSE’,’TRUE’) target_valuefrom …where value in (’TRUE’,’FALSE’)) loop//优化器参数execute immediate 'alter session set "'|| para.name||'"=‘||para.target_value;… --探测execute immediate 'alter session set "'|| para.name||'"=‘||para.value;…

然后，检查哪个fix_control或者初始化参数能解决问题：

Select statement_id from plan_tableWhere operation = ‘UNION ALL PUSHED PREDICATE’;

最终发现是BUG 4127058 导致。

 

这就是我想要与大家进行分享的，谢谢！