Analyze clauses and dbms_stats package（二）

        ORACLE优化程序的成本模型考虑了I/O成本和CPU成本，两者都是以时间为单位。CBO根据评估得到所需的I/O次数和CPU周期，并将它们转化为执行时间，然后对所有的执行路径的执行时间进行比较，从中选择CBO优化器认为的花费时间最少的执行路径，这就是我们所说的执行计划。
然而，CBO对执行成本的估算根据统计信息来计算的，所以统计信息的准确性，直接影响着执行计划的优劣。
        这些优化器用到的统计信息包括：
表的统计信息：Number of rows、Number of blocks、Average row length
列的统计信息：Number of distinct values (NDV) in column、Number of nulls in column、Data distribution (histogram)、Extended statistics
索引统计信息：Number of leaf blocks、Levels、Clustering factor
系统统计信息：I/O performance and utilization、CPU performance and utilization

        在默认情况下，数据库会自动的收集统计信息，当然我们也可以通过使用DBMS_STATS package手工收集统计信息。

        适合执行手工收集的情况：
1、大量的删除，truncate或者重建。
2、加载到对象的数据超过对象本身大小的10%。
3、没有统计信息的对象。虽然查询时遇到无统计信息对象时会自动收集统计信息，但只会收集查询优化所必备的一部分信息。这取决于OPTIMIZER_DYNAMIC_SAMPLING参数值。

收集统计信息：

EXEC DBMS_STATS.gather_database_stats;  --数据库统计信息   

EXEC DBMS_STATS.GATHER_SYSTEM_STATS;  --操作系统统计信息I/O、CPU performance and utilization,默认情况无负载方式。负载方式('start')   ('stop')  aux_stats$

EXEC DBMS_STATS.gather_schema_stats('SCOTT');   --用户统计信息

EXEC DBMS_STATS.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP');  --表统计信息

EXEC DBMS_STATS.gather_index_stats('SCOTT','EMP_PK');  --索引统计信息

查看os统计信息：

SYS AS SYSDBA@cedb>select * from aux_stats$;


SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    11-25-2013 13:08
SYSSTATS_INFO                  DSTOP                                     11-25-2013 13:08
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                           1900
SYSSTATS_MAIN                  IOSEEKTIM                              14
SYSSTATS_MAIN                  IOTFRSPEED                          28566
SYSSTATS_MAIN                  SREADTIM                            7.326
SYSSTATS_MAIN                  MREADTIM                             .745
SYSSTATS_MAIN                  CPUSPEED                             1901
SYSSTATS_MAIN                  MBRC                                   11


SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_MAIN                  MAXTHR                           96396288
SYSSTATS_MAIN                  SLAVETHR

参数说明：

Parameter Name	Description	Initialization	Options for Gathering or Setting Statistics	Unit
cpuspeedNW	Represents noworkload CPU speed. CPU speed is the average number of CPU cycles in each second.	At system startup	Set gathering_mode = NOWORKLOAD or set statistics manually.	Millions/sec.
ioseektim	I/O seek time equals seek time + latency time + operating system overhead time.	At system startup 10 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	ms
iotfrspeed	I/O transfer speed is the rate at which an Oracle database can read data in the single read request.	At system startup 4096 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	Bytes/ms
cpuspeed	Represents workload CPU speed. CPU speed is the average number of CPU cycles in each second.	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART|STOP, or set statistics manually.	Millions/sec.
maxthr	Maximum I/O throughput is the maximum throughput that the I/O subsystem can deliver.	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART|STOP, or set statistics manually.	Bytes/sec.
slavethr	Slave I/O throughput is the average parallel slave I/O throughput.	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	Bytes/sec.
sreadtim	Single block read time is the average time to read a single block randomly.	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	ms
mreadtim	Multiblock read is the average time to read a multiblock sequentially.	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	ms
mbrc	Multiblock count is the average multiblock read count sequentially.	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	blocks

查看统计信息：

EXEC DBMS_STATS.gather_database_stats
EXEC DBMS_STATS.GATHER_SYSTEM_STATS
EXEC DBMS_STATS.gather_schema_stats
EXEC DBMS_STATS.gather_table_stats

EXEC DBMS_STATS.gather_index_stats('SCOTT','EMP_PK')

例：

  1  declare
  2  v_numrows   number;
  3  v_numblks   number;
  4  v_avgrlen   number;
  5  v_cachedblk number;
  6  v_cachehit  number;
  7  begin
  8  dbms_stats.get_table_stats(ownname=>'SCOTT',tabname=>'EMP',numrows=>v_numrows,numblks=>v_numblks,avgrlen=>v_avgrlen,cachedblk=>v_cachedblk,cachehit=>v_cachehit);   
  9  dbms_output.put_line(v_numrows);
 10  dbms_output.put_line(v_numblks);
 11  dbms_output.put_line(v_avgrlen);
 12  dbms_output.put_line(v_cachedblk);
 13  dbms_output.put_line(v_cachehit);
 14  end;
 15  /
14
5
38

cachedblk和cachehit：

cachedblk	The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition)
cachehit	The average cache hit ratio for the segment (index/table/index partition/table partition)

删除统计信息
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');
EXEC DBMS_STATS.delete_index_stats('SCOTT','EMP_PK');

创建备份统计信息表
EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname =>'',stattab => '',tblspace => '');

备份表统计信息
EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname =>'',tabname =>'',stattab => '');

导入统计信息
EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname =>'',tabname =>'',stattab => '');

删除备份统计信息表

EXEC DBMS_STATS.DROP_STAT_TABLE(ownname =>'',stattab => '');

统计信息存储在以下的数据字典中  
DBA_TABLES and DBA_OBJECT_TABLES
DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_TAB_COLS
DBA_COL_GROUP_COLUMNS
DBA_INDEXES and DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS

更多有关DBMS_STATS的使用请参照官方文档：
Database Performance Tuning Guide：Managing Optimizer Statistics
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003
Database PL/SQL Packages and Types Reference：DBMS_STATS
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059

附：
最最常用的收集统计信息程序的语法（官方文档）
DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
    estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
    degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
    cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'DATA',
   force            BOOLEAN  DEFAULT FALSE);


DBMS_STATS.GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (GET_PARAM('ESTIMATE_PERCENT')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type 
                                               (GET_PARAM('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);


DBMS_STATS.GATHER_SYSTEM_STATS (
   gathering_mode   VARCHAR2 DEFAULT 'NOWORKLOAD',
   interval         INTEGER  DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);


DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname          VARCHAR2, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE, 
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL, 
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL, 
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE,
  obj_filter_list  ObjectTab DEFAULT NULL);