Dbms_stats package User Manual

There are more than 40 stored procedures under the dbms_stats package. It is very important to generate the execution plan. Common examples include:

Analysis database (including all user objects and System Objects): gather_database_stats
Analyze all user objects (including tables, indexes, and clusters): gather_schema_stats
Analysis table: gather_table_stats
Analysis index: gather_index_stats
Delete database statistics: delete_database_stats
Delete user solution statistics: delete_schema_stats
Delete table statistics: delete_table_stats
Delete index statistics: delete_index_stats
Delete column statistics: delete_column_stats
Set table statistics: set_table_stats
SET index statistics: set_index_stats
Set column statistics: set_column_stats

You can view the dba_tables table to check whether the table has been analyzed, for example:

Select table_name, last_analyzed from dba_tables

Dbms_stats.gather_table_stats and dbms_stats.gather_index_stats are usually used.

Procedure gather_table_stats parameter name type input/output default value? Invalid variable ------ -------- ownname varchar2 intabname varchar2 inpartname varchar2 In Invalid Number in invalid Boolean in invalid varchar2 In Invalid Number in invalid varchar2 in invalid Boolean in defaultstattab varchar2 in defastatstatid varchar2 in invalid varchar2 in invalid Boolean in bytes then parameter name type input/output default value ------------------------------ ----------------------- ------ -------- ownname varchar2 ininindname varchar2 inpartname varchar2 in character number in defaultstattab varchar2 in character number in character varchar2 in character boolean in default

Note:

Ownname: owner of the table to be analyzed

Tabname: name of the table to be analyzed.

Partname: partition name, which is only useful for partitioned tables or partition indexes.

Estimate_percent: this parameter is equivalent to "estimate statistics sample x percent" in analyze ". estimate the percentage of the total number of rows. if this parameter is null. it is compute. You can also use dbms_stats.auto_sample_size to let Oracle determine the estimated percentage.

Block_sample: this parameter is a Boolean parameter. It determines whether to perform random sampling.

Method_opt: Specifies whether a histogram is used when data publication is unbalanced. Optional values: "For all columns or for all indexed columns. Of course, you can also use auto. To let Oracle decide to collect

Degree: Number of CPUs in parallel degree (parallel collection dimension)

Cascade: determines whether to collect statistics on indexes of related tables.

Force: Collects statistics even if the table is locked.

 

Case:

-- Create a statistical information History Table

sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

-- Export the statistics of the entire scheme

sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 

-- Analyze Scheme

Exec dbms_stats.gather_schema_stats( ownname => 'scott', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns ', degree => 6 ) 

-- Analysis table

sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ; 

-- Analysis index

SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;

-- If an execution plan error is found, delete the table statistics.

SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

-- Historical statistics of the imported table

sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ; 

-- If the execution plan of most tables is incorrect after analysis, the statistics of the entire scheme must be imported back.

sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

-- Import index statistics

SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table') 

-- Check whether the import is successful

SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';