Hello All,
I am using Oracle 11g R2.
I am having a partitioned table, this table can have up to 30 partitions based on my retention policy and each partition is 1 day of data. each partition have like 3 millions records.
I am gathering the statistics of my current partition as below:
DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO');
I noticed that when the table have 6 or 7 partitions the above gather statistics was taking like 20 mins. now I have 20 partitions on the table it is taking 45 minutes.
What is the reason ? should not be the same ? as I am gathering the statistics for only 1 partition ? and all partitions have the same number of records?
Regards,