请注意，本文编写于 786 天前，最后修改于 781 天前，其中某些信息可能已经过时。
一：统计信息包括
表统计信息
(1)行数
(2)块数
(3)平均行长
列统计信息
(1)列中不同值的数量(NDV)
(2)列中空值的数量
(3)数据分布(直方图)
(4)扩展统计
索引统计信息
(1)叶块数量
(2)索引level
(3)聚簇因子
系统统计信息
(1)I/O性能和利用率
(2)CPU性能和利用率二：自动统计信息收集
10g
gather_stats_job Scheduler调度
select program_name, schedule_name, schedule_type, enabled, state
from dba_scheduler_jobs
where owner = 'SYS'
and job_name = 'GATHER_STATS_JOB';
select program_action, number_of_arguments, enabled
from dba_scheduler_programs
where owner = 'SYS'
and program_name = 'GATHER_STATS_PROG';
select w.window_name, w.repeat_interval, w.duration, w.enabled
from dba_scheduler_wingroup_members m, dba_scheduler_windows w
where m.window_name = w.window_name
and m.window_group_name = 'MAINTENANCE_WINDOW_GROUP';11g
自动维护任务
select task_name, status
from dba_autotask_task
where client_name = 'auto optimizer stats collection';
select program_action, number_of_arguments, enable
from dba_scheduler_programs
where owner = 'SYS'
and program_name = 'GATHER_STATS_PROG';
select w.window_name, w.repeat_interval, w.duration, w.enabled
from dba_autotask_window_clients c, dba_scheduler_windows w
where c.window_name = w.window_name
and c.optimizer_stats = 'ENABLED';启用
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/禁用
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/三：手动收集统计信息
表级别收集实例
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE);说明：
1 收集cjc用户下t1表统计信息
2 estimate_percent收集数据百分比
3 method_opt直方图
4 CASCADE级联收集索引统计信息
5 no_invalidate为false表示立即将在Shared Pool中有依赖关系的shared cursor失效method_opt常用组合：
不收集直方图
method_opt => 'for all columns size 1'
收集所有列直方图
method_opt => 'FOR ALL COLUMNS'
收集索引列直方图
method_opt=> 'FOR ALL INDEXED COLUMNS'
收集指定列直方图
method_opt => 'FOR COLUMNS (empno, deptno)'
method_opt => 'FOR COLUMNS (sal+comm)')
收集指定列直方
analyze table table_name compute statistics for columns col_name size 254;四：收集统计信息示例
SQL >
conn cjc/cjc
create table t1 as select * from dba_objects;
create index i_t1_01 on t1(object_id);
create index i_t1_02 on t1(object_name);
alter session set tracefile_identifier='10046';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE);
ALTER SESSION SET EVENTS '10046 trace name context off';
[oracle@cjcdb01 trace]$ pwd
/oracle/product/diag/rdbms/cjcdb/cjcdb/trace
[oracle@cjcdb01 trace]$ ls -lrth *10046*
-rw-r----- 1 oracle oinstall 402K Feb 17 19:15 cjcdb_ora_6693_10046.trm
-rw-r----- 1 oracle oinstall 2.1M Feb 17 19:15 cjcdb_ora_6693_10046.trc
[oracle@cjcdb01 trace]$ tkprof cjcdb_ora_6693_10046.trc 10046_01.trc
[oracle@cjcdb01 trace]$ tkprof cjcdb_ora_6693_10046.trc 10046_02.trc sys=no
[oracle@cjcdb01 trace]$ vim 10046_02.trc在收集统计信息期间执行了如下语句
---01
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
no_substrb_pad */
count(*),
count("OWNER"),
sum(sys_op_opnsize("OWNER")),
count("OBJECT_NAME"),
count(distinct "OBJECT_NAME"),
sum(sys_op_opnsize("OBJECT_NAME")),
count("SUBOBJECT_NAME"),
sum(sys_op_opnsize("SUBOBJECT_NAME")),
count("OBJECT_ID"),
sum(sys_op_opnsize("OBJECT_ID")),
count("DATA_OBJECT_ID"),
sum(sys_op_opnsize("DATA_OBJECT_ID")),
count("OBJECT_TYPE"),
sum(sys_op_opnsize("OBJECT_TYPE")),
count("CREATED"),
count("LAST_DDL_TIME"),
count("TIMESTAMP"),
sum(sys_op_opnsize("TIMESTAMP")),
count("STATUS"),
sum(sys_op_opnsize("STATUS")),
count("TEMPORARY"),
sum(sys_op_opnsize("TEMPORARY")),
count("GENERATED"),
sum(sys_op_opnsize("GENERATED")),
count("SECONDARY"),
sum(sys_op_opnsize("SECONDARY")),
count("NAMESPACE"),
sum(sys_op_opnsize("NAMESPACE")),
count("EDITION_NAME"),
sum(sys_op_opnsize("EDITION_NAME")),
count("SHARING"),
sum(sys_op_opnsize("SHARING")),
count("EDITIONABLE"),
sum(sys_op_opnsize("EDITIONABLE")),
count("ORACLE_MAINTAINED"),
sum(sys_op_opnsize("ORACLE_MAINTAINED")),
count("APPLICATION"),
sum(sys_op_opnsize("APPLICATION")),
count("DEFAULT_COLLATION"),
sum(sys_op_opnsize("DEFAULT_COLLATION")),
count("DUPLICATED"),
sum(sys_op_opnsize("DUPLICATED")),
count("SHARDED"),
sum(sys_op_opnsize("SHARDED")),
count("CREATED_APPID"),
sum(sys_op_opnsize("CREATED_APPID")),
count("CREATED_VSNID"),
sum(sys_op_opnsize("CREATED_VSNID")),
count("MODIFIED_APPID"),
sum(sys_op_opnsize("MODIFIED_APPID")),
count("MODIFIED_VSNID"),
sum(sys_op_opnsize("MODIFIED_VSNID"))
from "CJC"."T1" t;---02
select min(minbkt),
maxbkt,
substrb(dump(min(val), 16, 0, 64), 1, 240) minval,
substrb(dump(max(val), 16, 0, 64), 1, 240) maxval,
sum(rep) sumrep,
sum(repsq) sumrepsq,
max(rep) maxrep,
count(*) bktndv,
sum(case
when rep = 1 then
1
else
0
end) unqrep
from (select val,
min(bkt) minbkt,
max(bkt) maxbkt,
count(val) rep,
count(val) * count(val) repsq
from (select /*+ no_expand_table(t) index_rs(t)
no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl
opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad
*/
substrb("OBJECT_NAME", 1, 64) val,
ntile(75) over(order by substrb("OBJECT_NAME", 1, 64)) bkt
from "CJC"."T1" t
where substrb("OBJECT_NAME", 1, 64) is not null)
group by val)
group by maxbkt
order by maxbkt;---03
select min(minbkt),
maxbkt,
substrb(dump(min(val), 16, 0, 64), 1, 240) minval,
substrb(dump(max(val), 16, 0, 64), 1, 240) maxval,
sum(rep) sumrep,
sum(repsq) sumrepsq,
max(rep) maxrep,
count(*) bktndv,
sum(case
when rep = 1 then
1
else
0
end) unqrep
from (select val,
min(bkt) minbkt,
max(bkt) maxbkt,
count(val) rep,
count(val) * count(val) repsq
from (select /*+ no_expand_table(t) index_rs(t)
no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl
opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad */
"OBJECT_ID" val, ntile(75) over(order by "OBJECT_ID") bkt
from "CJC"."T1" t
where "OBJECT_ID" is not null)
group by val)
group by maxbkt
order by maxbkt;---04
select /*+ opt_param('_optimizer_use_auto_indexes' 'on')
no_parallel_index(t, "I_T1_01") dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl
opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad no_expand
index(t,"I_T1_01") */
count(*) as nrw,
count(distinct sys_op_lbid(73368, 'L', t.rowid)) as nlb,
count(distinct "OBJECT_ID") as ndk,
sys_op_countchg(substrb(t.rowid, 1, 15), 1) as clf
from "CJC"."T1" t
where "OBJECT_ID" is not null;---05
select /*+ opt_param('_optimizer_use_auto_indexes' 'on')
no_parallel_index(t, "I_T1_02") dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl
opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad no_expand
index(t,"I_T1_02") */
count(*) as nrw,
count(distinct sys_op_lbid(73369, 'L', t.rowid)) as nlb,
count(distinct "OBJECT_NAME") as ndk,
sys_op_countchg(substrb(t.rowid, 1, 15), 1) as clf
from "CJC"."T1" t
where "OBJECT_NAME" is not null;五：查询统计信息
(1)查看表统计信息
DBA_TABLES
DBA_TAB_STATISTICS
(2) 查看列统计信息
DBA_TAB_COL_STATISTICS
DBA_TAB_COLUMNS
DBA_TAB_HISTOGRAMS
(3)查看索引统计信息
DBA_IND_STATISTICSStatistics on Tables, Indexes and Columns
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六：参数说明
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582
GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DICTIONARY_STATS
GATHER_DATABASE_STATS
表级别统计信息
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);参数说明：
GATHER_TABLE_STATS Procedure Parameters
参数1：ownname
描述：
Schema of table to analyze
要分析表的所有者
参数2：tabname
描述：
Name of table
表名
参数3：partname
描述：
Name of partition
分区名
参数4:estimate_percent
描述：
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100].
Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics.
This is the default.
The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
要估计的行的百分比（NULL表示计算）有效范围是[0.000001,100].
Oracle使用DBMS_STATS.AUTO_SAMPLE_SIZE来自动确定样本大小生成更好的统计信息，这也是默认值。
这个默认值也可以通过SET_DATABASE_PREFS、SET_GLOBAL_PREFS、SET_SCHEMA_PREFS、SET_TABLE_PREFS来改变。
参数5：block_sample
描述：
Whether or not to use random block sampling instead of random row sampling.
Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated.
Only pertinent when doing an estimate statistics.
是否用随机分组抽样代替随机行抽样。
随机块采样效率更高，但如果数据不是随机分布在磁盘上，则采样值可能有一定的相关性。
仅在进行估计统计时相关。
参数6：method_opt
描述：
Accepts either of the following options, or both in combination:
接受下列选项中的任何一个，或两者的组合:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension
- integer : Number of histogram buckets. Must be in the range [1,254].
直方图桶数。必须在[1,254]范围内。
- REPEAT : Collects histograms only on the columns that already have histograms
只在已经有直方图的列上收集直方图
- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
Oracle根据数据分布和列的工作负载确定收集直方图的列。
- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
Oracle根据列的数据分布确定收集直方图的列。
- column_name : Name of a column
- extension : can be either a column group in the format of (column_name, Colume_name [, ...]) or an expression
The default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
参数7：degree
描述：
Degree of parallelism.
并行度。
The default for degree is NULL.
默认是空。
The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement.
NULL表示使用CREATE TABLE或ALTER TABLE语句中DEGREE子句指定的表默认值。
Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.
使用常量DBMS_STATS.DEFAULT_DEGREE根据初始化参数指定默认值。
The AUTO_DEGREE value determines the degree of parallelism automatically.
AUTO_DEGREE值自动决定并行度。
This is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to the size of the object.
根据对象的大小，这个值介于1(串行执行)和DEFAULT_DEGREE(基于cpu数量和初始化参数的系统默认值)之间。
When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.
参数8：granularity
描述：
Granularity of statistics to collect (only pertinent if the table is partitioned).
要收集的统计信息的粒度(只有在表被分区时才相关)
'ALL' - Gathers all (subpartition, partition, and global) statistics
'APPROX_GLOBAL AND PARTITION' - similar to 'GLOBAL AND PARTITION' but in this case the global statistics are aggregated from partition level statistics. This option will aggregate all statistics except the number of distinct values for columns and number of distinct keys of indexes. The existing histograms of the columns at the table level are also aggregated.The aggregation will use only partitions with statistics, so to get accurate global statistics, users should make sure to have statistics for all partitions. Global statistics are gathered if partname is NULL or if the aggregation cannot be performed (for example, if statistics for one of the partitions is missing).
'AUTO'- Determines the granularity based on the partitioning type. This is the default value.
'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
'GLOBAL' - Gathers global statistics
'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
'PARTITION '- Gathers partition-level statistics
'SUBPARTITION' - Gathers subpartition-level statistics.
参数9：cascade
描述：
Gathers statistics on the indexes for this table.
收集关于该表索引的统计信息。
Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.
使用这个选项等价于在每个表的索引上运行GATHER_INDEX_STATS过程。
Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not.
使用常量DBMS_STATS.AUTO_CASCADE，让Oracle决定是否收集索引统计信息。这也是默认值。
This is the default.
The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
参数10：stattab
描述：
User statistics table identifier describing where to save the current statistics
参数11：statid
描述：
Identifier (optional) to associate with these statistics within stattab
参数12：statown
描述：
Schema containing stattab (if different than ownname)
参数13：no_invalidate
描述：
Does not invalidate the dependent cursors if set to TRUE.
如果设置为TRUE，则不会使从属游标无效。
The procedure invalidates the dependent cursors immediately if set to FALSE.
如果设置为FALSE，该过程将立即使从属游标无效。
Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default.
使用DBMS_STATS.AUTO_INVALIDATE。让Oracle决定何时使相关游标无效。这是默认值。
The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
这个默认值也可以通过SET_DATABASE_PREFS、SET_GLOBAL_PREFS、SET_SCHEMA_PREFS、SET_TABLE_PREFS来改变。
参数14：stattype
描述：Statistics type. The only value allowed is DATA.
参数15：force
描述：
Gather statistics of table even if it is locked.
即使表被锁定，也要收集表的统计信息。统计信息官方文档
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/toc.htm
Database PL/SQL Packages and Types Reference
Database Performance Tuning Guide作者：chenoracle
