Dbms_stats package Update, export, import, lock statistics

Translated from: http://www.cnblogs.com/zhaoguan_wang/p/5126629.html

With the advent of the Dbms_stats package, we can collect statistical data for the CBO in a new way. The use of old-fashioned analyze analysis tables and dbms_utility methods to generate CBO statistics is no longer recommended. Dbms_stats can estimate statistics well (especially for larger partitioned tables) and get better statistical results, resulting in a faster SQL execution plan

1 --Create a stage table for storing statistics2 3 execDbms_stats.create_stat_table (ownname= 'SCOTT', Stattab= 'st_t1', Tblspace= 'TBS1'); 4 5 --Delete the specified stage table that holds statistics6 execDbms_stats. Drop_stat_table ('Scott','st_t1');

--Use the Export_table_stats process to export statistics, at which point Statid is t1_20160101

exec = ' SCOTT ', tabname='T1', stattab='st_t1  '='t1_20160101');

--use Import_table_stats to import previously old statistics (you can specify a new Ownname)

1    exec = ' SCOTT ',  tabname='T1',  stattab='  st_t1'='t1_20160101'= 

Statistical information locking/unlocking

 1  --  Statistics for locked tables  2  exec  dbms_stats.lock_ Table_stats ( '  scott  , "  t1   "  3  --  Ching statistics  4  exec  dbms_stats.unlock_ Table_stats ( '  scott  , "  t1   "); 

Update the statistics for the schema or table:

1 execDbms_stats.gather_schema_stats (ownname= 'SCOTT', Options= 'GATHER AUTO', estimate_percent=Dbms_stats.auto_sample_size, method_opt= 'For all columns size repeat', Degree= 8    );2 3 4 EXECDbms_stats.gather_table_stats (ownname= 'Socct', tabname='T1', estimate_percent= -, method_opt= 'For all columns size auto',Cascade =True, Degree= 8);

estimate_percent options    It allows Oracle's dbms_stats to specify a specific sample scale when collecting statistics, the default is 100     estimate_percent = dbms_stats.auto_sample_size  

method_opt option to intelligently generate histograms, Oracle prepares method_opt parameters for dbms_stats. In the METHOD_OPT clause, there are some important new options, including Skewonly,repeat and auto: method_opt=> ' for all columns size skewonly '--   The skewonly option consumes a lot of processing time because it checks the distribution of the values of each column in each index. method_opt=> ' For all columns size repeat '--will only re-parse the index for the existing histogram and no longer search for other histogram opportunities.   You should take this approach when you periodically re-analyze the statistics. method_opt=> ' For all columns size auto '-creates histograms based on data distribution and how the application accesses columns (for example, using ALTER TABLE XXX monitoring; command Watch table, Use Auto option) Parallel Collection Oracle allows CBO statistics to be collected in parallel, which significantly increases the speed at which statistics are collected. However, to collect statistics in parallel, you need an SMP server with multiple CPUs installed. gather_schema_stats options parameters    gather--re-parses the entire schema.    gather empty--only analyzes the tables that are not currently counted.    gather stale--only re-analyzes tables that have a modified amount of more than 10% (these modifications include INSERT, UPDATE, and delete).    gather auto--re-analyzes objects that do not currently have statistics, as well as objects that have statistics that expire (become dirty). Note that using gather auto is similar to combining the use of gather stale and gather empty.    note that monitoring is required regardless of gather stale or gather auto. If you execute an ALTER TABLE XXX monitoring command, Oracle uses the Dba_tab_modifications view to track the changed table. This way, you know exactly how many insertions, updates, and deletions have occurred since the last time the statistics were analyzed.

There are also references to the negative effects of the collection of index statistics, which suggest using the ALTER index IDX_NAME rebuild nologging directly; compared with analyze dbms_stats has many advantages, such as parallel, such as partition information statistics, but dbms_stats in the analysis of index when the processing method is not too ideal, dbms_stats Analysis index will only be counted leaf_ Blocks is the leaf block with the current data, and analyze is counted as all the leaf block numbers that have been used, and it is clear that Dbms_stats's statistical results will severely underestimate the cost of index fast full scan. In some cases it would be wrong to choose Index Fast full scan as the execution path.

Dbms_stats package Update, export, import, lock statistics