Oracle学习系列—数据库优化—Collect Statistics

虽然提倡使用DBMS_STATS包,估计ANALYZE使用还是会多一些吧,毕竟方便...

收集统计信息,收集分区对象的全局信息,调整你的统计信息.

但是,以下情况下,你必须使用ANALYZE语句,而不是DBMS_STATS来收集统计信息(这些信息与CBO优化无关):

n To use the VALIDATE or LIST CHAINED ROWS clauses

n To collect information on freelist blocks

Table 3–1 Statistics Gathering Procedures in the DBMS_STATS Package Procedure Collects

GATHER_INDEX_STATS Index statistics

GATHER_TABLE_STATS Table, column, and index statistics

GATHER_SCHEMA_STATS Statistics for all objects in a schema

GATHER_DATABASE_STATS Statistics for all objects in a database

GATHER_SYSTEM_STATS CPU and I/O statistics for the system

收集系统统计信息

系统统计信息能够确保优化器考虑系统I/O和CPU的性能和利用率.对于每一个候选执行计划,优化器进行I/O和CPU成本的评估.了解系统如何在平衡I/O和CPU成本中选择最佳计划是非常重要的.

系统I/O依赖于许多因素,不是一个常量.通过系统统计信息,数据库管理员能够获取通常状态下的系统压力.例如系统应用能够在白天处理OLTP事务,同时在晚上运行OLAP报表.系统管理员收集这两种状态下的信息,需要的时候,激活OLTP或者OLAP统计信息.这能够保证优化器根据系统资源计划产生相应的成本.

Oracle产生系统统计信息时,主要分析一段时间内的系统活动情况..和表索引字段统计不同,系统统计信息更新时Oracle不会使的已经解析的SQL语句无效,新的SQL语句将根据新的统计信息进行解析.

DBMS_STATS.GATHER_SYSTEM_STATS可以根据用户自定义的方式收集系统信息.

DBMS_STATS.SET_SYSTEM_STATS能够指定系统统计参数.

execute dbms_stats.create_stat_table(ownname => 'WBQ',stattab => 'MYSTATS');

Gather statistics during the day. Gathering ends after 720 minutes and is stored in the mystats table:

BEGIN

DBMS_STATS.GATHER_SYSTEM_STATS(

gathering_mode => 'interval',

interval => 720,

stattab => 'mystats',

statid => 'OLTP');

END;

/

Verifying Table Statistics

To verify that the table statistics are available, query the data dictionary view DBA_TABLES, using a statement like the one in Example 3–3:

Example 3–3 Verifying Table Statistics

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,

TO_CHAR(LAST_ANALYZED, ’MM/DD/YYYY HH24:MI:SS’)

FROM DBA_TABLES

WHERE TABLE_NAME IN (’SO_LINES_ALL’,’SO_HEADERS_ALL’,'SO_LAST_ALL');

Verifying Index Statistics

To verify that index statistics are available and decide which are the best indexes to use in an application, query the data dictionary view DBA_INDEXES, using a statement like the one in Example 3–4:

Example 3–4 Verifying Index Statistics

SQL> SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT",

LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL",

AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"

FROM DBA_INDEXES

WHERE OWNER = 'SH'

ORDER BY INDEX_NAME;

Optimizer Index Determination Criteria

The optimizer uses the following criteria when determining which index to use:

n Number of rows in the index (cardinality).

n Number of distinct keys. These define the selectivity of the index.

n Level or height of the index. This indicates how deeply the data probe must search in order to find the data.

n Number of leaf blocks in the index. This is the number of I/Os needed to find the desired rows of data.

n Clustering factor (CF). This is the collocation amount of the index block relative to data blocks. The higher the CF, the less likely the optimizer is to select this index.

n Average leaf blocks for each key (ALFBKEY). Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always one.

Verifying Column Statistics

To verify that column statistics are available, query the data dictionary view DBA_TAB_COL_STATISTICS, using a statement like the one in Example 3–5:

Example 3–5 Verifying Column Statistics

SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY

FROM DBA_TAB_COL_STATISTICS

WHERE TABLE_NAME ="PA_EXPENDITURE_ITEMS_ALL"

ORDER BY COLUMN_NAME;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-145526/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145526/