Detailed explanation of Oracle database statistical information method

1. View statistics

Official Documentation:

Optimizer Statistics Concepts ()

1.1 View table statistics information

SELECT OWNER,
       TABLE_NAME,
       PARTITION_NAME,
       OBJECT_TYPE,
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS,
       AVG_SPACE,
       CHAIN_CNT,
       AVG_ROW_LEN,
       to_char(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED,
       USER_STATS
  FROM DBA_TAB_STATISTICS
 WHERE OWNER='CMXBUSI'
 AND TABLE_NAME = 'T02';

1.2 View index statistics

SELECT INDEX_NAME              AS NAME,
       BLEVEL,
       LEAF_BLOCKS             AS LEAF_BLKS,
       DISTINCT_KEYS           AS DST_KEYS,
       NUM_ROWS,
       CLUSTERING_FACTOR       AS CLUST_FACT,
       AVG_LEAF_BLOCKS_PER_KEY AS LEAF_PER_KEY,
       AVG_DATA_BLOCKS_PER_KEY AS DATA_PER_KEY,
       LAST_ANALYZED
  FROM DBA_IND_STATISTICS where
  TABLE_OWNER='CMXBUSI' AND
  TABLE_NAME='T02';

select table_name,index_name,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows from dba_indexes where owner = 'SCOTT' and table_name = 'T1';

1.3 View column statistics

Method 1：
SELECT COLUMN_NAME,
      NUM_DISTINCT,
      LOW_VALUE,
      HIGH_VALUE,
      DENSITY ,
      NUM_NULLS ,
      AVG_COL_LEN ,
      HISTOGRAM,
      NUM_BUCKETS
  FROM DBA_TAB_COL_STATISTICS
 WHERE OWNER='CMXBUSI'
 AND TABLE_NAME = 'T02';


Method 2：
 ||'.'||a.table_name name ,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
a.num_distinct/b.num_rows selectivity,
num_nulls,density,
,
a.num_buckets 
from dba_tab_col_statistics a,dba_tables b 
where = 
and a.table_name=b.table_name
and =upper('CMXBUSI')
and a.table_name=upper('T02')
and a.column_name=upper('ID');

Method 3：
select table_name,column_name,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'SCOTT' and table_name = 'T1';

2. DBMS_STATS collects statistical information

The dbms_stas package not only allows table analysis, it also manages database analysis. According to the function, you can divide it into several categories:

(1) Collection of performance data

(2) Setting of performance data

(3) Deletion of performance data

(4) Backup and recovery of performance data

1.Collection of performance data
The following four stored procedures of this package are collected separately index、table、schema、database Statistics of:
dbms_stats.gather_table_stats Collection table、List和indexStatistics of(when cascade fortrue hour，Analysis table、List（index）information)；
dbms_stats.gather_schema_stats collect schema 下所有对象Statistics of；
dbms_stats.gather_index_stats collectindexStatistics of；
dbms_stats.gather_system_stats collect系统统计information
dbms_stats.gather_dictionary_stats： Statistics for all dictionary objects；
dbms_stats.GATHER_DATABASE_STATS：分析数据库information
dbms_stats.create_stat_table 创建存放统计information表
dbms_stats.auto_sample_size Sample value

2.Performance data settings
设置表统计information：dbms_stats.set_table_stats
设置index统计information：dbms_stats.set_index_stats
设置List统计information：dbms_stats.set_column_stats

3.Deletion of performance data
删除数据库统计information：dbms_stats.delete_database_stats
删除用户方案统计information：dbms_stats.delete_schema_stats
删除表统计information：dbms_stats.delete_table_stats
删除index统计information：dbms_stats.delete_index_stats
删除List统计information：dbms_stats.delete_column_stats

4.Backup and recovery of performance data
dbms_stats.EXPORT_COLUMN_STATS：导出List的分析information
dbms_stats.EXPORT_INDEX_STATS：导出index分析information
dbms_stats.EXPORT_SYSTEM_STATS：导出系统分析information
dbms_stats.EXPORT_TABLE_STATS：导出表分析information
dbms_stats.EXPORT_SCHEMA_STATS：导出方案分析information
dbms_stats.EXPORT_DATABASE_STATS：导出数据库分析information
dbms_stats.IMPORT_COLUMN_STATS：导入List分析information
dbms_stats.IMPORT_INDEX_STATS：导入index分析information
dbms_stats.IMPORT_SYSTEM_STATS：导入系统分析information
dbms_stats.IMPORT_TABLE_STATS：导入表分析information
dbms_stats.IMPORT_SCHEMA_STATS：导入方案分析information
dbms_stats.IMPORT_DATABASE_STATS：导入数据库分析information

2.1 Steps to collect statistical information

1.Check statistics
set linesize 300;
SELECT OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED  FROM dba_tables where  TABLE_NAME  in (
'T_S_TYPE',
'T_EDF_TASK'
);

2.For slow operationSQLCondition，Collect statistics by table：
sqlplus / as sysdba &lt;&lt;EOF
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =&gt; 'username',TABNAME =&gt; 'Table name' ,ESTIMATE_PERCENT =&gt; 0.1 ,METHOD_OPT =&gt; 'FOR ALL INDEXED COLUMNS' ,CASCADE =&gt; TRUE,DEGREE =&gt; 30);
EOF

ps：ESTIMATE_PERCENT =&gt; 0.1    for sampling ratio

3.For slow operationSQLCondition，Collect statistics by user：
sqlplus / as sysdba &lt;&lt;EOF
exec DBMS_STATS.GATHER_schema_STATS(OWNNAME =&gt; 'CCICJY' ,ESTIMATE_PERCENT =&gt; 0.1 ,METHOD_OPT =&gt; 'FOR ALL INDEXED COLUMNS' ,CASCADE =&gt; TRUE,DEGREE =&gt; 30);
EOF

4.Check whether the statistics are executed
select  * from dba_scheduler_jobs;
select * from dba_scheduler_jobs  WHERE JOB_NAME='GATHER_STATS_JOB';

2.2 Detailed explanation of DBMS_STATS.GATHER_TABLE_STATS usage

DBMS_STATS.GATHER_TABLE_STATSThe syntax is as follows:

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname  VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);

Parameter description:

:Owners of the table to be analyzed

:Table name to be analyzed.

:The name of the partition,Useful only for partition tables or partition indexes.

4.estimate_percent:Percentage of sample rows,Value range[0.000001,100],nullFor all analysis,No sampling. 
  constant:DBMS_STATS.AUTO_SAMPLE_SIZEIt's the default value,Depend onoracleDetermine the best sampling value.

5.block_sapmple:Whether to replace row sampling with block sampling.

6.method_opt:DecidehistogramsHow is information counted（Sampling method）
method_optThe value of(The default value isFOR ALL COLUMNS SIZE AUTO):
  for table：Statistics table only
  for all columns：Analyze all columns
  for all indexes：Only statistically relevant indexes are analyzed
  for all indexed columns：Only indexed table columns
  for all hidden columns:Analyze all hidden columns（Function index, etc.
  for columns &lt;list&gt; SIZE &lt;N&gt; | REPEAT | AUTO | SKEWONLY:Statistics of specified columnshistograms.
      N的Value range[1,254]; 
      REPEAT：Last countedhistograms;
      AUTO：Depend onoracleDecideNThe size of;
      skewonly：Oracle Identify the distribution of each column value in each index that needs to be collected and checked
  For example：
  	method_opt=&gt;'for all columns size skewonly'
  	method_opt=&gt;'for all columns size repeat'
  	method_opt=&gt;'for all columns size auto'
    
:Decide并行度.The default value isnull.
  Accelerate indexing speed,according to cpu Set the quantity，Usually when the business is idle degree Can be set as cpu quantity－1，Be smaller when you are busy。

:Granularity of statistics to be collected（Related only if the table is partitioned）
  according to将优化的 SQLStatement，The optimizer can choose to use partition statistics or global statistics，For most systems, both statistics are very
Important，Oracle Recommended GRANULARITY Set as AUTO Collect all information at the same time。  
			①'AUTO'-according to分区类型确定粒度。这It's the default value。
			②'ALL' -Collect all（Subpartition，Partition and global）Statistical information
			③'GLOBAL' -Collect global statistics
			④'GLOBAL AND PARTITION'-收集全局and分区级别的Statistical information。Even if it is a composite partition object，也不会收集任何Subpartition级别Statistical information。
			⑤'PARTITION '-收集分区级别的Statistical information
			⑥'SUBPARTITION' -收集Subpartition级别的Statistical information
			⑦'DEFAULT'-收集全局and分区级别的Statistical information。This option is outdated，And currently supported，But it is included in the document only for legacy reasons。You should use“ GLOBAL AND PARTITION”To implement this function。Please note，The default value is now'AUTAUTO'。

:It collects index information.Default isFALSE.

:用户Statistical information表Identifier，用于描述将当前Statistical information保存在何处
  statid如果多个表的Statistical information存储在同一个stattabUsed to distinguish，Identifier
  statown存储Statistical information表的拥有者.Included architecturestattab（If different fromownname）
If the above three parameters are not specified,Statistical information会直接更新到数据字典.

11.no_invalidate: Configure through different parameters，Can be achieved Oracle Control of failed shared cursor behaviorTRUE，FALSE
  If the value is true，It means that the cursor failure action is not performed，The original shared cursor Stay in original state。
  If the value is false，Indicates all related to the statistic object cursor All invalid。
  如果Set asauto_invalidate，according to官方文档，Oracle 自己Decide shared cursor Failed action。
  from 10G start，Oracle Will auto_invalidate As the default statistic collection behavior。
  select dbms_stats.get_param(pname =&gt; 'no_invalidate') from dual;

:即使表锁住了也收集Statistical information.

 Analysis mode
use 4 One of the preset methods，This option can be grasped Oracle Statistical refresh method：
  gather——Reanalyze the entire（Schema）。
  gather empty——Only analyze the tables that are not currently counted。
  gather stale——Only reanalyze the number of modifications exceeds 10%Table of（These modifications include insertion、Update and delete）。
  gather auto——Reanalyze objects that were not previously counted，And the statistics expired（Got dirty）Object of。
Notice，use gather auto 相似于组合use gather stale and gather empty。Notice，whether gather stale Still gather auto，All requests monitoring。If you implement onealter table xxx monitoring Order，Oracle Can use it dba_tab_modifications 视图来跟踪发生变动Table of。This way，You know indeed，自from上一次剖析统计数据以来，How many times have the insertion occurred、Update and delete操作。

: Specify a list of objects
15.obj_filter_list：A list of object filters
16.gather_sys：Collect only sys Object of。
：Statistical information类型。The only value allowed isDATA。

2.3 Collect histograms

Histogram collection method
1. Syntax format
  fordbms_statsBag，It is specifiedmethod_optParameter implementation，This parameter is acceptable for the following：
1.1Statistics of all columnshistograms(Histogram).
for all [indexed | hidden] columns [size_clause]

1.2Statistics of specified columnshistograms(Histogram).
for columns [size_clause] column | attribute [size_clause] [,column | attribute [size_clause]...]
  For example： 
    for columns column  size 1
    for columns column  size auto
  insize_clauseMust comply with the following formats:size [Integer value | repeat | auto | skewonly]

      Integer value：Histogram的Bucketnumber，The range is[1,254]，for1表示删除List上Histogram信息
      repeat：只对已有Histogram的List收集Histogram信息
      auto：Depend onoracle决定是否对List收集Histogram，以及使用哪类Histogram。oracleBy default, only used columns（whereColumns that appear in the condition）自动收集Histogram统计信息，oracleWill be heresys.col_usage$Record the usage of each column in the base table。在自动收集Histogram统计信息时先查该表，If the column has not been used，Will not collect。
      skewonly：只对倾斜List收集Histogram 

2.distance
2.1 For all columns in the tableauto方式收集Histogram
exec dbms_stats.gather_table_stats(ownname =&gt; 'USER01',tabname =&gt; 'TAB01',method_opt=&gt; 'for all columns size auto');

2.2 For all columns with indexes on the tableauto方式收集Histogram
exec dbms_stats.gather_table_stats(ownname =&gt; 'USER01',tabname =&gt; 'TAB01',method_opt=&gt; 'for all indexed columns size auto');

2.3 For the tableempnoanddeptnoListedauto方式收集Histogram
exec dbms_stats.gather_table_stats(ownname =&gt; 'USER01',tabname =&gt; 'TAB01',method_opt=&gt; 'for columns size auto empno deptno');

2.4 For the tableempnoanddeptnoList收集Histogram，and specifyBucketnumberfor10
exec dbms_stats.gather_table_stats(ownname =&gt; 'USER01',tabname =&gt; 'TAB01',method_opt=&gt; 'for columns size 10 empno deptno');

2.5 For the tableempnoanddeptnoList收集Histogram，SpecifyempnoListBucketnumberfor10，deptnoListBucketnumberfor5
exec dbms_stats.gather_table_stats(ownname =&gt; 'USER01',tabname =&gt; 'TAB01',method_opt=&gt; 'for columns empno size 10 deptno size 5')
 
3.查询Histogram
SELECT COLUMN_NAME,NOTES,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SALES2'; 

select * from user_histograms;

2.4 Collect database statistics information

gather_database_stats Collect database-level statistics information

GATHER_DATABASE_STATS Syntax and Parameters

DBMS_STATS.GATHER_DATABASE_STATS (
  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 DEFAULTto_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,
  gather_sys BOOLEAN DEFAULT TRUE,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
    get_param('NO_INVALIDATE')),
  obj_filter_list ObjectTab DEFAULT NULL);

gather_database_stats test:

SQL>exec DBMS_STATS.gather_database_stats;
SQL>exec DBMS_STATS.gather_database_stats(estimate_percent => 15);
SQL>exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

process:

SQL> exec DBMS_STATS.gather_database_stats;
PL/SQL procedure successfully completed

View the process

SQL> select distinct sid, serial# from v$session_longops where opname like'%Gather%';
       SID    SERIAL#
---------- ----------
       459      20726

       select distinct sid, serial# from v$session_longops where opname like'%gather_database_stats%';

If you want to terminate:

SQL> alter system kill session '459,20726';
System altered

2.5 Collect user statistics information

1) GATHER_SCHEMA_STATS syntax and parameters

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 DEFAULTto_degree_type(get_param('DEGREE')),
  granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
  cascade BOOLEAN DEFAULTto_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);	
 GATHER_SCHEMA_STATS

2.5.2 Recollect all user statistics

begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
                                options => 'gather',
                                estimate_percent => 100,
                                method_opt => 'for all indexed columns',
                                degree => 2);
end;
/

2.5.3 Recollect objects that users have not counted before

begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
                                options => 'gather auto',
                                estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,
                                method_opt => 'for all columns size auto',
                                cascade => true,
                                degree => 2);
end;
/

2.5.4 Collect user partition tables and index information

begin
dbms_stats.gather_schema_stats(ownname => 'CMXBUSI',
                                estimate_percent => 10,
                                cascade => true,
                                granularity => 'all',
                                degree => 2);
end;
/

2.6 Collect statistics on the table

Gather_table_stats

1) Functions of statistical information dbms_stats.gather_table_stats Introduction:

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 defaultto_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')),
  force boolean default false);

2) Gather_table_stats test:

– View the statistics of the table

SQL>select table_name, blocks, empty_blocks, num_rows from SQL>dba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name, blocks, empty_blocks, num_rows,LAST_ANALYZED fromdba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'SCOTT' and table_name = 'T1';
SQL>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'SCOTT' order by table_name;

– View statistics in table columns

SQL>select table_name,column_name,last_analyzed,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'SCOTT' and table_name ='T1';

– View statistics of table index

SQL>select table_name,index_name,last_analyzed,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows fromdba_indexes where owner = 'SCOTT' and table_name = 'T1';

–Scenario 1: Collect tables, including statistics on all columns and indexes of the table

SQL>analyze table ITPUX_M10 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent =>dbms_stats.AUTO_SAMPLE_SIZE,
                               >method_opt => 'for all indexed columns',
                               >cascade => true,
                               >degree => 2);
>end;
>/

–Scenario 2: Only the statistics of the table are collected, the sampling ratio is 15%

SQL>analyze table ITPUX_M10 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 15,
                               >method_opt => 'for table',
                               >cascade => false,
                               >degree => 2);
>end;
>/

–Scenario 3: Only statistical information of the table is collected, and the information of two columns does not collect histograms

SQL>analyze table T1 delete statistics;
SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 100,
                               >method_opt => 'for columns size 1 object_name status',
                               >cascade => false,
                               >degree => 2);
>end;
>/

–Scenario 4: Only collect information about tables, statistics about all columns of tables and all indexes of tables

SQL>begin
>dbms_stats.gather_table_stats(ownname => 'SCOTT',
                               >tabname => 'T1',
                               >estimate_percent => 100,
                               >cascade => true,
                               >degree => 2);
>end;
>/

–Scenario 5: (including tables, partition tables, sub-partitions)

SQL&gt;begin
&gt;dbms_stats.gather_table_stats(ownname =&gt; 'SCOTT',
                               &gt;tabname =&gt; 'T1',
                               &gt;estimate_percent =&gt; 18,
                               &gt;cascade =&gt; true,
                               &gt;granularity =&gt; 'ALL',    --Global table，Partition table，子Partition table
                               &gt;degree =&gt; 2);
&gt;end;
&gt;/

–Scenario 6: Statistical information collection at the partition level

SQL&gt;begin
&gt;dbms_stats.gather_table_stats(ownname =&gt; 'SCOTT',
                               &gt;tabname =&gt; 'T1',
                               &gt;partname =&gt; 'Partition name',
                               &gt;estimate_percent =&gt; 5,
                               &gt;cascade =&gt; true,
                               &gt;granularity =&gt; 'PARTITION',
                               &gt;method_opt =&gt; 'for all indexes columns',
                               &gt;degree =&gt; 2);
&gt;end;
&gt;/

–Scenario 7: Delete the statistics of the table

SQL>begin
>dbms_stats.delete_table_stats (ownname => 'SCOTT',tabname >=>'T1');
>end;
>/

2.7 Collect index statistics

1) Gather_index_stats syntax

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);

2.8 Data dictionary table collection

GATHER_DICTIONARY_STATS：
DBMS_STATS.GATHER_DICTIONARY_STATS (
  comp_id 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,
  options VARCHAR2 DEFAULT 'GATHER AUTO',
  objlist OUT ObjectTab,
  statown VARCHAR2 DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),
  obj_filter_list ObjectTab DEFAULT NULL);

SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS
(estimate_percent=>100, Degree=>8, Cascade=>TRUE, Granularity=>'ALL');

2.9 Dynamic Performance Table Collection

Dynamic performance table statistics collectionGATHER_FIXED_OBJECTS_STATS
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
  stattab VARCHAR2 DEFAULT NULL,
  statid VARCHAR2 DEFAULT NULL,
  statown VARCHAR2 DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')));

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

test:
v$lock view access slow solution
V$ view access slow – workaround
Analysis: It may be caused by the data dictionary statistical information for too long.

SQL>exec dbms_stats.gather_fixed_objects_stats;

2.10 Collect statistics on fixed tables for all data dictionaries

Do not perform during peak business periods.

If you need to perform statistical analysis on this fixed_table separately, you can also use:

SQL>exec DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KSUSE');
SQL>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'RECYCLEBIN$');

Then confirm the correctness of the statistics:

SQL>select count(*) from sys.tab_stats$;
SQL>select num_rows, last_analyzed from user_tab_statistics where table_name ='X$KSUSE';

Query about base table:

SQL>select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$LOCK';
or:
SQL>select dbms_metadata.get_ddl('VIEW', 'DBA_EXTENTS') from dual;

3. analyze collects statistical information

Three major functions

① Collect and delete statistical information of indexes, tables and clusters

② Verify the structure of tables, indexes and clusters

③ Authenticate tables and clusters and row migrations and row links

3.1 Table situation query

View statistics in the table

select table_name, blocks, empty_blocks, num_rows
from dba_tables
where owner = 'CMXBUSI'
and table_name = 'T02';

View statistics in table columns

select table_name,
column_name,
num_distinct,
low_value,
high_value,
histogram,
density
from dba_tab_columns
where owner = 'CMXBUSI'
and table_name = 'T02';

View statistics of table index

select * from dba_indexes
leaf_blocks,              -- Leaf block data in index
blevel,                   -- B Tree index level
distinct_keys,            -- Number of different index values
avg_leaf_blocks_per_key,  -- How many leaf blocks are there on average for each value in the index，If it is a primary key/The only thing is 1.
avg_data_blocks_per_key,  -- Point to a block of data in the table by a value in the index，The average number of data blocks。
clustering_factor,        -- Aggregation factor，The smaller the better，The bigger the worse the。num_rows --Number of rows indexed。

SELECT  TABLE_NAME,INDEX_NAME,LEAF_BLOCKS,blevel,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR
from dba_indexes
WHERE TABLE_OWNER='CMXBUSI'
and table_name = 'T02';

3.2 Table collection

analyze table t1 compute statistics for table;

3.3 Table field collection

analyze table T1 compute statistics for all columns;

3.4 Index Field Collection

analyze table T1 compute statistics for all indexed columns;

3.5 Collect tables, table fields, and index fields simultaneously

analyze table T1 compute statistics;

3.6 Index Collection

analyze table T1 compute statistics for all indexes;

3.7 Collect tables, table fields, and indexes at the same time

analyze table T1 compute statistics for table for all indexes for all columns;

3.8 Delete statistics information

analyze table t1 delete statistics;

3.9 Verify the structure of table, index, and partition

analyze table T1 validate structure;

analyze table CMXBUSI.T01 validate structure;

4. Knowledge summary

Here is a summary of the commands and toolkits:

1. For partition tables, it is recommended to use DBMS_STAT instead of using analyze statement.

a) It can be carried out in parallel, for multiple users and multiple tables

b) You can obtain the data of the entire partition table and the data of a single partition.

c) Compute statics can be computed at different levels: single partition, sub-partition, full table, all partitions.

d) Statistical information can be exported

e) Users can automatically collect statistical information

Disadvantages of DBMS_STATS

a) Cannot validate structure

b) CHAINED ROWS cannot be collected, CLUSTER TABLE information cannot be collected, these two still require the use of analyze statements.

c) DBMS_STATS does not analyze the index by default, because the default cascade is false and needs to be manually specified as True.

Summarize

This is the end of this article about Oracle database statistics. For more information about Oracle statistics, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!