然而,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:
| The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition) |
| 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);