统计数据可以分为两个层次:系统统计数据和对象统计数据。
1、系统统计数据
系统统计数据由dbms_stats.get_system_stats存储过程来收集。系统收集到的系统统计数据会被存储在系统数据字典aux_stats$中。
SQL> select * from aux_stats$ order by pname;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_MAIN CPUSPEED --CPU在负载模式下的处理速度。
SYSSTATS_MAIN CPUSPEEDNW 1565.429 --CPU在无负载模式下的处理速度。
SYSSTATS_INFO DSTART 01-25-2013 23:19
SYSSTATS_INFO DSTOP 01-25-2013 23:19
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN IOSEEKTIM 10 --IO寻址时间。
SYSSTATS_MAIN IOTFRSPEED 4096 --IO传输速度。
SYSSTATS_MAIN MAXTHR --IO系统的最大吞吐量。
SYSSTATS_MAIN MBRC --系统设置多数据块读的数据块数。
SYSSTATS_MAIN MREADTIM --多数据块读的平均读取时间。
SYSSTATS_MAIN SLAVETHR --单个平行服务进程的最大吞吐量。
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_MAIN SREADTIM --单数据块读的平均读取时间。
SYSSTATS_INFO STATUS COMPLETED
已选择13行。
2、对象统计数据
DBMS_STATS 包!
3、“待定”统计数据
11gr2开始,可以使用下面类型的操作来收集优化器统计信息:
1.自动发布收集的统计信息在收集操作结束以后(默认选项publish)
2.保存新的统计信息,并且待定(暂不发布pending)
这个特性可以将新收集的统计信息置为待定状态,所以可以先验证新统计信息的有效性然后再发布。可以使用下面的命令来查看是否默认发布新的统计信息。
SQL> select dbms_stats.get_prefs('publish') publish from dual;
PUBLISH
--------------------------------------------------
TRUE
返回为true或者false。True表示新的统计信息收集后即发布,也就是说优化器会使用新的统计信息来生查询计划,False表示收集的统计信息会被放入USER_TAB_PENDING_STATS和 USER_IND_PENDING_STATS,并且不会立刻被优化器使用,为待定状态。
可以使用下面的包来改变各个级别(global,schema,table)的默认publish选项。
Global
exec Dbms_stats.set_global_prefs(pname =>'PUBLISH' ,pvalue=> 'FALSE') ;
Schema
exec dbms_stats.set_schema_prefs(ownname => 'U1',pname=>'PUBLISH' ,pvalue => 'TRUE') ;
table
Exec dbms_stats.set_table_prefs('U1', 'T1','PUBLISH', 'false');
假设你执行了上面的关于table的操作,那么关于schema u1 上t1表的统计信息收集以后就不会立刻应用于优化器上面,而是先置于USER_TAB_PENDING_STATS表里面为待定状态。设置好默认的publish选项之后,就可以开始验证新统计信息了。
默认的优化器会使用已经发布的存放在数据字典里面的统计信息,可以通过更改初始化参数OPTIMIZER_USE_PENDING_STATISTICS来设定优化器使用哪一种类型的统计信息(published or pending),比如使用下面的操作来更改session级别的优化器统计信息来源(不要写成alter system了)。
alter session set optimizer_use_pending_statistics = TRUE;
这样在session级别内就可以使用待定的统计信息来编译sql语句并且生成查询计划,如果新的统计信息已经被验证,那么可以使用下面的语句发布统计信息。
Execdbms_stats.publish_pending_stats('DEXTER','PUBLISH_TEST');
如果不想使用新的统计信息,那么可以使用下面的语句去删除。
Execdbms_stats.delete_pending_stats('DEXTER','PUBLISH_TEST');