Troubleshooting ora-7445 [kkqteParseSqlForPG()+1840] in 11g r2, Table expansion transformation

今天接到有人反映有套数据库db alert中会经常性的出现ora-7445 [kkqteParseSqlForPG()+1840], 下面简单的记录下这个问题。
# DB Alert

Thread 1 advanced to log sequence 139083 (LGWR switch)
  Current log# 1 seq# 139083 mem# 0: +DATAGP/anbob/onlinelog/group_1.269.854275533
  Current log# 1 seq# 139083 mem# 1: +DATAGP/anbob/onlinelog/group_1.268.854275533
Mon Sep 21 02:05:22 2015
Exception [type: SIGSEGV, Invalid permissions for mapped object] [ADDR:0x63] [PC:0x107C00D30, kkqteParseSqlForPG()+1840] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_22283288.trc  (incident=253347):
ORA-07445: 出现异常错误: 核心转储 [kkqteParseSqlForPG()+1840] [SIGSEGV] [ADDR:0x63] [PC:0x107C00D30] [Invalid permissions for mapped object] []
Incident details in: /u01/app/oracle/diag/rdbms/anbob/anbob1/incident/incdir_253347/anbob1_ora_22283288_i253347.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
复制

# 7445 trace file

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:	AIX
ORA-07445: 出现异常错误: 核心转储 [kkqteParseSqlForPG()+1840] [SIGSEGV] [ADDR:0x63] [PC:0x107C00D30] [Invalid permissions for mapped object] []
----- Current SQL Statement for this session (sql_id=am4xxtggvg67g) -----
SELECT * FROM anbob.V_ITPRO_LOG_SSO WHERE RESULT != 'xxxxxxx' and LOGINTIME>to_date('2015-09-21 01:58:21','yyyy-mm-dd hh24:mi:ss') and LOGINTIME<=to_date('2015-09-21 02:00:21','yyyy-mm-dd hh24:mi:ss') order by LOGINTIME asc
-----CallStackTrace-----
skdstdst()+40<ksedst1()+112<ksedst()+40<dbkedDefDump()+1516<ksedmp()+72<ssexhd()+267248bc<kkqteGeneratePreds()+404<
kkqteTransform()+444<kkqctdrvTE()+1552<kkqtedtr()+168<qksqbApplyToQbcLoc()+372<qksqbApplyToQbc()+5<kkqctdrvTD()+3596<
kkqtedrv()+88<kkqdrv()+1060<kkqctdrvIT()+720<apadrv()+2820<opitca()+3660<kksLoadChild()+8296<kxsGetRuntimeLock()<
kksfbc()+12876<kkspsc0()+1240<kksParseCursor()+11<opiosq0()+1844<kpooprx()+332<kpoal8()+6872<opiodr()+720<
ttcpip()+1028<opitsk()+1508<opiino()+940<opiodr()+720<opidrv()+1132<sou2o()+136>opidrv>opimai_real()+608>ssthrdmain()+268>opimai_real()>main()+204
Starting SQL statement dump
SQL Information
user_id=97 user_name=YUNSHJ module=JDBC Thin Client action=
sql_id=am4xxtggvg67g plan_hash_value=0 problem_type=3
----- Current SQL Statement for this session (sql_id=am4xxtggvg67g) -----
SELECT * FROM anbob.V_ITPRO_LOG_SSO WHERE RESULT != 'xxxxxx' and LOGINTIME>to_date('2015-09-21 01:58:21','yyyy-mm-dd hh24:mi:ss') and LOGINTIME<=to_date('2015-09-21 02:00:21','yyyy-mm-dd hh24:mi:ss') order by LOGINTIME asc sql_text_length=233 sql=SELECT * FROM anbob.V_ITPRO_LOG_SSO WHERE RESULT != 'xxxxxx' and LOGINTIME>to_date('2015-09-21 01:58:21','yyyy-mm-dd hh24:mi:ss') and LOGINTIME<=to_date('2015-09-21 02:00:21','yyyy-mm-dd hh24:mi:ss') order by LOGINTIME asc
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 11.2.0.3
...
...
...
_optimizer_table_expansion          = true
...
复制

Note:
触发错误的SQL 是条SELECT FROM VIEW的查询，在SQLPLUS 中查询无报错， 而且查看view的基表存在partition index并无unusable 状态local分区， 在MOS中发现ORA-07445, core dump [kkqteParseSqlForPG()] [SIGSEGV] upon execution of a SQL (文档 ID 1327807.1) 记录，
有可能是11 r2 引入的一个新特性Optimizer Table Expansion Transformation (介绍可参考https://blogs.oracle.com/optimizer/entry/optimizer_transformations_table_expansion)引起, 该特性在使用分区索引时分区裁剪使用索引访问的SQL转换时可以提升性能，受隐藏参数
_optimizer_table_expansion控制（默认启用）。 根据MOS中记录的CALL Stack 和本案例非常相似如下:
=== ODM Cause Justification ===
Generated stack:
kkqteGeneratePreds kkqteTransform kkqctdrvTE kkqtedtr qksqbApplyToQbcLoc qksqbApplyToQbcLoc
qksqbApplyToQbcLoc qksqbApplyToQbcLoc qksqbApplyToQbcLoc qksqbApplyToQbc
kkqctdrvTD kkqtedrv kkqdrv kkqctdrvIT apadrv
该日志提供的解决方法是
打补丁 patch 9665974且说是在11.2.0.2中修复
或
禁用参数 ALTER SYSTEM SET “_optimizer_table_expansion”=FALSE;
本案例是11.2.0.3的环境，看来该bug并未完全修复，且发现在11.2.0.4中都存在一个问题ORA-07445[qcsraic()] CALL stack描述如下
Call stack in the incident trace file written by the error includes all or some of the following functions:
qcsraic <- qcspqbDescendents <- qcspqb
< - kkmdrv <- kkqteParseSqlForPG <- kkqteGeneratePreds <- kkqteTransform <- kkqtedtr
< - qksqbApplyToQbcLoc <- qksqbApplyToQbc <- kkqtedrv <- kkqdrv <- kkqctdrvIT
< - apadrv <- opitca <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc
< - kkspsc0 <- kksParseCursor <- opiosq0 <- opiall0 <- opial7
< - opiodr <- ttcpip <- opitsk <- opiino <- opiodr
< - opidrv <- sou2o <- opimai_real <- ssthrdmain <- main
< - libc_start_main <- start
而且在Note 1959974.1记录真实原因未知， 不过解决方法也是”_optimizer_table_expansion” = false, 查询该参数确实存在的BUG不少，个人建议禁用该参数，注意修改该参数可能改变原有SQL的执行计划，建议测试后再修改生产。