1. 什么是系通通计信息? ios
咱们知道在CBO环境中,Oracle依赖于对象的统计估算成本,以选择正确的SQL执行计划。从Oracle9i开始CBO计算成本(cost)的算法有了变化。Oracle9i之前更多考虑IO(多块读与单块读)成本;9i之后,强化了cpu速度对成本估算的影响。算法
Oracle提供了dbms_stats.gather_system_stats来收集系通通计信息。系通通计信息让优化器考虑服务器的IO与CPU性能及其利用率,做为计算成本的依据;为每个可选的执行计划估算IO与CPU成本。于是对于CBO来讲,得到准确的系通通计信息对于正确估计成本是很是重要的。Oracle收集的系通通计信息主要内容说明以下:sql
--cpuspeedNW 表示非负载状况下的cpu速度,在系统启动时自动搜集 数据库
--ioseektim IO查找时间,以毫秒表示;缺省为10ms,非负载模式或能够手动设置。缓存
--iotfrspeed IO传输速度,表示Oracle数据库单次读数据的传输速率,单位为bytes/ms,在系统启动时自动收集;默认为4096 bytes/ms服务器
--cpuspeed 表示负载状况下的cpu速度,以平均每秒可提供的cpu周期表示session
--maxthr 最大IO吞吐量,单位为bytes/s 性能
--slavethr 从属IO吞吐量,表示并行进程时,从属进程的IO吞吐量,单位为bytes/s 测试
--sreadtim 单块读时间(如索引读取),表示随机读一个Oracle数据块的时间,以ms计算优化
--mreadtim 多块读时间(主要是指全表扫描),表示连续读取多个Oracle数据库的平均时间,以ms计算
--mbrc 多块读计数,表示一次多块读的读取的Oracle数据块数量
系通通计信息存储在sys.aux_stats$表中:
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-24-2011 18:06
SYSSTATS_INFO DSTOP 01-24-2011 18:06
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1970.048
SYSSTATS_MAIN IOSEEKTIM 11.132
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 6
SYSSTATS_MAIN MREADTIM 24
SYSSTATS_MAIN CPUSPEED 1800
SYSSTATS_MAIN MBRC 6
SNAME PNAME PVAL1 PVAL2
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
2. 系通通计信息的收集
Dbms_stats.gather_system_stats的参数以下:
SQL> desc dbms_stats.gather_system_stats;
Parameter Type Mode Default?
-------------- -------- ---- --------
GATHERING_MODE VARCHAR2 IN Y
INTERVAL NUMBER IN Y
STATTAB VARCHAR2 IN Y
STATID VARCHAR2 IN Y
STATOWN VARCHAR2 IN Y
STATTAB、STATID、STATOWN与其余收集统计信息的参数同样,很少作说明。系通通计信息有工做负载与无工做负载两种类型; ioseektim、iotrfspeed、cpuspeednw是无负载的统计信息,也就是说不须要系统有工做负载,能够系统空闲时进行收集。Oracle为在系统启动时间从新设置,或重置为默认值。要手动收集非工做负载统计信息,使用dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD')。当使用dbms_stats.delete_system_stats()删除系通通计信息时间,将只保留非负载时的统计信息:
SQL> exec dbms_stats.delete_system_stats();
PL/SQL 过程已成功完成。
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-25-2011 11:37
SYSSTATS_INFO DSTOP 01-25-2011 11:37
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 2030.679
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
已选择13行。
不一样压力与不一样类型的应用,甚至同一系统的不一样时间,cpu与io的能力都是不同的。好比ZLHIS在8点到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 |
| 1 |
来自 “ ITPUB博客 ” ,连接:http://blog.itpub.net/7839206/viewspace-1045163/,如需转载,请注明出处,不然将追究法律责任。
转载于:http://blog.itpub.net/7839206/viewspace-1045163/