本文探讨了Oracle系统统计信息的重要性，特别是CPU和IO统计在成本估算中的角色。通过收集不同负载下的系统统计信息，可以更准确地估计SQL执行计划的成本。文章通过实验展示了系统统计信息如何改变SQL的估算成本，并解释了当存在系统统计信息时，db_file_multiblock_read_count参数如何被忽略或影响成本估算。
select * from sys.aux_stats$;
2、统计信息项
--cpuspeedNW 表示非负载情况下的cpu速度,在系统启动时自动搜集
--ioseektim IO查找时间，以毫秒表示；缺省为10ms,非负载模式或可以手动设置。
--iotfrspeed IO传输速度，
表示Oracle数据库单次读数据的传输速率，单位为bytes/ms，在系统启动时自动收集；默认为4096 bytes/ms
--cpuspeed 表示负载情况下的cpu速度,以平均每秒可提供的cpu周期表示
--maxthr 最大IO吞吐量,单位为bytes/s
--slavethr 从属IO吞吐量，表示并行进程时，从属进程的IO吞吐量，单位为bytes/s
--sreadtim 单块读时间(如索引读取)，表示随机读一个Oracle数据块的时间，以ms计算
--mreadtim 多块读时间(主要是指全表扫描),表示连续读取多个Oracle数据库的平均时间，以ms计算
--mbrc 多块读计数，表示一次多块读的读取的Oracle数据块数
3、收集和删除统计信息
dbms_stats.gather_system_stats 和 dbms_stats.delete_system_stats
exec DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'start');
exec DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'stop');
或者
exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL',interval => 10);
4、大话+实验
们知道在CBO环境中，Oracle依赖于对象的统计估算成本，以选择正确的SQL执行计划。从Oracle9i开始CBO计算成本(cost)的算法有了变化。Oracle9i以前更多考虑IO（多块读与单块读）成本;9i以后，强化了cpu速度对成本估算的影响。
Oracle提供了dbms_stats.gather_system_stats来收集系统统计信息。系统统计信息让优化器考虑服务器的IO与CPU性能及其利用率，作为计算成本的依据；为每一个可选的执行计划估算IO与CPU成本。因而对于CBO来说，获得准确的系统统计信息对于正确估计成本是非常重要的。
不同压力与不同类型的应用，甚至同一系统的不同时间，cpu与io的能力都是不一样的。比如ZLHIS在８点到11点的压力，明显于大于下午的压力；这时候包括多块读时间、单块读时间的效率都会有差异。理想的情况是，收集不同系统负载下的系统统计信息，存放到特定的统计信息表中，然后在负载发生变化的时候导入到Oracle中，但在类似ZLHIS这种要求高可用的系统，频繁的变更系统统计信息不太现实。大多数情况下，只需要采集系统高峰时段或典型时段的系统统计信息即可。
收集负载情况下的统计信息有两种方式，一种是手工指定收集时段的开始与结束：
--启动收集
exec dbms_stats.gather_system_stats(gathering_mode => 'START');
.............
--等待系统运行一段时间，等待时间长短根据情况做调整
.............
--停止收集
exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');
另一种方式就是使用间隔模式，指定一个间隔时段，Oracle自动开始与结束信息收集：
--以未来10分钟的系统负载，收集系统统计信息。
exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL',interval => 10);
需要说明的是收集系统统计信息，并不影响已经缓存的sql语句，只会影响新解析的SQL语句，如果要已经缓存的SQL语句也按新的系统统计信息生成执行计划，只有清空共享池，但这在生产系统上是比较危险的操作。另外需要注意的就是，如果在收集时段内没有相应操作，将不会收集对应的系统统计信息；例如，如果收集时段内没有产生全表扫描的多块读，mbrc(多块读计数)将不会收集。
2. 系统统计信息对CBO成本计算的影响
虽然CBO计算的成本只是对生成何种执行计划有关，并不对真正执行SQL语句的真实代价产生什么影响，但作为CBO估算成本的基础要素，系统统计信息要尽量保证准确。我们通过实验来说明系统统计信息对sql语句成本估算的影响：
使用dba_objects视图创建一个测试表:
SQL> --创建测试表
SQL> create table test as select * from dba_objects;
表已创建。
SQL> insert into test select * from test;
已创建10212行。
SQL> --收集测试表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'test',cascade => true);
PL/SQL 过程已成功完成。
然后删除删除工作负载的统计信息，只保留非工作负载的统计信息:
exec dbms_stats.delete_system_stats();
我们来看此时的对test全表扫描估算的成本：
SQL> explain plan for select count(*) from test;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 20424 | 71 (0)| 00:00:01 |
可以看到Oracle在没有负载情况下的系统统计信息时，估算的成本为71。接下为，我们使用导入一些系统统计信息。由于测试环境，没有什么负载，我们使用dbms_stats.set_system_stats过程来手工修改统计信息：
SQL> --创建统计信息表
SQL> exec dbms_stats.create_stat_table(ownname => user,stattab => 'SYSTEM_STATS');
PL/SQL 过程已成功完成。
SQL> --设置相关的统计信息值
SQL> exec dbms_stats.set_system_stats(pname => 'SREADTIM',pvalue => '6' ,stattab => 'system_stats');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.set_system_stats(pname => 'MREADTIM',pvalue => '12',stattab => 'system_stats');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.set_system_stats(pname => 'CPUSPEED',pvalue => '1800' ,stattab => 'system_stats');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.set_system_stats(pname => 'MBRC',pvalue => '16',stattab => 'system_stats');
PL/SQL 过程已成功完成。
SQL> --导入相应统计信息
SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);
PL/SQL 过程已成功完成。
现在来重新查看sql语句的估算成本：
SQL> explain plan for select count(*) from test;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 20424 | 33 (0)| 00:00:01 |
这次估算的成本(cost)为33,在收集了系统信息后，CBO估算的成本发生了变化。我们知道Oracle提供了db_file_multiblock_read_count参数，来控制Oracle一次多块读的Oracle数据块数量，也将影响Oracle对全表扫描成本的估算。Oracle既然收集了多块读IO速度(mreadtim)、多块读计数(mbrc)等信息，那db_file_multiblock_read_count的设置与这些统计信息是什么关系呢？答案是：如果存在负载情况下的多块读的相关统计信息，将会忽略db_file_multiblock_read_count的设置，如果不存在相应的系统统计信息，将使用db_file_multiblock_read_count的值对全表扫描成本进行估算。
首先，我们测试一下，不存在相关系统统计信息时，全表扫描的成本：
SQL> explain plan for select count(*) from test;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 20424 | 71 (0)| 00:00:01 |
-------------------------------------------------------------------
已选择9行。
可以看成本(cost)又回到了未收集系统统计信息时的71,而不是收集后的33,这个时候，我们修改db_file_multiblock_read_count参数，来看看相应的成本cost是否会起变化：
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_file_multiblock_read_count integer 8
SQL> alter session set db_file_multiblock_read_count=16;
会话已更改。
SQL> explain plan for select count(*) from test;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
--------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 20424 | 57 (0)| 00:00:01 |
-------------------------------------------------------------------
已选择9行。
可以看到在没有系统统计信息的情况下，设置db_file_multiblock_read_count，可以影响SQL的成本估算，现在变成了57。现在重新导入系统统计信息，看设置db_file_multiblock_read_count能否影响执行计划：
--重新导入系统统计信息
SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);
PL/SQL 过程已成功完成。
SQL> --还原db_file_multiblock_read_count
SQL> alter session set db_file_multiblock_read_count=8;
会话已更改。
SQL> explain plan for select count(*) from test;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 20424 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------
已选择9行。
可以看到，导入系统统计信息后，成本又变成了33，我们再设置db_file_multiblock_read_count，再重新解析sql语句：
SQL> --再设置db_file_multiblock_read_count值
SQL> alter session set db_file_multiblock_read_count=16;
会话已更改。
SQL> explain plan for select count(*) from test;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |
扫一扫
1037
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
