Oracle automatic statistics gathering

STATISTICS rules
1. 10g and above: Oracle statistics saved in data dictionary, and optimizer chooses the best execution plan for every SQL based on statistics, by CBO (Cost-BasedOptimization) only.
2. 9g: Oracle chooses execution plan by RBO（Rule-BasedOptimization）or CBO.


1. Cost in execution plan is summation of CPU and I/O as default.
2. Parameter Optimizer_mode to control optimizer
2.1. first_rows: The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
2.2. all_rows: Default. The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput （minimum resource use to complete the entire statement）
2.3. first_rows_n: The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows （where n = 1, 10, 100, 1000）
2.4. rule, choose: only for 9i.
3. Parameter OPTIMIZER_INDEX_COST_ADJ: OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
3.1. rate of cost of MultiBlock I/O (table scan) and Single-block I/O (index scan). Oracle identifies OPTIMIZER_INDEX_COST_ADJ to affect CBO.
3.2. OPTIMIZER_INDEX_COST_ADJ less, table full scan
3.3. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost.



STATISTICS
1. Table statistics：Number of rows, Number of blocks, Average row length；
2. Column statistics：Number of distinct values (NDV) in column, Number of nulls in column, Data distribution (histogram)；
3. Index statistics：Number of leaf blocks, Levels, Clustering factor；
4. System statistics：I/O performance and utilization、CPU performance and utilization。==>only can be gathered manually


STATISTIC_LEVEL
statistics, reporting status information controlled by parameters for STATISTIC_LEVEL can be viewed in V$STATISTICS_LEVEL


Statistics not for...
1.tables being deleted or truncated in session and re-create
==>set statistics for these tables NULL, then Oracle will dynamically gather statistics a part of by optimized SQL
==>fix typical statistics for most representative table situation and take as optimized statistics
2.heavy-loading tables being inserted or updated largely in session with more than 10% modified
==>manually gather statistics after large modification as SQL procedure or database operation


MUST gather statistics manually
1. External table
2. Automatic statistics gathering stopped
3. system statistics
4. fixed objects, like dynamic performance tables



difference between 11g and 10g
1. Gather statistics with same options for all objects in 10g; options for different gathering can be setup separately for global, database, schema, and table
2. New options
2.1. PUBLISH: whether publish statistics to data dictionary after gathering
2.2. INCREMENTAL: incrementally gather global statistics for partition table
2.3. STALE_PERCENT: modified percentage(default 10%) of statistics for certain object is outdated

11 LEVELS for DYNAMIC SAMPLING
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94760

DYNAMIC SAMPLING DISADVANTAGES
1. Selection bias for large database
2. Taking resource, not suitable for OLTP, repeated execution, bound variables, few hard analysis

DYNAMIC SAMPLING ADVANTAGES
1. Suitable for data warehouse or OLAP, since SQL execution costs much more than SQL parsing
2. Support CBO for more information
3. CBO only can gather statistics for global temporary table by dynamic sampling
4. Do statistics for different rows.













http://space.itpub.net/35489/viewspace-608036