1.收集方案概述
1.1大表统计信息收集方式：针对现有万达商管营帐系统230张大表，结合实际日均数据量，我们采用收集单独分区，冰将分区复制到各个
其他子分区方式来实现大表统计信息收集。
1.2例如:如果账期规划表，共计366个分区，那么可以为任何分区(比如P_20190101)收集统计信息，然后将统计信息复制到该分区的其余部分注意：选择分区中有数据的那个分区。不需要为所有分区收集统计数据，因为oracle内部会根据分区键分发数据。
2.具体实施步骤：
2.1收集P_20190101子分区统计信息EXEC dbms_stats.gather_table_stats(ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’ , PARTNAME => ‘P_20190101’,
estimate_percent => 10, method_opt=> ‘for all indexed columns size skewonly’, granularity => ‘ALL’, degree => 8 ,cascade => true );
2.2为其余的分区生成脚本exec DBMS_STATS.COPY_TABLE_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, srcpartname => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, 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 => ‘P_20190101’, dstpartname => ‘P017’, force => TRUE);
exec DBMS_STATS.LOCK_PARTITION_STATS( ownname => ‘TABLE_OWNER’, tabname => ‘TABLE_NAME’, partname => ‘P017’);