Bug with dynamic sampling in 11.2.0.3

March 19, 2012 2 Comments

Sod’s Law part II (part I was a block corruption on production during the penultimate dress rehearsal): FIVE days before go-live of an 11gR2 upgrade, we’re hit on a development database by Oracle bug 13505390.

The symptoms of this are ORA-00600: [kkedsGetTabBlkCnt: null segment].

This is related to dynamic sampling in combination with some other features including deferred segment creation or interval partitioning.

In my particular case, it’s neither of those so the full extent of the bug is unclear. However, the good news is that the patch resolves the issue.

I have a test case which reproduced the problem in our environments involving an empty RANGE-LIST partitioned table.

This was tested :

  • initially with DEFERRED_SEGMENT_CREATION defaulting to TRUE,
  • then also tested after materialising the segments using DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS
  • and finally – because Support insisted – with DEFERRED_SEGMENT_CREATION set to FALSE and the table recreated.

None of these made any difference, which was a surprise given the nature of the error message.

Unfortunately, the old Metalink note 175982.1 which used to describe the internal mnemonic’s has disappeared, but with kkedsGetTabBlkCnt, I believe ‘kke’ is part of the SQL Costing component, ‘ds’ might well stand for dynamic sampling and ‘GetTabBlkCnt’ speaks for itself.

Anyway, as mentioned the patch resolves it.

See illustration below run on unpatched 11.2.0.3.
Note that if I have more than a certain number of partitions, the bug occurs.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> DROP TABLE test_ds_bug purge; 
DROP TABLE test_ds_bug purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE test_ds_bug 
  2  (cmpy_num    NUMBER(3,0)  NOT NULL
  3  ,pos_date    DATE         NOT NULL)
  4  PARTITION BY RANGE ("POS_DATE") 
  5  SUBPARTITION BY LIST ("CMPY_NUM") 
  6  SUBPARTITION TEMPLATE ( 
  7    SUBPARTITION "ABC" VALUES ( 14 ), 
  8    SUBPARTITION "XYZ" VALUES ( 2 ), 
  9    SUBPARTITION "DEF" VALUES ( DEFAULT ) ) 
 10  (PARTITION "P_2012FEB13"  
 11   VALUES LESS THAN (TO_DATE(' 2012-02-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 12   PARTITION "P_2012FEB14"  
 13   VALUES LESS THAN (TO_DATE(' 2012-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 14   PARTITION "P_2012FEB15"  
 15   VALUES LESS THAN (TO_DATE(' 2012-02-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 16   PARTITION "P_2012FEB16"  
 17   VALUES LESS THAN (TO_DATE(' 2012-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 18   PARTITION "P_2012FEB17"  
 19   VALUES LESS THAN (TO_DATE(' 2012-02-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 20   PARTITION "P_2012FEB18"  
 21   VALUES LESS THAN (TO_DATE(' 2012-02-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 22   PARTITION "P_2012FEB19"  
 23   VALUES LESS THAN (TO_DATE(' 2012-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 24   PARTITION "P_2012FEB20"  
 25   VALUES LESS THAN (TO_DATE(' 2012-02-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 26   PARTITION "P_2012FEB21"  
 27   VALUES LESS THAN (TO_DATE(' 2012-02-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 28   PARTITION "P_2012FEB22"  
 29   VALUES LESS THAN (TO_DATE(' 2012-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 30   PARTITION "P_2012FEB23"  
 31   VALUES LESS THAN (TO_DATE(' 2012-02-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 32   PARTITION "P_2012FEB24"  
 33   VALUES LESS THAN (TO_DATE(' 2012-02-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 34   PARTITION "P_2012FEB25"  
 35   VALUES LESS THAN (TO_DATE(' 2012-02-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 36   PARTITION "P_2012FEB26"  
 37   VALUES LESS THAN (TO_DATE(' 2012-02-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 38   PARTITION "P_2012FEB27"  
 39   VALUES LESS THAN (TO_DATE(' 2012-02-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 40   PARTITION "P_2012FEB28"  
 41   VALUES LESS THAN (TO_DATE(' 2012-02-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 42   PARTITION "P_2012FEB29"  
 43   VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 44   PARTITION "P_2012MAR01"  
 45   VALUES LESS THAN (TO_DATE(' 2012-03-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 46   PARTITION "P_2012MAR02"  
 47   VALUES LESS THAN (TO_DATE(' 2012-03-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 48   PARTITION "P_2012MAR03"  
 49   VALUES LESS THAN (TO_DATE(' 2012-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 50   PARTITION "P_2012MAR04"  
 51   VALUES LESS THAN (TO_DATE(' 2012-03-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 52   PARTITION "P_2012MAR05"  
 53   VALUES LESS THAN (TO_DATE(' 2012-03-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 54   PARTITION "P_2012MAR06"  
 55   VALUES LESS THAN (TO_DATE(' 2012-03-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 56   PARTITION "P_2012MAR07"  
 57   VALUES LESS THAN (TO_DATE(' 2012-03-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 58   PARTITION "P_2012MAR30"  
 59   VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));

Table created.

SQL> var rico_today varchar2(11)
SQL> var cmsg_cmpy_num number
SQL> exec :rico_today := '17-MAR-2012'

PL/SQL procedure successfully completed.

SQL> exec :cmsg_cmpy_num := 1;

PL/SQL procedure successfully completed.

SQL>     SELECT * 
  2      FROM test_ds_bug TP 
  3      WHERE TP.POS_DATE  = to_date(:RICO_TODAY,'DD-MON-YYYY') 
  4        AND TP.CMPY_NUM  = :CMSG_CMPY_NUM; 
    FROM test_ds_bug TP
         *
ERROR at line 2:
ORA-00600: internal error code, arguments: [kkedsGetTabBlkCnt: null segment], [76], [], [], [], [], [], [], [], [], [],
[]

But if I comment out one of the partitions, no bug:

SQL> DROP TABLE test_ds_bug purge; 

Table dropped.

SQL> CREATE TABLE test_ds_bug 
  2  (cmpy_num    NUMBER(3,0)  NOT NULL
  3  ,pos_date    DATE         NOT NULL)
  4  PARTITION BY RANGE ("POS_DATE") 
  5  SUBPARTITION BY LIST ("CMPY_NUM") 
  6  SUBPARTITION TEMPLATE ( 
  7    SUBPARTITION "ABC" VALUES ( 14 ), 
  8    SUBPARTITION "XYZ" VALUES ( 2 ), 
  9    SUBPARTITION "DEF" VALUES ( DEFAULT ) ) 
 10  (--PARTITION "P_2012FEB13"  
 11   --VALUES LESS THAN (TO_DATE(' 2012-02-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 12   PARTITION "P_2012FEB14"  
 13   VALUES LESS THAN (TO_DATE(' 2012-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 14   PARTITION "P_2012FEB15"  
 15   VALUES LESS THAN (TO_DATE(' 2012-02-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 16   PARTITION "P_2012FEB16"  
 17   VALUES LESS THAN (TO_DATE(' 2012-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 18   PARTITION "P_2012FEB17"  
 19   VALUES LESS THAN (TO_DATE(' 2012-02-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 20   PARTITION "P_2012FEB18"  
 21   VALUES LESS THAN (TO_DATE(' 2012-02-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 22   PARTITION "P_2012FEB19"  
 23   VALUES LESS THAN (TO_DATE(' 2012-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 24   PARTITION "P_2012FEB20"  
 25   VALUES LESS THAN (TO_DATE(' 2012-02-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 26   PARTITION "P_2012FEB21"  
 27   VALUES LESS THAN (TO_DATE(' 2012-02-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 28   PARTITION "P_2012FEB22"  
 29   VALUES LESS THAN (TO_DATE(' 2012-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 30   PARTITION "P_2012FEB23"  
 31   VALUES LESS THAN (TO_DATE(' 2012-02-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 32   PARTITION "P_2012FEB24"  
 33   VALUES LESS THAN (TO_DATE(' 2012-02-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 34   PARTITION "P_2012FEB25"  
 35   VALUES LESS THAN (TO_DATE(' 2012-02-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 36   PARTITION "P_2012FEB26"  
 37   VALUES LESS THAN (TO_DATE(' 2012-02-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 38   PARTITION "P_2012FEB27"  
 39   VALUES LESS THAN (TO_DATE(' 2012-02-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 40   PARTITION "P_2012FEB28"  
 41   VALUES LESS THAN (TO_DATE(' 2012-02-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 42   PARTITION "P_2012FEB29"  
 43   VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 44   PARTITION "P_2012MAR01"  
 45   VALUES LESS THAN (TO_DATE(' 2012-03-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 46   PARTITION "P_2012MAR02"  
 47   VALUES LESS THAN (TO_DATE(' 2012-03-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 48   PARTITION "P_2012MAR03"  
 49   VALUES LESS THAN (TO_DATE(' 2012-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 50   PARTITION "P_2012MAR04"  
 51   VALUES LESS THAN (TO_DATE(' 2012-03-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 52   PARTITION "P_2012MAR05"  
 53   VALUES LESS THAN (TO_DATE(' 2012-03-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 54   PARTITION "P_2012MAR06"  
 55   VALUES LESS THAN (TO_DATE(' 2012-03-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 56   PARTITION "P_2012MAR07"  
 57   VALUES LESS THAN (TO_DATE(' 2012-03-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')), 
 58   PARTITION "P_2012MAR30"  
 59   VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));

Table created.

SQL>     SELECT * 
  2      FROM test_ds_bug TP 
  3      WHERE TP.POS_DATE  = to_date(:RICO_TODAY,'DD-MON-YYYY') 
  4        AND TP.CMPY_NUM  = :CMSG_CMPY_NUM; 

no rows selected
Advertisements

Filed under 11gR2, dynamic sampling, oracle

2 Responses to Bug with dynamic sampling in 11.2.0.3

  1. jgarry says:
    March 20, 2012 at 11:47 pm

    Lesson: Hoard all useful MOS notes! 🙂

    Reply
  2. Dom Brooks says:
    March 21, 2012 at 9:58 am

    True Joel, true… and always have an offline copy of the documentation – something else I rarely do.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Google+ photo

You are commenting using your Google+ account. ( Log Out / Change )

Cancel

Connecting to %s

%d bloggers like this: