Oracle 性能优化 — 统计数据收集

张兴康 · 发表于 2020-5-1 17:10:45

ORACLE优化器的优化方式有两大类，即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。A、 RBO方式：优化器在分析SQL语句时，更据数据库中表和索引等定义信息，遵循的是Oracle内部预定的一些规则。比如我们常见的：当一个where子句中的一列有索引时去走索引而不走全表扫描。B、 CBO方式：依词义可知,它是看语句的代价(Cost)了。基于代价的查询，数据库根据搜集的表和索引的数据的统计信息（统计信息通过analyze 命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划（实际上不一定最优）。统计信息给出表的大小、有多少行、每行的长度等信息。注意：这些统计信息起初在库内是没有的，是根据 analyze 命令或者dbms_stats包来定期搜集后才出现的，所以很多的时侯过期统计信息会令优化器做出一个错误的执行计划，因些我们应及时更新这些信息。为了使用基于成本的优化器(CBO) , 你必须经常运行analyze或dbms_stats命令,以增加数据库中的对象统计信息(object statistics)的准确性。在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。1. 如何查看对象统计信息(object statistics)对CBO模式，对象统计信息至关重要。如何查看对象统计信息(object statistics)？Oracle中关于表的统计信息是在数据字典中的，可以下SQL查询到，eg：SELECT table_name,num_rows, blocks, empty_blocks AS empty, avg_space, chain_cnt, avg_row_lenFROM dba_tablesWHERE owner = ‘ONT’AND table_name = ‘OE_ORDER_LINES_ALL’;TABLE_NAME NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LENOE_ORDER_LINES_ALL 5344 505 5 0 0 441可以看到数据字典中统计到的该表有5344笔记录，我们下SQL验证一下：select count(*) from apps.OE_ORDER_LINES_ALL;发现返回是16518笔记录，可见这个表的统计信息是比较陈旧的，真实数据与统计到的数据有较大的差别。在这种情况下，如果某个View用到此Table，且系统使用CBO的方式，则可能导致Oracle的optimizer给出效率低下的执行计划。此时可以用ANALYZE去重新统计OE_ORDER_LINES_ALL这个表，可以下SQL：ANALYZE TABLE ONT.OE_ORDER_LINES_ALL COMPUTE STATISTICS;再次Query数据字典：TABLE_NAME NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LENOE_ORDER_LINES_ALL 16518 1530 1035 865 257 643发现此时的信息已是最新的了。有了比较正确的统计信息，optimizer才能给出高效的执行计划。2. 并发请求：统计数据收集模式(FNDGSCST) / Gather Schema StatisticsOracle ERP中有几个与Gather有关的标准Request：Gather All Column Statistics –FND_STATS.GATHER_ALL_COLUMN_STATS()Gather Column Statistics –FND_STATS.GATHER_COLUMN_STATS()Gather Schema Statistics –FND_STATS.GATHER_SCHEMA_STATS()Gather Table Statistics –FND_STATS.GATHER_TABLE_STATS()查看FND_STATS 这个Package的写法，其实它就是在调用Oracle DB中Standard的Package dbms_stats 中的某些Function。Oracle DB中常用的Gather有以下一些，DBA也可以直接在Database级别上定期Run这些Function，以便能让Oracle统计到最新的数据库状况：dbms_stats.gather_database_stats();dbms_stats.gather_schema_stats();dbms_stats.gather_table_stats();dbms_stats.gather_index_stats();Oracle CBO需要系统定期分析统计表/索引。只有这样CBO才能使用正确的SQL访问路径，提高查询效率。因此在Instance Level的optimizer_mode = choose ，定期运行ANALYZE 或dbms_stats是非常重要的，尤其是当上次统计后，数据量已发生较大变化之后。注意：统计操作是很耗资源的动作，要在系统Loading小的时候进行。