ITPub博客

首页 > 数据库 > Oracle > [20131227]ORA-00600 [kkocxj : pjpCtx] error is reported when running ....txt

[20131227]ORA-00600 [kkocxj : pjpCtx] error is reported when running ....txt

原创 Oracle 作者:lfree 时间:2013-12-30 16:24:00 0 删除 编辑
[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<=1e3;
create table t12 pctfree 99 pctused 1 as select rownum id,rownum idx,rpad('t12',80,'b') name from dual connect by level<=1e3;
create table t21 pctfree 99 pctused 1 as select rownum id,rpad('t21',80,'c') name from dual connect by level<=1e3;
create table t22 pctfree 99 pctused 1 as select rownum id,rpad('t22',80,'d') name from dual connect by level<=1e3;

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 <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb <- apaqbdDescendents <- apaqbdListReverse
   <- apaqbd <- kkqctCostTransfQB <- kkqctdrvJP <- kkqjpdttr <- kkqjpdttr <- kkqctdrvTD <- kkqjpddrv
      <- kkqdrv <- kkqctdrvIT <- apadrv <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild
         <- kxsGetRuntimeLock <- kksfbc <- kkspbd0 <- kksParseCursor <- opiosq0 <- opipls <- opiodr
            <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <- psddr0 <- psdnal

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来稳定执行计划.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1065675/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2669
  • 访问量
    6427242