Statistics on Partitioned Tables - Part 6c - C ...

I wanted to talk about a few of the bugs and patches you need to be aware of if you plan to use DBMS_STATS.COPY_TABLE_STATS. Believe me, when entering the world of stats on (sub-)partitioned objects, you had better be prepared to spend a lot of time on My Oracle Support and a little time applying patches. It's a complex picture but I'll try to simplify it as much as possible here.

10.2.0.3 or 10.2.0.4 (or, late update ... 10.2.0.5)

All of the posts so far have been based on the 10.2.0.4 patchset with additional one-off patches applied where necessary (more below). Although part of me can understand the resistance to applying new patchsets because of the additional testing, change management and outages required, I think it's asking for trouble to be on older patchsets if you're using newer features such as DBMS_STATS.COPY_TABLE_STATS. For example, as far as I'm aware COPY_TABLE_STATS is not truly supported in 10.2.0.3 but I've seen a number of people using it.

These features are being worked on continuously, as you'll see in the rest of this post, so I think you'll want to apply the latest patches on top of the latest patchset where possible.

So you should take my bitching about the new availability of 10.2.0.5 with a pinch of salt. In fact, I found myself quite excited that there might be some fixes for issues I've found. Back to 10.2.0.5 later ...

High/Low Partition Key Values

This is probably the best known of the stats copying bugs. Remember that at the end of part 6a, COPY_TABLE_STATS had copied the statistics for the new P_20100211_GROT subpartition, using the P_20100209_GROT subpartition statistics as the source. Looking at a very small sample of the subpartition column statistics that have been copied :-

*******************************
Subpartition-Level Column Stats
*******************************
SQL> select
  2     a.column_name, a.subpartition_name,
  3     a.num_distinct,
  4     display_raw(a.low_value,b.data_type) as low_val,
  5     display_raw(a.high_value,b.data_type) as high_val,
  6     b.data_type
  7  from
  8     dba_subpart_col_statistics a, dba_tab_cols b
  9  where
 10     a.owner='TESTUSER' and
 11     a.table_name='TEST_TAB1' and
 12     a.table_name=b.table_name and
 13     a.column_name=b.column_name
 14  order by 1, 2
 15  /

COLUMN_NAME                SUBPARTITION_NAME              NUM_DISTINCT LOW_VAL              HIGH_VAL            
-------------------------- ------------------------------ ------------ -------------------- --------------------
DATA_TYPE                                                                                       
--------------------                            
<<snipped>>

REPORTING_DATE             P_20100211_GROT                           1 20100209             20100209            
NUMBER

<<snipped>>

Spot the deliberate mistake? You would think that DBMS_STATS would be smart enough to know that the partition key column high and low values are implicitly different for each new partition. I added a partition to cover reporting dates 20100210 and 20100211 :-

SQL> ALTER TABLE TEST_TAB1
  2  ADD  PARTITION P_20100211 VALUES LESS THAN (20100212);

So I doubt that the high and low values for REPORTING_DATE are likely to be 20100209

It's bug number 8318020 and is mentioned in several places on the net. You can work around it by setting the column stats manually for the partition key, but you might as well just apply the correct one-off patch. Of course, once you start to apply that one-off patch, you will find a whole bunch of DBMS_STATS-related patches and I would lay good odds now that you'll actually end up having to apply a Merge Label Request (MLR), encompassing a number of these patches. 8866627 is the particular one that I applied before most of these tests and that we use at work, which also solves the next issue.

Subpartition issues

I deliberately skipped over this one when I wrote part 6a by ensuring I had 8866627 applied but it's a nasty one, quite easy to miss and not referenced in too many places on the web, although I could find at least one useful thread.

If I repeat the partition-level stats copy from Part 6a without any patches applied to 10.2.0.4, this is the result.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                                      srcpartname => 'P_20100209', dstpartname => 'P_20100210');
BEGIN dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                                      srcpartname => 'P_20100209', dstpartname => 'P_20100210'); END;
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 17408
ORA-06512: at line 

As far as I'm aware and like many of the issues I'm raising, this would only be a problem when you have subpartitions as well as partitions. The important point is that I've never seen this bug since applying 8866627 and can't recreate it on 10.2.0.5 either.

ORA-03113 during aggregation

Unfortunately, 8866627 doesn't seem to fix the next issue and I can't find any references to it on the web either, but I've been able to recreate it on several databases with different patches applied, on different O/S. First I'll confirm the patches that 8866627 includes.

Installed Top-level Products (2): 

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0

There are 2 products installed in this Oracle Home.

Interim patches (1) :

Patch  8866627      : applied on Fri Apr 23 13:04:42 BST 2010
   Created on 6 Nov 2009, 05:38:04 hrs PST8PDT
   Bugs fixed:
     7475493, 7116357, 7463138, 6718212, 7441785, 6526370, 8318020

Now, having realised that copying the statistics at the partition level doesn't work when I have only one subpartition populated with data because it doesn't copy the statistics for the unpopulated partitions, I'll try gathering the statistics on each subpartition one by one. You can try the same yourself using stats_6c.sql (note that you will have to run stats_5_6a.sql first and that this script will generate a trace file) or see my results in stats_6c.txt. The script just repopulates the load table, exchanges it with the GROT subpartition and then gathers stats on each of the subpartitions in turn. If this seems a little weird, it really isn't because it's perfectly feasible that we would have some empty subpartitions.

The stats for each subpartition are copied successfully ....

SQL> exec dbms_monitor.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_GROT', dstpartname => 'P_20100211_GROT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_JUNE', dstpartname => 'P_20100211_JUNE');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_HALO', dstpartname => 'P_20100211_HALO');

PL/SQL procedure successfully completed.

Until we get to the last subpartition. If you've been wading through the earlier posts, then you might be expecting Oracle to aggregate the subpartition stats to generate Aggregated Stats at the Partition level, now that all subpartitions have valid stats (which they should, as I've just copied them). Clearly some sort of aggregation process does kick-in, because the session is disconnected at this point.

SQL> alter session set "_minimal_stats_aggregation"=TRUE;

Session altered.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS');

SQL> show parameter user_dump_dest
ERROR:
ORA-03114: not connected to ORACLE 

The reason I set _minimal_stats_aggregation to TRUE was to make sure that I was using the default value but setting it to FALSE leads to the same end result, as you'll see from the test output posted earlier.

Because this seemed such a weird error, I decided to trace the session and here's a snippet of the resulting trace file that shows where the error occurs.

PARSING IN CURSOR #44 len=38 dep=1 uid=0 oct=7 lid=0 tim=1242214568228618 hv=624276838 ad='a03acac0'
delete from ind_online$ where obj#= :1
2END OF STMT
PARSE #44:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1242214568228606
EXEC #44:c=0,e=141,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=1242214568228961
STAT #44 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  IND_ONLINE$ (cr=3 pr=0 pw=0 time=101 us)'
STAT #44 id=2 cnt=0 pid=1 pos=1 obj=731 op='TABLE ACCESS FULL IND_ONLINE$ (cr=3 pr=0 pw=0 time=66 us)'
EXEC #7:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242214568229627
FETCH #7:c=0,e=158,p=0,cr=12,cu=0,mis=0,r=1,dep=1,og=1,tim=1242214568229851
EXEC #40:c=0,e=167,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242214568230144
FETCH #40:c=0,e=851,p=0,cr=13,cu=0,mis=0,r=1,dep=1,og=1,tim=1242214568231118
EXEC #38:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1242214568231322
FETCH #38:c=0,e=99,p=0,cr=12,cu=0,mis=0,r=1,dep=1,og=1,tim=1242214568231540
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, 
                                PC: [0x1a1baf7, qospMinMaxPartCol()+3135]
*** 2010-04-23 14:01:57.888
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qospMinMaxPartCol()+3135] 
                                  [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
BEGIN dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                      srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS'); END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xa0625b88       830  package body SYS.DBMS_STATS
0xa0625b88     18292  package body SYS.DBMS_STATS
0x97cbbe60         1  anonymous block 
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   2B3BC0FACD50 ? 2B3BC0FACDB0 ?
                                                   2B3BC0FACCF0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   2B3BC0FACD50 ? 2B3BC0FACDB0 ?
                                                   2B3BC0FACCF0 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   2B3BC0FACD50 ? 2B3BC0FACDB0 ?
                                                   2B3BC0FACCF0 ? 000000000 ?
__restore_rt()+0     call     ssexhd()             00000000B ? 2B3BC0FADD70 ?
                                                   2B3BC0FADC40 ? 2B3BC0FACDB0 ?
                                                   2B3BC0FACCF0 ? 000000000 ?
qospMinMaxPartCol()  signal   __restore_rt()       0068986E0 ? 000000000 ?
+3135                                              000000001 ? 000000000 ?
                                                   000000000 ? 0000000C2 ?
spefcmpa()+687       call     qospMinMaxPartCol()  7FFF31FFEDA0 ? 2B3BC13AABB0 ?
                                                   000000000 ? 2B3BC13AAB70 ?
                                                   000000000 ? 2B3BC13AAB30 ?
spefmccallstd()+218  call     spefcmpa()           2B3BC13AAA90 ? 2B3BC13AAAE0 ?
                                                   2B3BC129E500 ? 2B3BC13AAB70 ?
                                                   000000000 ? 2B3BC13AAB30 ?
pextproc()+41        call     spefmccallstd()      7FFF31FFEE00 ? 7FFF31FFEA80 ?

An ORA-07445 error in qospMinMaxPartCol. I tried to find some references to this but haven't been successful so far so I'm not sure whether this is a new bug or not. I was hoping it would be fixed in 10.2.0.5 but it doesn't appear so.

Installed Top-level Products (3): 

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 3 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.

The test script leads to the same error. In fact, all of the example output was generate from 10.2.0.5, but I've recreated the same results on 10.2.0.4 many times now.

Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, 
                      PC: [0x1abfe92, qospMinMaxPartCol()+3210]
*** 2010-05-02 13:56:33.603
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qospMinMaxPartCol()+3210] 
                     [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
BEGIN dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100206_OTHERS', dstpartname => 'P_20100212_OTHERS'); END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xa07618e8       838  package body SYS.DBMS_STATS
0xa07618e8     19131  package body SYS.DBMS_STATS
0xa02394b8         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   2BA713361D50 ? 2BA713361DB0 ?
                                                   2BA713361CF0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   2BA713361D50 ? 2BA713361DB0 ?
                                                   2BA713361CF0 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   2BA713361D50 ? 2BA713361DB0 ?
                                                   2BA713361CF0 ? 000000000 ?
__restore_rt()+0     call     ssexhd()             00000000B ? 2BA713362D70 ?
                                                   2BA713362C40 ? 2BA713361DB0 ?
                                                   2BA713361CF0 ? 000000000 ?
qospMinMaxPartCol()  signal   __restore_rt()       006AE9A20 ? 000000000 ?
+3210                                              000000001 ? 000000000 ?
                                                   000000000 ? 0000000C2 ?
spefcmpa()+809       call     qospMinMaxPartCol()  7FFFDBEA5280 ? 2BA71361F008 ?
                                                   000000000 ? 2BA71361EFC8 ?
                                                   000000000 ? 2BA71361EF88 ?
spefmccallstd()+122  call     spefcmpa()           2BA71361EEE8 ? 2BA71361EF38 ?
8                                                  2BA7135EE500 ? 2BA71361EFC8 ?
                                                   000000000 ? 2BA71361EF88 ?
pextproc()+41        call     spefmccallstd()      7FFFDBEA52E0 ? 7FFFDBEA4F68 ?
                                                   7FFFDBEA4D10 ? 7FFFDBEA4C78 ?
                                                   000000000 ? 2BA71361EF88 ?

There is a workaround, of course, which is to copy stats at the Partition-level after all of the Subpartitions have been populated and I've shown that at the end of the test script, but the problem with that is that we could have quite a long lag before the stats are copied, because it appears that copying up-front before the data appears (which would seem sensible) doesn't work too well, based on part 6a.

I've probably added to any confusion again, rather than reducing it, but I'll leave you with two main messages

1) You're going to encounter some bugs because some of these features are new, so you need to work hard identifying them, applying the appropriate patches and, preferably, being on later releases in the first place.

2) Things are probably much more straightforward if you stick to Table and Partition-level stats, but that's not a luxury I have on my current system.