Oracle 统计信息相关命令汇总

Oracle 统计信息相关命令汇总

一：统计信息包括

表统计信息(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/cjccreate 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

在收集统计信息期间执行了如下语句

---01select /*+ 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;---02select 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 maxbktorder by maxbkt;---03select 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 maxbktorder by maxbkt;---04select /*+ 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" twhere "OBJECT_ID" is not null;---05select /*+ 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" twhere "OBJECT_NAME" is not null;

五：查询统计信息

(1)查看表统计信息DBA_TABLESDBA_TAB_STATISTICS(2) 查看列统计信息DBA_TAB_COL_STATISTICSDBA_TAB_COLUMNSDBA_TAB_HISTOGRAMS(3)查看索引统计信息DBA_IND_STATISTICS

Statistics on Tables, Indexes and ColumnsDBA_TABLES and DBA_OBJECT_TABLESDBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICSDBA_TAB_HISTOGRAMSDBA_TAB_COLSDBA_COL_GROUP_COLUMNSDBA_INDEXES and DBA_IND_STATISTICSDBA_CLUSTERSDBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONSDBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONSDBA_PART_COL_STATISTICSDBA_PART_HISTOGRAMSDBA_SUBPART_COL_STATISTICSDBA_SUBPART_HISTOGRAMS

六：参数说明

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582

GATHER_INDEX_STATSGATHER_TABLE_STATSGATHER_SCHEMA_STATSGATHER_DICTIONARY_STATSGATHER_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 expressionThe 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.htmhttp://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003Database PL/SQL Packages and Types ReferenceDatabase Performance Tuning Guide

###2021-02-21 17:30 chenjuchao###