Oracle Database Discussions

Hi Everyone,

I am trying to take a new approach for taking statistics.

First my system:

Oracle 10.2.0.1
3 instances RAC running on Linux RH AS 3, 64bit

I am currently taking my stats using dbms_stats.gather_schema_stats with the option 'gather auto', it is fine because only tables with stale data and empty tables are being analyzed, but the process takes 3 to 4 days..

Now I want to spread the statistics processes across the cluster's nodes using gather_table_stats (stale tables and empty tables only), while I was doing my procedure I found out the following, and here is where I need your help.

If I execute the following command:

exec dbms_stats.gather_table_stats(ownname=>'CACOSTA',tabname=>'subpart_date',granularity=>'SUBPARTITION',partname=>'P200901')

I can see the stats of my subpartitions BUT no stats were taken for the partition.

If I change the granularity on the gather_table_stats to 'PARTITION':

exec dbms_stats.gather_table_stats(ownname=>'CACOSTA',tabname=>'subpart_date',granularity=>'PARTITION',partname=>'P200901')

It will take the stats ONLY for the partition but not for the subpartitions.

If I take the stats twice for both 'PARTITION' and 'SUBPARTITION' I will get all the stats but it will take double time to finish.

Can someone please tell me if I am on a wrong approach? is there a way to make the RDBMS understand that when I am using the 'PARTITION' granularity it can take the opportunity to use that very same information it is gathering and provide with stats to the subpartitions too?

Thanks and sorry for the long post.

Carlos Acosta