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/，如需轉載，請註明出處，否則將追究法律責任。