ANBOB™

之前分享过oracle split分区的内部检查方式《 oracle fast split partition 》，我们一个客户目前还是人肉拆分区模式，每月10余万的分区， 最近遇到一个Oracle 19c（19.9） 拆分区时ora-600 错误[kkpapDIPObjNum1]  ， 问题是当split分区时递归的分区检索SQL, 分区列上只有全局分区,分区裁剪的分区未发现任何分区数据。

kkpapDIPObjNum1==> kernel compile compilation partitioning…

SQL:

[TOC00003]
----- Current SQL Statement for this session (sql_id=f19turzmr9u50) -----
select /*+ FIRST_ROWS(1) PARALLEL("xxxxx", 1) */ 1 from NO_CROSS_CONTAINER("xxx"."xxxxx") PARTITION ("PART_999_MAX") where ( ( ( ( "REGION" > 999 ) ) OR ( "REGION" = 999 AND ( "CREATEDATE" >= TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) ) ) ) and rownum < 2
[TOC00003-END]

Call Stack:

kkpapDIPObjNum>> kkopBuildPnumPred >> kkoUpdateFroAnn>> kkoipt >> kkoqbc

Demo

SQL> create table ANBOB.torder
  2   (id int ,
  3   region number,
  4   timeid number,
  5   xxx number
  6   )
  7   partition by range (region,timeid)
  8   (
  9   partition p_110_2020 values less than(110,2021),
 10   partition p_110_2021 values less than(110,2022)
 11   );

Table created.

SQL> @seg ANBOB.torder

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         4 ANBOB                 TORDER                         P_110_2020                     TABLE PARTITION      IDX313                                512        683     971913
         4 ANBOB                 TORDER                         P_110_2021                     TABLE PARTITION      IDX313                                512        683     972425

SQL>  exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&TOWN' , tabname => '&TNAME' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT',  degree => 8,no_invalidate=>false); 
Enter value for town: ANBOB
Enter value for tname: torder

PL/SQL procedure successfully completed.

SQL> select 1 from ANBOB.torder partition(p_110_2021) where (region>110) and rownum<2;
 no rows selected 

SQL> create index ANBOB.idx_torder on ANBOB.torder(id,region);

Index created.

SQL>  exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&TOWN' , tabname => '&TNAME' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT',  degree => 8,no_invalidate=>false); 
Enter value for town: ANBOB
Enter value for tname: torder

PL/SQL procedure successfully completed.

SQL> select 1 from ANBOB.torder partition(p_110_2020) where (region>110) and rownum<2; 
select 1 from ANBOB.torder partition(p_110_2020) where (region>110) and rownum<2
                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpapDIPObjNum1], [], [], [], [], [], [], [], [], [], [], []

问题出在创建的全局索引后。

SQL> select /*+full(t)*/ 1 from ANBOB.torder partition(p_110_2020) t where (region>110) and rownum<2;
no rows selected

SQL> explain plan for select 1 from ANBOB.torder partition(p_110_2020) where (region>110) and rownum<2;
explain plan for select 1 from ANBOB.torder partition(p_110_2020) where (region>110) and rownum<2
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpapDIPObjNum1], [], [], [], [], [], [], [], [], [], [], []

SQL> create index ANBOB.idx_torder_l on ANBOB.torder(region) local;
Index created.

SQL> explain plan for select 1 from ANBOB.torder partition(p_110_2020) where (region>110) and rownum<2;
Explained.

SQL> @x2

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 2063533098

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |     4 |     1   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY         |              |       |       |            |          |       |       |
|   2 |   PARTITION RANGE EMPTY|              |     1 |     4 |     1   (0)| 00:00:01 |INVALID|INVALID|
|*  3 |    INDEX RANGE SCAN    | IDX_TORDER_L |     1 |     4 |     1   (0)| 00:00:01 |INVALID|INVALID|
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2) 3 - access("REGION">110)

16 rows selected.

SQL> select 1 from ANBOB.torder partition(p_110_2020) where (region>110) and rownum<2;
no rows selected

Note:
这是oracle的已知Bug 31667096， 影响19.6 19.7 19.9版本， 在19.11 RU引入补丁。除了安装对应的补丁，发现创建索引列的local索引可以临时解决。

打赏

微信扫一扫，打赏作者吧～