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

本期，我们请到云和恩墨性能优化专家 罗海雄 先生，罗老师是一名 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_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

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

c_xxx_user: cert_no
xxxx_p_info: cert_no

V_xxxx_APPLY：

select ID,... from C_xxxx_APPLY
union all
 select a.app_no ID, ... from xxxx_MAIN
C_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进行绑定：

DECLARE
clsql_text CLOB;
BEGIN
SELECT 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;

示例代码：

Declare
Target_value number;
begin
For bugs in (select * from v$session_fix_control
where sid = (select sid from v$mystat where rownum=1)
) loop
If(bugs.value=1 ) then
Target_value :=0;
Else
Target_value:=1;
End if;
// fix_control
execute 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_value
from …
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_table
Where operation = ‘UNION ALL PUSHED PREDICATE’;

最终发现是BUG 4127058 导致。

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

PPT下载链接：https://www.modb.pro/doc/1366