Oracle系统迁移导致SQL性能下降
Tags： Oracle
宝岛某客户，系统迁移后（主机迁移，数据库版本相同，都是11.2.0.3），某个SQL性能严重下降，执行时间由原系统的的0.001秒，变成了131秒多，严重影响了业务的正常使用：
（注：其中#1执行计划是在使用了sql profile固定后生成的，新系统开始只有一个#2的执行计划）
SQL代码如下：
SELECT *
FROM MB_PN_MESSAGE
WHERE (UDID = : 1 OR CUST_ID = : 2) AND
(MB_PN_MESSAGE.APP IS NULL OR MB_PN_MESSAGE.APP = 'MB')
AND MESSAGE_DATE <= SYSDATE
ORDER BY MESSAGE_DATE DESC ,MESSAGE_NUMBER DESC;
迁移后新系统的执行计划为：
根据谓词条件，使用message_date字段上的索引明显是非常差的选择。
原系统的执行计划是：
原系统SQL执行计划使用了选择性好的UDID和CUST_ID两个字段上独立索引，做Bitmap OR操作，这个计划才是最优的。
发现这个区别后，我们马上使用coe_xfr_sql_profile.sql脚本，将原系统的执行计划导出（生成sql脚本），然后在新的系统上执行该SQL，这样就通过sql profile，将新系统的执行计划与原系统保持了一致。快速恢复系统运行正常后，接下来我们就可以从容地对这个问题的原因进行分析了。
因为MESSAGE_DATE字段创建的索引为降序索引，DDL如下：
CREATE INDEX "INHBMGR"."MB_PN_MESSAGE_MESSAGE_DATE" ON "INHBMGR"."MB_PN_MESSAGE" ("MESSAGE_DATE" DESC)
我们团队的资深专家很快就把问题怀疑到bug上，经过一番努力搜索，发现一个疑似的bug：
Bug 11072246 : NON OPTIMAL INDEX IS USED WHEN INDEX COLUMN IS IN DESC ORDER
Bug 11072246 - Wrong Cardinality estimations for columns with DESC indexes (Doc ID 11072246.8)
bug的描述与现场的情况基本一致， 但是，大家心中又有一个疑问，既然是bug，为什么在原系统上没有触发？而再看bug的修复情况，在11.2.0.3版本的某个patch set已经得到修复，难道说两个系统的patch set不一致？
这个时候，我做了一个test case，在11.2.0.3 （没有任何patch）和12.1.0.1上分别执行SQL，对比执行计划后，基本可以确定为bug：
在客户现场仔细对比原系统和新系统的patch信息后，确实是新系统的patch比原系统要少，而缺少的patch恰恰就导致了SQL触发了bug。我们在测试环境也证明了这一点：11.2.0.3.7打11072246 patch前后的对比与现场情况完全一致。
总结：
系统迁移，不光数据库的大版本要相同，patch也要保持一致。
coe_xfr_sql_profile.sql脚本最适合在这种情况下使用。
参考
参考：老虎刘谈SQL优化
