Dbms_stats package Update, export, import, lock statistics

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 well estimate statistics (especially for larger partitioned tables) and get better statistical results, eventually making a faster SQL execution plan.

--Create a stage table for storing statistics
exec dbms_stats.create_stat_table (ownname = ' SCOTT ', stattab = ' st_t1 ', tblspace = ' TBS1 ');

--delete the specified stage table that holds the statistics
EXEC dbms_stats. Drop_stat_table (' Scott ', ' st_t1 ');

--Use the Export_table_stats process to export statistics, at which point Statid is t1_20160101
exec dbms_stats.export_table_stats (ownname = ' SCOTT ', tabname=> ' T1 ', stattab=> ' st_t1 ', Statid = t1_ 20160101 ');

--use Import_table_stats to import previously old statistics (you can specify a new Ownname)
exec dbms_stats.import_table_stats (ownname = ' SCOTT ', tabname=> ' T1 ', stattab=> ' st_t1 ', Statid = t1_ 20160101 ', no_invalidate = true);

Statistical information locking/unlocking

--锁住表的统计信息

Update the statistics for the schema or table:

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

EXEC Dbms_stats.gather_table_stats (
Ownname = ' Socct ',
tabname = ' T1 ',
Estimate_percent =>100,
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 by default of 100
a new parameter that automatically estimates the optimal percentage of a segment to be sampled:estimate_percent = dbms_stats.auto_sample_sizewhen using automatic sampling, Oracle chooses a percentage of 5 to 20 for a sample size. Method_opt Options
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 takes a lot of processing time because it checks the distribution of the value 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.

Options parameters for Gather_schema_stats
using one of the 4 preset methods, this option controls how Oracle statistics is refreshed:
gather--re-parses the entire schema.
gather empty--only analyzes tables that have not yet been counted.
gather stale--only re-parses tables with more than 10% modifications, including inserts, updates, and deletions.
gather auto--re-analyzes objects that do not currently have statistics, and 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