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

作者：2023面试高手 | 2024-03-10 04:48:44

踩

dbms_stats.gather_table_stats对大数据量表会不会变慢

生产上有一张 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，查看执行计划，可以看到成本的大幅下降

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-1962249/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1962249/

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/217228