Doug's Oracle Blog

Is it really that time already? I'll be working in London, but for those of you who can make it, this just in from Thomas Presslie ...

"The annual Scottish Oracle Conference is being held in Glasgow on 27th May 2010.  Now with five streams and keynote by David Callaghan, Senior Vice President Oracle, UK, Ireland and Israel a local event in Scotland not to be missed!   More information can be found at http://scotland.ukoug.org/ The OUG Scotland Chairman, Thomas Presslie, has some free places to give away to readers of Doug's Oracle Blog.  Please email Thomas tpresslie@pisec.org for details of the discount code for registration."

Sorry I'll miss it

I will get back to the stats stuff at some point, but I'm quite busy at the moment working on something that I can't talk too much about, but which is throwing up enough generic issues to talk about. This is one that I meant to blog about ages ago when I first noticed it but when it caused us some problems last week, it was a useful reminder.

In summary you need to be careful when you upgrade to 11g because Resource Manager is enabled by default!

I don't want to blog about the ins and outs of Resource Manager and whether it's a good thing or not, but I do think this is a pretty extreme change to implement without a lot of surrounding publicity. It's a bit like the auto stats gather job that appeared in 10g that caused so many problems for Oracle users. It seems like it might be a good idea, but would you really want to introduce it on to a stable system that you're upgrading to 11g?

But rather than just talk about the change, I wanted to highlight how I first realised it was going on ...




Yes, a pretty picture (albeit not too legible at that resolution, even when you click the thumbnail to see the bigger version). This is the sort of change that could have completely passed me by (and I am utterly convinced it has others) but, because I have a habit of looking at the Top Activity page for any system I'm working on, this activity leaps out at me as the new pale green wait class - Scheduler - which sits just above CPU + Wait for CPU (the addition of + Wait for CPU is one of the tiny details I really like in 11g). I should say that the specific event these sessions are waiting on is "resmgr:cpu quantum".

As soon as I upgraded my first laptop db to 11g (quite a while ago now) I noticed the additional activity and though, mmmm, that's interesting and when I checked the resource_manager_plan parameter, it was set to DEFAULT_PLAN, to my surprise.

Low and behold, whilst working on an 11gR2 system last week that we couldn't get enough parallelism out of, I checked and the DEFAULT_PLAN was enabled. Once it was disabled, like so ....

alter system set resource_manager_plan=''; 

Everything started running as expected.

So I went home to do a little background research and the first obvious place to look was the documentation. But that's pretty misleading because it says there is no default value for resource_manager_plan. I checked v$PARAMETER and, sure enough, the default is not DEFAULT_PLAN. So I started to doubt myself. Maybe it was just because I'd created Mickey Mouse databases on my laptop, using the GUI. However, that wouldn't apply to the database I'm working on and, after some discussion with the DBA who completed the upgrade, it became clear that the Upgrade Assistant (dbua) sets this parameter.

But that's not all. In total, 11gR2 creates 10 plans as far as I can tell and DEFAULT_PLAN is not the only one used, but maintenance plans are also used during maintenance windows. Here is my laptop database right now, while it's in a maintenance window.

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      SCHEDULER[0x3003]:DEFAULT_MAIN
                                                 TENANCE_PLAN

I can imagine this leading to a lot of confusion, so thanks again to Jari Kuhanen for pointing out this Metalink Note to me that covers it.

Definitely something to look out for but, in fairness to Oracle, if I had re-read the relevant documentation it is all clear in there. I wonder how many people do that before every upgrade though?

I've been meaning to blog about a couple of interesting events coming up in London during the same week in the very near future - the week after next.

Oracle University are running - Advanced Oracle 10g Performance Analysis and Tuning with Kyle Hailey on 3rd - 4th June. For those of you who aren't aware of Kyle, he was one of the primary movers behind tools like OEMs Top Activity when he worked at Oracle, one of the guys behind ashmasters.com, is a member of the Oak Table Network and has a long history in the Oracle world. You can read more about him at his website.

Meanwhile Miracle Benelux are running two of Tanel Poder's seminars back to back. Advanced Oracle SQL Tuning (1st -3rd June) followed immediately by Oracle Partitioning and Parallel Execution for Performance on 4th June. I imagine most readers are more than familiar with Tanel's work via his blog, presentations and forum contributions and know that he's absolutely top-notch.

They both look like terrific events and I would have attended one of them if it wasn't for the fact that I have a holiday booked in the U.S. that week. Which one? I really don't know because I know they'll both be excellent, so I suggest you read the agendas carefully and pick the one that you think has the most appropriate agenda for your needs.

Not long after that is an event I'm really looking forward to. Marco Gralike has been good enough to organise for me to come and spend a day at Amis showing some of their DBAs what a valuable tool OEM can be in performance analysis. As it's an onsite course I'm going to trim down the normal two-day course I do for Oracle and hopefully we can focus on the best stuff and have some fun, as well as learning something new. One of the innovations that Marco and Amis have instigated is running informal evening sessions so I'll be doing one of those the evening before. I think I'm right in thinking that it's free and open to all and registration details will appear later (but I'm sure Marco will correct me if I'm wrong). Could this be the very first demonstration of OEM whilst drinking beer? We'll see

I'm really excited about this one and extremely grateful to Marco for organising it. I love talking about Oracle's modern performance analysis tools regardless, but it's a while since I've been in Holland and I'll get to catch up with Marco and people like Jacco Landlust and Anjo Kolk who I only ever see at conferences. In fact, I'll be staying over for some of the weekend to get a little more time with friends.

Now I just need to make sure it's good enough to justify Marco's faith in me

I'm pretty self-concious of the amount of waffle that surrounds any technical content here, so let's get the technical bit out of the way first, then the waffling can come later ...

I finally tracked down the mistake I didn't make in part 6a, but thought I'd identified and fixed in part 6b! Here are the two sets of subpartitions for the P_20100209 partition that's the source of the statistics and for the new P_20100210 partition that the stats were copied to. This is how part 6a originally looked

TEST_TAB1                      P_20100209_GROT                NO  22-APR-2010 11:24:12          3
TEST_TAB1                      P_20100209_HALO                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_JUNE                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_OTHERS              NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100210_GROT                NO
TEST_TAB1                      P_20100210_HALO                NO
TEST_TAB1                      P_20100210_JUNE                NO
TEST_TAB1                      P_20100210_OTHERS              NO

and here it is after I'd fixed it.

TEST_TAB1                      P_20100209_GROT                NO  22-APR-2010 11:24:12          3
TEST_TAB1                      P_20100209_HALO                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_JUNE                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_OTHERS              NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100210_GROT                NO  22-APR-2010 11:24:10          3
TEST_TAB1                      P_20100210_HALO                NO                                 
TEST_TAB1                      P_20100210_JUNE                NO                                 
TEST_TAB1                      P_20100210_OTHERS              NO                                 

So originally I had seen no statistics on the P_20100210_GROT partition after the call to COPY_TABLE_STATS but then I did see statistics on a later run so I edited the post to reflect that.

Why the difference? Well it's not a COPY_TABLE_STATS issue and COPY_TABLE_STATS does not copy statistics for one subpartition and not the others. It just doesn't make sense and never did but I somehow convinced myself it did. Here's where those subpartition stats actually came from and you can walk through  the script and output I posted earlier to see this for yourself.

(Note - I actually repeat steps 1 and 2 twice - once with _minimal_stats_aggregation set to it's default value of TRUE and then with _minimal_stats_aggregation set to FALSE. But both runs have the same error, so you can refer to either)

1) I create LOAD_TAB1 and re-use it 4 times to load 3 rows into each of the 4 new subpartitions using subpartition exchange. Here's an example of one of those loads.

SQL> TRUNCATE TABLE LOAD_TAB1;

Table truncated.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U');

1 row created.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1;

Table altered.

SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION	P_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES;

Table altered

2) The subpartition stats now look like this

TABLE_NAME                     SUBPARTITION_NAME              GLO LAST_ANALYZED          NUM_ROWS
------------------------------ ------------------------------ --- -------------------- ----------
TEST_TAB1                      P_20100209_GROT                NO  22-APR-2010 11:24:12          3
TEST_TAB1                      P_20100209_HALO                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_JUNE                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_OTHERS              NO  22-APR-2010 11:24:13          3

3) Now that I have the P_20100209 subpartition stats, I'll add a new partition, populate the P_20100210_GROT subpartition with 10 rows using the same LOAD_TAB1 and subpartition exchange and then copy the stats from the previous partition.

SQL> ALTER TABLE TEST_TAB1
  2  ADD  PARTITION P_20100210 VALUES LESS THAN (20100211);

Table altered.

SQL> TRUNCATE TABLE LOAD_TAB1;

Table truncated.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1000, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 30000, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2000, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 10000, 'N');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2400, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 500, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1200, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 400, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 600, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 700, 'Z');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100210_GROT WITH TABLE load_tab1;

Table altered.

SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION	P_20100210_GROT REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

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

PL/SQL procedure successfully completed.

4) The subpartition stats now look like this

TEST_TAB1                      P_20100209_GROT                NO  22-APR-2010 11:24:12          3
TEST_TAB1                      P_20100209_HALO                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_JUNE                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_OTHERS              NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100210_GROT                NO  22-APR-2010 11:24:10          3
TEST_TAB1                      P_20100210_HALO                NO
TEST_TAB1                      P_20100210_JUNE                NO
TEST_TAB1                      P_20100210_OTHERS              NO

so it looks like COPY_TABLE_STATS copied stats for just the subpartition I just loaded via subpartition exchange, but why didn't it copy the stats for the other subpartitions? The answer is that it didn't copy any stats for subpartitions. The stats for P_20100210_GROT were created because we exchanged LOAD_TAB1 with that subpartition and LOAD_TAB1 still had statistics from the previous time it was used!

If I had added a gather_stats call on LOAD_TAB1 immediately after populating it with data (as I did in earlier parts of the test), the stats would still have appeared on P_20100210_GROT, but NUM_ROWS of 10 would have highlighted that these were not copied stats, but stats from the load table. Alternatively, if I had dropped and recreated LOAD_TAB1 each time (or used a different load table entirely) and not gathered stats on it (as you wouldn't if you were using COPY_TABLE_STATS) then I would have seen what I did at the outset - no subpartition stats at all! That would be a much closer version of how you might actually use this in production.

A final alternative would have been to delete the inappropriate stats from LOAD_TAB1 when I reuse it. That's the approach I've used in a very slightly modified version of stats_5_6a.txt that I've put here (just search for delete_table_stats).


The Waffle

As well as being an illustration of how easy it is to screw up tests when you start cutting and pasting sections from other scripts to (ha!) simplify things, this is a poster-child for discussing things with others. It was only when I sat down with my colleague Jari Kuhanen (excellent Oracle guy - looking for work soon ) to go over another aspect of these tests and he asked some pertinent questions, that the problem suddenly hit me. He didn't need to tell me what the problem was - just him asking good questions made me think about it a different way and I suddenly knew what the issue was.

I keep saying this, but one of my favourite aspects of blogging is making and fixing your mistakes in public. It's often the mistakes that provide the real learning experiences and it's also an extension of discussing your ideas with others. However, the down-side is that you can end up with an unholy, confusing mess of different, contradictory posts and it can be hard to judge what you should edit as a result. I never edit posts meaningfully without making it pretty clear I've done so and, although I'm tempted to tidy up this mess to make all the posts more consistent, I've decided I'd rather leave the meandering 'story' as is. No promises on when this might happen but, when these posts are all done, I think there's no avoiding a white paper about managing statistics on partitioned objects that will be coherent and correct. For now, let's leave the mess as it is

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.