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/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_STATISTICS

Statistics 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

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