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
Lesson: Hoard all useful MOS notes! :-)
True Joel, true… and always have an offline copy of the documentation – something else I rarely do.