Database Version --> 11.2.0.4
Operating System --> RHEL 6.5
I am fairly new to partitioning.
In my production DBs, we have huge partitioned tables. Some of them have 5000 partitions. Some of them have subpartitions.
Gathering stats for these tables are taking very long time. So, I am thinking of gathering stats at partition level using the below command.
Question1.
Is the below command enough ? I can put the below in a PL/SQL block and dynamically get the partition names and gather its stats.
exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname =>'EMP', granularity => 'PARTITION', partname => 'P_20160206_TGL');
Question2.
I gather that , I can collect stats at SubPartiton level too, by using granularity => 'SUBPARTITION' . But is this necessary ?
Question3. Since I am on 11.2 , are there are any new 11g features which I can make use of for collecting partitions' statistics ?
Any recommendations (however trivial it is) ?