Oracle DBMS_STATS.SET_TABLE_STATS 模拟大数据量进行 SQL 调试和优化

生产上有一张 200 万条的表，需要进行 SQL 优化；测试环境中的这张表，里面只有 10 万行的数据。不需要真正插入 190 万条的数据，可以通过DBMS_STATS.SET_TABLE_STATS 这个包进行表统计信息...

cuantangnie3915

716人浏览 · 2015-12-25 17:46:22

cuantangnie3915 · 2015-12-25 17:46:22 发布

生产上有一张 200 万条的表，需要进行 SQL 优化；测试环境中的这张表，里面只有 10 万行的数据。
不需要真正插入 190 万条的数据，可以通过DBMS_STATS.SET_TABLE_STATS 这个包进行表统计信息的设置，来模拟大数据量的场景。

首先，对测试环境的这张表进行统计信息收集，以得到准确的统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS ('acct', 'elmp_trans_custommessage');
END;

查看这张表的相关信息（行数、使用的所有数据块、每行的字节数）

select table_name,num_rows,blocks,avg_row_len from user_tables where lower(table_name) = 'elmp_trans_custommessage';

下面是使用这张表的SQL执行计划

根据上面查询到的信息，重新设置这张表的统计信息，将表的数据量增大到 200 万条

计算200万条时，这张表的数据块大小

select round(3268*2000000/111684) from dual;

58522

手动设置这张表的统计信息

BEGIN
DBMS_STATS.SET_TABLE_STATS(OWNNAME => 'ACCT',
TABNAME => 'ELMP_TRANS_CUSTOMMESSAGE',
NUMROWS => 2000000,
NUMBLKS => 58522,
AVGRLEN => 202);
END;

查看这张表的相关信息（行数、使用的所有数据块、每行的字节数），可以看到，我们手动设置的表的相关信息已经生效了

select table_name,num_rows,blocks,avg_row_len from user_tables where lower(table_name) = 'elmp_trans_custommessage';

重新查看执行计划，可以看到成本从 891 增大到 15900

创建索引

create index acct.idx_trans_custommessage on acct.elmp_trans_custommessage(CustomMessageType,CUSTOMMESSAGESTATUS) tablespace TS_ACCT_INDEX;

加上索引的 hint，查看执行计划，可以看到成本的大幅下降