[20131227]PUSH_PRED?ORA-00600 [kkocxj : pjpCtx] error is reported when running a complex query.txt
前几天生产系统出现严重的性能问题,跟查询变换有关,自己做一些探究看看.
SYSTEM> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
1.建立测试环境:
create table t11 pctfree 99 pctused 1 as select rownum id,rownum idx,rpad('t11',80,'a') name from dual connect by level
create table t12 pctfree 99 pctused 1 as select rownum id,rownum idx,rpad('t12',80,'b') name from dual connect by level
create table t21 pctfree 99 pctused 1 as select rownum id,rpad('t21',80,'c') name from dual connect by level
create table t22 pctfree 99 pctused 1 as select rownum id,rpad('t22',80,'d') name from dual connect by level
create index i_t11_id on t11(id);
create index i_t12_id on t12(id);
create index i_t21_id on t21(id);
create index i_t22_id on t22(id);
create index i_t11_idx on t11(idx);
create index i_t12_idx on t12(idx);
exec dbms_stats.gather_table_stats(user, 'T11', method_opt=>'for all columns size 1 ',no_invalidate => false);
exec dbms_stats.gather_table_stats(user, 'T12', method_opt=>'for all columns size 1 ',no_invalidate => false);
exec dbms_stats.gather_table_stats(user, 'T21', method_opt=>'for all columns size 1 ',no_invalidate => false);
exec dbms_stats.gather_table_stats(user, 'T22', method_opt=>'for all columns size 1 ',no_invalidate => false);
create view v_t1 as select * from t11 union all select * from t12 ;
create view v_t2 as select * from t21 union all select * from t22 ;
开始测试:
SYSTEM> select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.id=42;
ID C10 C20
---------- ---------- --------------------
42 t12bbbbbbb t21ccccccc
42 t11aaaaaaa t21ccccccc
42 t12bbbbbbb t22ddddddd
42 t11aaaaaaa t22ddddddd
SYSTEM> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g8963926sg3pb, child number 0
-------------------------------------
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where
v_t1.id=v_t2.id and v_t1.id=42
Plan hash value: 3439423677
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 4 | 9 (12)| 670K| 670K| 338K (0)|
| 2 | VIEW | V_T1 | 2 | 4 (0)| | | |
| 3 | UNION-ALL | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T11 | 1 | 2 (0)| | | |
|* 5 | INDEX RANGE SCAN | I_T11_ID | 1 | 1 (0)| | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T12 | 1 | 2 (0)| | | |
|* 7 | INDEX RANGE SCAN | I_T12_ID | 1 | 1 (0)| | | |
| 8 | VIEW | V_T2 | 2 | 4 (0)| | | |
| 9 | UNION-ALL | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T21 | 1 | 2 (0)| | | |
|* 11 | INDEX RANGE SCAN | I_T21_ID | 1 | 1 (0)| | | |
| 12 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 2 (0)| | | |
|* 13 | INDEX RANGE SCAN | I_T22_ID | 1 | 1 (0)| | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V_T1"."ID"="V_T2"."ID")
5 - access("ID"=42)
7 - access("ID"=42)
11 - access("ID"=42)
13 - access("ID"=42)
--可以发现sql语句很好的使用正确的索引.但是如果执行如下语句.
--select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
SYSTEM> select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
ID C10 C20
---------- ---------- --------------------
42 t12bbbbbbb t21ccccccc
42 t11aaaaaaa t21ccccccc
42 t12bbbbbbb t22ddddddd
42 t11aaaaaaa t22ddddddd
SYSTEM> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 5vzmydwgadm36, child number 0
-------------------------------------
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where
v_t1.id=v_t2.id and v_t1.idx=42
Plan hash value: 2028129758
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 40 | 669 (1)| 670K| 670K| 338K (0)|
| 2 | VIEW | V_T1 | 2 | 4 (0)| | | |
| 3 | UNION-ALL | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T11 | 1 | 2 (0)| | | |
|* 5 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 (0)| | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T12 | 1 | 2 (0)| | | |
|* 7 | INDEX RANGE SCAN | I_T12_IDX | 1 | 1 (0)| | | |
| 8 | VIEW | V_T2 | 2000 | 665 (1)| | | |
| 9 | UNION-ALL | | | | | | |
| 10 | TABLE ACCESS FULL | T21 | 1000 | 332 (0)| | | |
| 11 | TABLE ACCESS FULL | T22 | 1000 | 332 (0)| | | |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V_T1"."ID"="V_T2"."ID")
5 - access("IDX"=42)
7 - access("IDX"=42)
--这个时候出现奇怪的情况,T21,T22选择了全表扫描.说明查询转换有问题.
--但是如果写成如下:
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from t11 v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from t12 v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
--都可以很好的选择索引.修改如下也是一样,执行计划不贴了.
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,t21 v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,t22 v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
3.在11G下重复测试:
建表过程忽略..
SCOTT@test> select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
ID C10 C20
---------- ---------- --------------------
42 t11aaaaaaa t21ccccccc
42 t11aaaaaaa t22ddddddd
42 t12bbbbbbb t21ccccccc
42 t12bbbbbbb t22ddddddd
SCOTT@test> host cat /home/oracleg/sql/dpc.sql
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost'));
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5vzmydwgadm36, child number 0
-------------------------------------
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20
from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42
Plan hash value: 130698427
--------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 12 (100)|
| 1 | NESTED LOOPS | | 4 | 12 (0)|
| 2 | VIEW | V_T1 | 2 | 4 (0)|
| 3 | UNION-ALL | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T11 | 1 | 2 (0)|
|* 5 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| T12 | 1 | 2 (0)|
|* 7 | INDEX RANGE SCAN | I_T12_IDX | 1 | 1 (0)|
| 8 | VIEW | V_T2 | 1 | 4 (0)|
| 9 | UNION ALL PUSHED PREDICATE | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T21 | 1 | 2 (0)|
|* 11 | INDEX RANGE SCAN | I_T21_ID | 1 | 1 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 2 (0)|
|* 13 | INDEX RANGE SCAN | I_T22_ID | 1 | 1 (0)|
--------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SET$BE4AEC69")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T1"@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "V_T1"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$9384AC1D" "T22"@"SEL$5" ("T22"."ID"))
INDEX_RS_ASC(@"SEL$8E13D68A" "T21"@"SEL$4" ("T21"."ID"))
INDEX_RS_ASC(@"SEL$3" "T12"@"SEL$3" ("T12"."IDX"))
INDEX_RS_ASC(@"SEL$2" "T11"@"SEL$2" ("T11"."IDX"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("IDX"=42)
7 - access("IDX"=42)
11 - access("ID"="V_T1"."ID")
13 - access("ID"="V_T1"."ID")
--可以发现11G,查询变化可以使用索引.注意提示里面包含PUSH_PRED提示.
4.取出outline date的信息作为提示,在10g下执行.
select /*+
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SET$BE4AEC69")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T1"@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "V_T1"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$9384AC1D" "T22"@"SEL$5" ("T22"."ID"))
INDEX_RS_ASC(@"SEL$8E13D68A" "T21"@"SEL$4" ("T21"."ID"))
INDEX_RS_ASC(@"SEL$3" "T12"@"SEL$3" ("T12"."IDX"))
INDEX_RS_ASC(@"SEL$2" "T11"@"SEL$2" ("T11"."IDX"))
*/
v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
--出现如下提示: *
ERROR at line 24:
ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], []
--google发现如下链接:http://blog.itpub.net/11976525/viewspace-742711/
=========
ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], []
Symptoms
ORA-00600 [kkocxj : pjpCtx] error is reported when running a complex query.
Cause
Bug 7014646: INTERNAL ERROR CODE, ARGUMENTS: [KKOCXJ : PJPCTX], [], [], [], [], []
Call stack includes:
kkocxj
If a PUSH_PRED hint containing a predicate number is present in the culprit query, then this could be Bug 5637915.
Solution
Bug 7014646 is fixed in 10.2.0.5 , 11.1.0.7 and 11.2.0.1
For earlier DB versions, please apply Patch 7014646 where available
OR
use the following workaround:
SQL> conn / as sysdba
SQL> alter system set "_optimizer_push_pred_cost_based"=false;
SQL> exit
References
BUG:7014646 - ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KKOCXJ : PJPCTX], [], [], [], [], []
=========
--Bug 7014646 is fixed in 10.2.0.5 , 11.1.0.7 and 11.2.0.1,我们生产系统使用的是10.2.0.4.暂时无法解决这个问题.
--看来最简单的方法就是修改sql语句.
5.尝试修改参数看看:
alter session set "_optimizer_push_pred_cost_based"=false;
现在11G下测试看看,执行提示使用的是OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T22"."ID")),并且能正常使用索引.
在10G下,出现死循环.不停的输出,很明显10.2.0.4的版本存在bug.
修改如下:(PUSH_PRED修改为OLD_PUSH_PRED):
select /*+
OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$9AACC4F0")
OUTLINE_LEAF(@"SEL$693A5C0E")
OUTLINE_LEAF(@"SET$7BE537C4")
OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T22"."ID"))
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T1"@"SEL$1")
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "V_T1"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$693A5C0E" "T22"@"SEL$5" ("T22"."ID"))
INDEX_RS_ASC(@"SEL$9AACC4F0" "T21"@"SEL$4" ("T21"."ID"))
INDEX_RS_ASC(@"SEL$3" "T12"@"SEL$3" ("T12"."IDX"))
INDEX_RS_ASC(@"SEL$2" "T11"@"SEL$2" ("T11"."IDX"))
*/
v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
ID C10 C20
---------- ---------- --------------------
42 t11aaaaaaa t21ccccccc
42 t11aaaaaaa t22ddddddd
42 t12bbbbbbb t21ccccccc
42 t12bbbbbbb t22ddddddd
--ok执行正常!
SYSTEM> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4qfu642abbt7j, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$9AACC4F0")
OUTLINE_LEAF(@"SEL$693A5C0E") OUTLINE_LEAF(@"SET$7BE537C4")
OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T22"."ID"))
OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$4") OUTLINE(@"SEL$5")
OUTLINE(@"SET$2") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$1"
"V_T1"@"SEL$1") NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "V_T1"@"SEL$1" "V_T2"@"SEL$1") USE_NL(@"SEL$1"
"V_T2"@"SEL$1") INDEX_RS_ASC(@"SEL$693A5C0E" "T22"@"SEL$5"
("T22"."ID")) INDEX_RS_ASC(@"SEL$9AACC4F0" "T21"@"SEL$4"
("T21"."ID")) INDEX_RS_ASC(@"SEL$3" "T12"@"SEL$3" ("T12"."IDX"))
INDEX_RS_ASC(@"SEL$2" "T11"@"SEL$2" ("T11"."IDX")) */
v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from
v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42
Plan hash value: 637972453
--------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 4 | 10 (0)|
| 2 | VIEW | V_T1 | 2 | 4 (0)|
| 3 | UNION-ALL | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T11 | 1 | 2 (0)|
|* 5 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| T12 | 1 | 2 (0)|
|* 7 | INDEX RANGE SCAN | I_T12_IDX | 1 | 1 (0)|
| 8 | VIEW | V_T2 | 2 | 3 (0)|
| 9 | UNION-ALL PARTITION | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T21 | 1 | 2 (0)|
|* 11 | INDEX RANGE SCAN | I_T21_ID | 1 | 1 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 2 (0)|
|* 13 | INDEX RANGE SCAN | I_T22_ID | 1 | 1 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("IDX"=42)
7 - access("IDX"=42)
11 - access("ID"="V_T1"."ID")
13 - access("ID"="V_T1"."ID")
总结:
1.oracle的bug还真多!
2.这样看来修改sql语句比较方便.
3.如果不能修改语句,可能要使用sql profile来稳定执行计划.