How to Gather Statistics on Large Partitioned Tables in Oracle

It is difficult to gather stats on large partition tables which is huge in size, specially in core domain like telecom sectors where customers has to maintain call detail records in a partitioned table which are very big and huge in size.

For such tables we use to gather statistics of one partition which we can call it as a source partition and copy that stats to rest of the partition which we can call it as destination partition.

For Example: If you have 366 partitions then you can gather stats for anyone partition say P185 and now copy stats to rest of the partition. please note that, choose the partition where you have data in that partition. There is no need to gather stats for all the partition because oracle internally distribute the data based on the partitioned key.

STEPS TO MAINTAIN STATISTICS ON LARGE PARTITION TABLES

STEP 1: Gather stats for any one partition say P185.

EXEC dbms_stats.gather_table_stats(ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’ , PARTNAME => ‘P185’, estimate_percent => 10, method_opt=> ‘for all indexed columns size skewonly’, granularity => ‘ALL’, degree => 8 ,cascade => true );

Note: Change table_name and table_owner. You can increase degree if free parallel servers are available.

STEP 2: Generate script for rest of the remaining partition like shown below. Your source partition will be P185 and destination partition will be rest of the remaining partitions.

STEP 3: After gather statistics you can lock the stats. Using below format you can generate the script for all the partitions after making necessary changes.

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P001’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P001’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P002’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P002’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P003’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P003’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P004’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P004’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P005’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P005’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P006’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P006’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P007’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P007’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P008’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P008’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P009’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P009’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P010’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P010’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P011’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P011’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P012’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P012’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P013’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P013’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P014’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P014’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P015’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P015’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P016’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P016’);

exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P185’, dstpartname => ‘P017’, force => TRUE);

exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P017’);