PLSQL_效能最佳化系列15_Oracle Statistics統計資訊

標籤：des   style   blog   http   ar   io   color   os   使用   

2014-12-18 BaoXinjian

一、摘要

Statistic 對Oracle 是非常重要的。

它會收集資料庫中對象的詳細資料，並儲存在相應的資料字典裡。 根據這些統計資訊， optimizer 可以對每個SQL 去選擇最好的執行計畫。

Statistic 對Oracle 是非常重要的，它會收集資料庫中對象的詳細資料，並儲存在相應的資料字典裡。

根據這些統計資訊， optimizer 可以對每個SQL 去選擇最好的執行計畫。

Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包來收集。

Oracle 建議使用DBMS_STATS包來收集統計資訊，因為DBMS_STATS包收集的更廣，並且更準確，Analyze 在以後的版本中可能會被移除。

 

二、統計資訊內容和層次

1. Table statistics

(1). 行數，塊數，行平均長度

(2). DBA_TBALES：NUM_ROWS，BLOCKS，AVG_ROW_LEN；

• Number of rows
• Number of blocks
• Average row length

2. Column statistics

(1). 列中唯一值的數量（NDV），NULL值的數量，資料分布；

(2). DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；

• Number of distinct values (NDV) in column
• Number of nulls in column
• Data distribution (histogram)

3. Index statistics

(1). 葉塊數量，等級，聚簇因子；

(2). DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；

• Number of leaf blocks
• Levels
• Clustering factor

4. System statistics

(1). 儲存在aux_stats$中，需要使用dbms_stats收集，I/O統計在X$KCFIO中；

• I/O performance and utilization
• CPU performance and utilization

 

三、統計資訊文法

1. analyze

需要使用ANALYZE統計的統計：使用LIST CHAINED ROWS和VALIDATE子句收集空閑列表塊的統計；

analyze 不適合做分區表的分析

• analyze table tablename compute statistics;
• analyze index|cluster indexname estimate statistics;
• analyze table tablename compute statistics for table /for all [local] indexes / for all [indexed] columns
• analyze table tablename delete statistics
• analyze table tablename validate ref update
• analyze table tablename validate structure [cascade]|[into tablename]
• analyze table tablename list chained rows [into tablename]

2. dbms_stats

dbms_stats能良好地估計統計資料（尤其是針對較大的分區表），並能獲得更好的統計結果，最終制定出速度更快的SQL執行計畫。

這個包的下面四個預存程序分別收集index、table、schema、database的統計資訊:

• dbms_stats.gather_table_stats     收集表、列和索引的統計資訊；
• dbms_stats.gather_schema_stats    收集SCHEMA下所有對象的統計資訊；
• dbms_stats.gather_index_stats     收集索引的統計資訊；
• dbms_stats.gather_system_stats    收集系統統計資訊
• dbms_stats.gather_dictioinary_stats   所有字典對象的統計；
• dbms_stats.delete_table_stats     刪除表的統計資訊
• dbms_stats.delete_index_stats     刪除索引的統計資訊
• dbms_stats.export_table_stats     輸出表的統計資訊
• dbms_stats.create_state_table
• dbms_stats.set_table_stats     設定表的統計
• dbms_stats.auto_sample_size

 

四、統計資訊文法

4.1 統計資訊收集如下資料：

（1）表自身的分析： 包括表中的行數，資料區塊數，行長等資訊。

（2）列的分析：包括列值的重複數，列上的空值，資料在列上的分布情況。

（3）索引的分析： 包括索引葉塊的數量，索引的深度，索引的彙總因子等。

4.2 這些統計資訊存放在資料字典裡，如：

（1）.  DBA_TABLES

（2）.  DBA_OBJECT_TABLES

（3）.  DBA_TAB_STATISTICS

（4）.  DBA_TAB_COL_STATISTICS

（5）.  DBA_TAB_HISTOGRAMS

（6）.  DBA_INDEXES

（7）.  DBA_IND_STATISTICS

（8）.  DBA_CLUSTERS

（9）.  DBA_TAB_PARTITIONS

（10）. DBA_TAB_SUBPARTITIONS

（11）. DBA_IND_PARTITIONS

（12）. DBA_IND_SUBPARTITIONS

（13）. DBA_PART_COL_STATISTICS

（14）. DBA_PART_HISTOGRAMS

（15）. DBA_SUBPART_COL_STATISTICS

（16）. DBA_SUBPART_HISTOGRAMS

4.3 表的統計資訊：

包含表行數，使用的塊數，空的塊數，塊的使用率，行遷移和連結的數量，pctfree，pctused的資料，行的平均大小：

SELECT NUM_ROWS, --表中的記錄數        BLOCKS, --表中資料所佔的資料區塊數        EMPTY_BLOCKS, --表中的空塊數        AVG_SPACE, --資料區塊中平均的使用空間        CHAIN_CNT, --表中行串連和行遷移的數量        AVG_ROW_LEN --每條記錄的平均長度FROM USER_TABLES 

4.4 索引列的統計資訊   

包含索引的深度（B-Tree的層級），索引葉級的塊數量，叢集因子（clustering_factor), 唯一值的個數。

SELECT BLEVEL, --索引的層數    LEAF_BLOCKS, --葉子結點的個數    DISTINCT_KEYS, --唯一值的個數    AVG_LEAF_BLOCKS_PER_KEY, --每個KEY的平均葉塊個數    AVG_DATA_BLOCKS_PER_KEY, --每個KEY的平均資料區塊個數    CLUSTERING_FACTOR --群集因子FROM USER_INDEXES

4.5 列的統計資訊  

包含 唯一的值個數，列最大小值，密度（選擇率），資料分布（長條圖資訊），NUll值個數

SELECT NUM_DISTINCT, --唯一值的個數    LOW_VALUE, --列上的最小值    HIGH_VALUE, --列上的最大值    DENSITY, --選擇率因子（密度）    NUM_NULLS, --空值的個數    NUM_BUCKETS, --長條圖的BUCKET個數    HISTOGRAM --長條圖的類型FROM USER_TAB_COLUMNS

 

五、案例

案例：　查詢表時，解析計劃返回結果集Rows完全不正確，表通過大量的DML後，未進行分析導致統計資訊過久

Step1. 建立測試SQL

Step2. 查看結果集的Cardinality

Step3. 查看錶的統計計劃，最後分析時間過久

Step4. 分析表

BEGIN   DBMS_STATS.gather_table_stats (‘SH‘, ‘SALES‘);END;

Step5. 分析表後統計資訊變更為最新

Step6. 解析計劃Cardinality變更更為準確

 

參考： 一江水 - http://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html

參考： David - http://blog.csdn.net/tianlesoftware/article/details/4668723

參考： Edwardking888 - http://blog.itpub.net/8183550/viewspace-666335/

PLSQL_效能最佳化系列15_Oracle Statistics統計資訊