Oracle SQL 高版本与Cursor Mutex X争用严重性能故障分析

2021-11-23 15:12:44

近遇到一个系统，在游标失效后，并发业务导致了大量的Cursor Mutex X，基本是同一个SQL
改SQL的版本高达900多个。
通过分析日志,trc文件以及AWR，初步确定是bug导致，目前我们的补丁是18年。参考如下文档。
Bug 28794230 - 12.2 Cursor Mutex X Due To Sql Not Shared Because Of BIND_EQUIV_FAILURE (Doc ID 28794230.8)
这个bug的影响范围是Versions >= 12.2.0.1 but BELOW 20.1
而fixed的数据库版本为
20.1.0
19.10.0.0.210119 (Jan 2021) Database Release Update (DB RU)
19.9.0.0.201020 (Oct 2020) Database Release Update(DB RU)
18.13.0.0.210119 (JAN 2021) Database Release Update (DB RU)
12.1.0.2.210119 (JAN 2021) Database Proactive Bundle Patch
12.2.0.1.191015 (Oct 2019) Bundle Patch for Windows Platforms
所以，要么我们升级数据库，要么使用workaound解决，如下所示，其目的是关闭统计信息反馈和Oracle acs

Several alternatives:
_optimizer_use_feedback=false
_optimizer_adaptive_cursor_sharing=false
_optimizer_extended_cursor_sharing_rel=none
_fix_control='23596611:OFF' may also help in some cases

建议关闭acs:
1. 导致额外的硬解析，2.可能造成子游标过多。3.存储子游标对shared pool有压力。

关闭自适应游标共享
alter system set "_optimizer_use_feedback"=false scope=spfile;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=spfile;

alter system set "_optimizer_use_feedback"=false;
alter system set "_optimizer_adaptive_cursor_sharing"=false;
alter system set "_optimizer_extended_cursor_sharing_rel"=none;
关闭Cardinality feedback
alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile;

alter system set "_fix_control"='23596611:OFF';
查询设置结果。
SQL> col KSPPDESC for a50
SQL> set line 200
SQL> SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm in ('_optimizer_use_feedback','_optimizer_adaptive_cursor_sharing','_optimizer_extended_cursor_sharing_rel','_fix_control')
SQL> /

KSPPINM KSPPSTVL KSPPDESC
---------------------------------------- ------------------------------ --------------------------------------------------
_fix_control 7555510:OFF bug fix control parameter
_optimizer_extended_cursor_sharing_rel NONE optimizer extended cursor sharing for relational operators
_optimizer_adaptive_cursor_sharing FALSE optimizer adaptive cursor sharing
_optimizer_use_feedback FALSE optimizer use feedback