Update statistics for accurate generation of execution plans-analyze and Dbms_stats

If we want the CBO to use the statistics of reasonable use of the data to correctly determine the quickest way to execute any SQL query, it is necessary to use the Analyze command or dbms_stats the statistics of the data in a timely manner.

For example, in the index skip scan example, if you do not collect statistics for table employee and index, it is not the index skip scan policy.

Analyze statistical information

Analyze table can generally specify analysis: Table, all fields, all indexed fields, all indexes. If not specified, all are analyzed.

---table Statistical InformationAnalyzeTableEmpCompute Statistics  for Table;---column Statistical InformationAnalyzeTableEmpCompute Statistics  for  Allcolumns;---Index statistical informationAnalyzeTableEmpCompute Statistics  for  Allindexes;---Index Column StatisticsAnalyzeTableEmpCompute Statistics  for  Allindexed columns;---effect equals analyze table TableName compute statistics for table for all indexes for all columnsAnalyzeTableTableNameCompute Statistics

To view statistical information

Statistics for table exist in views: User_tables, All_tables, Dba_tables

Statistics for indexes are present in views: User_indexes, All_indexes, dba_indexes

The statistics for all columns exist in an attempt to: User_tab_columns, All_tab_columns, Dba_tab_columns

SCOTT@PDBORCL>AnalyzeTableEmpCompute Statistics  for Table; The table has been parsed. SCOTT@PDBORCL>AnalyzeTableEmpCompute Statistics  for   Allcolumns; The table has been analyzed. SCOTT@PDBORCL>AnalyzeTableEmpCompute Statistics  for   Allindexes; The table has been analyzed. SCOTT@PDBORCL> SelectTable_name,num_rows fromUser_tableswheretable_name= 'EMP';

table_name    Num_rows----------------------

  EMP  14  scott   @PDBORCL  >  select  index_name,uniqueness from  user_indexes where  table_name "  emp   " ;  

 index_name uniquenes  -- --------------- pk_emp  unique  Span style= "color: #000000;" >scott   @PDBORCL  >  select  column_name,data_type from  user_tab_columns where  table_name "  emp   " data_type   -- --------- -------------- 

EMPNO          Number ename         VARCHAR2 JOB           VARCHAR2 MGR            Number HireDate     Datesal          numberCOMM         numberDEPTNO       number 8 lines  .   SCOTT@PDBORCL>^A

Delete statistic information

All statistics of EMP will be removed.

Table Statistics

Delete only some columns become empty, such as the number of lines in the EMP table is empty

SCOTT@PDBORCL>AnalyzeTableEmpCompute Statistics; The table has been parsed. SCOTT@PDBORCL> SelectTable_name,num_rows fromUser_tableswheretable_name= 'EMP'; TABLE_NAME Num_rows--------    ----------EMP -SCOTT@PDBORCL>AnalyzeTableEmpDelete Statistics; The table has been parsed. SCOTT@PDBORCL> SelectTable_name,num_rows fromUser_tableswheretable_name= 'EMP'; TABLE_NAME Num_rows-------- ----------Empscott@PDBORCL>

Dbms_stats

Dbms_stats can well estimate statistics (especially for larger partitioned tables) and get better statistical results, eventually making a faster SQL execution plan.

Grammar:

Dbms_stats.gather_table_stats (ownnameVarchar2, tabnamevarchar2 PartName number, Block_sample Boolean, method_opt varchar2 degree Span style= "color: #0000ff;" >numbervarchar2 cascade Boolean, Stattab varchar2 Statid varchar2 Statown       

Dbms_stats.delete_table_stats is used to delete statistics.

Example:

------Delete statistics [email protected]> exec dbms_stats.delete_table_stats (ownname = ' Scott ', tabname = ' emp ');PL/The SQL process has completed successfully. ----Query statistic informationSCOTT@PDBORCL> SelectTable_name,num_rows fromUser_tableswheretable_name= 'EMP'; TABLE_NAME Num_rows-------- ----------EMP---Get statistical information [email protected]> exec dbms_stats.gather_table_stats (ownname = ' Scott ', tabname = ' emp ');PL/The SQL process has completed successfully. ---Re-check new statsSCOTT@PDBORCL> SelectTable_name,num_rows fromUser_tableswheretable_name= 'EMP'; TABLE_NAME Num_rows-------- ----------Emp -SCOTT@PDBORCL>

Reference:

Update statistics for accurate generation of execution plans-analyze

http://docs.oracle.com/database/121/TGSQL/tgsql_stats.htm#TGSQL389

Update statistics for accurate generation of execution plans-analyze and Dbms_stats