【調優篇基本原理】物件統計資訊
物件統計資訊用來描述資料是如何在資料庫中儲存的，有表統計，列統計，索引統計。先不看分割槽相關的內容，分割槽相關內容在分割槽專題中分析和實驗。
Oracle中有一些表或者檢視的可以檢視這些資訊：
User_tab或all_tab或dba_tab打頭的表相關的，user_tab_col或all_tab_col或dba_tab_col打頭的列相關的，user_ind,all_ind,dba_ind打頭的索引相關的，比如
User_tab_statistics
User_tables
User_tab_col_statistics
User_tab_histograms
User_ind_statistics
User_indexes
簡單看看相關的欄位資訊：
建立一個表：
create table t_test_statistics1 as select * from dba_tables ;
SQL> set linesize 200
SQL> select a.object_type,
2 a.num_rows,
3 a.blocks,
4 a.empty_blocks,
5 a.avg_space,
6 a.chain_cnt,
7 a.avg_row_len,
8 a.last_analyzed,
9 a.stale_stats
10 from user_tab_statistics a
11 where a.table_name = upper('t_test_statistics1');
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN LAST_ANAL STA
------------ ---------- ---------- ------------ ---------- ---------- ----------- --------- ---
TABLE
SQL>
SQL> select a.table_name,a.column_name,a.num_distinct
2 from user_tab_col_statistics a
3 where a.table_name = upper('t_test_statistics1');
no rows selected
SQL>
可以看到新建立的表並沒有相關的統計資訊，比如：
行數num_rows，
塊數blocks，
空塊數empty_block,
平均行的空間avg_space，
行連結數量chain_cnt，
平均行長度avg_row_len,
最後分析時間last_analyzed,
統計資訊是否陳舊stale_stats.
同時也發現並沒有相應列的統計資訊。
接著在表上建立一個索引：
SQL> create index idx_test_stat_name on t_test_statistics1(table_name);
Index created
SQL> select a.blevel,
2 a.leaf_blocks,
3 a.distinct_keys,
4 a.avg_leaf_blocks_per_key,
5 a.avg_data_blocks_per_key,
6 a.avg_cached_blocks,
7 a.avg_cache_hit_ratio,
8 a.clustering_factor,
9 a.num_rows,
10 a.last_analyzed,
11 a.stale_stats
12 from user_ind_statistics a
13 where a.index_name = upper('idx_test_stat_name');
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO CLUSTERING_FACTOR NUM_ROWS LAST_ANAL STA
---------- ----------- ------------- ----------------------- ----------------------- ----------------- ------------------- ----------------- ---------- --------- ---
1 10 2298 1 1 1103 2309 22-JUL-10
SQL>
可以看到，在建立索引的時候便產生了索引相關的統計資訊，包括：
Blevel 索引高度
Leaf_blocks 葉塊數
Distinct_keys 不同的鍵值數
avg_leaf_blocks_per_key 鍵值平均葉塊數
avg_data_blocks_per_key 鍵值平均資料塊數
avg_cached_blocks 平均快取的塊數
avg_cache_hit_ratio 平均快取的命中率
clustering_factor 索引叢集因子
num_rows 行數
last_analyzed 最後分析時間
stale_stats 是否陳舊
可以使用analyze table xx compute statistics來分析表，也可以使用dbms_stats包中的一些方法，通過設定一些引數來具體的分析表。dbms_stats包的一些過程如下：具體可參見oralce官方文件 performance tuning guide.
以gather_table_stats為例：
SQL> begin
2 dbms_stats.gather_table_stats(ownname => 'TEST', --擁有者使用者名稱
3 tabname => upper('t_test_statistics1'),--表名
4 degree => 2, --並行度，也可以設定為預設值
5 --DBMS_STATS.AUTO_DEGREE
6 cascade => true, --是否手機索引統計資訊
7 method_opt => '',
8 estimate_percent => 100, --是否取樣收集，值為取樣百分比
9 --或者取常量：DBMS_STATS.AUTO_SAMPLE_SIZE為0
10 block_sample => true,--採用資料行或者塊取樣，true為塊
11 granularity => 'AUTO', --分割槽粒度級別，取值為
12 --AUTO， ALL， GLOBAL， PARTITION，
13 --SUBPARTITION， GLOBAL AND PARTITION，10g預設AUTO
14 force => false);
15 end;
16
17 /
PL/SQL procedure successfully completed.
此外還有gather_temp是否收集臨時表統計資訊，gather_sys是否收集sys使用者的統計資訊等
或者也可以使用 analyze table 來收集統計資訊：
SQL> analyze table t_test_statistics1 compute statistics;
Table analyzed.
Oracle 10g預設是設定每天的10點自動收集統計資訊，可以在下面的查詢中看到最近一次job的執行情況：
SQL> select max(to_char(a.log_date, 'yyyy-mm-dd hh24:mi:ss')) datetime,
2 a.job_name,
3 a.job_subname,
4 a.status
5 from dba_scheduler_job_run_details a
6 where a.job_name = 'GATHER_STATS_JOB'
7 group by a.job_name, a.job_subname, a.status;
DATETIME JOB_NAME JOB_SUBNAME STATUS
------------------- --------------------- --------------------- ------
2010-07-22 22:00:41 GATHER_STATS_JOB SUCCEEDED
SQL>
由於作業系統時間問題，這裡看到的07-22其實已經過了，也即已經自動分析了，注意到10g這個自動收集統計資訊可能會給一些在晚間業務繁忙的系統帶來效能隱患，比如一些線上交易的系統，應該選取合適的時間點和時間間隔來做收集。
使用dbms_stats.lock_schema_stats來鎖定或解鎖物件統計資訊
同樣以table物件為例子：
鎖定：
SQL> begin
2 dbms_stats.lock_table_stats(ownname => 'TEST',
3 tabname => upper('t_test_statistics1'));
4 end;
5 /
PL/SQL procedure successfully completed
解鎖：
SQL> begin
2 dbms_stats.unlock_table_stats(ownname => 'TEST',
3 tabname => upper('t_test_statistics1'));
4 end;
5 /
PL/SQL procedure successfully completed
當然也可以刪除統計資訊，隨後的一些測試可能會用到，不過一般情況下，不會去delete統計資訊。
例如：
SQL> begin
2 dbms_stats.delete_table_stats(ownname => 'TEST',
3 tabname => upper('t_test_statistics1'));
4 end;
5 /
PL/SQL procedure successfully completed
SQL>
SQL> select a.object_type,
2 a.num_rows,
3 a.blocks,
4 a.empty_blocks,
5 a.avg_space,
6 a.chain_cnt,
7 a.avg_row_len,
8 a.last_analyzed,
9 a.stale_stats
10 from user_tab_statistics a
11 where a.table_name = upper('t_test_statistics1');
OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN LAST_ANALYZED STALE_STATS
------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------- -----------
TABLE
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-668649/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【調優篇基本原理】系統統計資訊
- 【sql調優】系統資訊統計SQL
- 【調優篇基本原理】優化器相關引數配置優化
- 生產sql調優之統計資訊分析SQL
- 資料庫效能調優之始： analyze統計資訊資料庫
- MySQL調優篇 | SQL調優實戰（5）MySql
- SQL調優--表統計資訊未及時更新導致查詢超級慢SQL
- ORACLE SQL調優之統計資訊缺失導致的邏輯讀暴增OracleSQL
- JVM調優-學習篇JVM
- JVM 調優（學習篇）JVM
- MySQL調優篇 | EXPLAIN執行計劃解讀（4）MySqlAI
- SQL優化之統計資訊和索引SQL優化索引
- 【Spark篇】---Spark調優之程式碼調優，資料本地化調優，記憶體調優，SparkShuffle調優，Executor的堆外記憶體調優Spark記憶體
- 【統計資訊】Oracle統計資訊Oracle
- 關閉特定物件統計資訊自動收集物件
- 監視stale statistics(失真的統計資訊)的物件！物件
- 管好統計資訊，開啟SQL優化之門SQL優化
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- spark效能調優指南高階篇Spark
- JVM效能調優與實戰篇JVM
- jvm系列(七):jvm調優-工具篇JVM
- Oracle調優(入門及提高篇)Oracle
- 使用dbms_stats列出沒有統計資訊的物件！物件
- Linux雲端計算架構-系統調優【CPU、IO、網路、核心引數調優】Linux架構
- 效能優化——統計資訊——SQLServer自動更新和自動建立統計資訊選項 (轉載)優化SQLServer
- Oracle優化案例-擴充套件統計資訊（十四）Oracle優化套件
- Linux系統調優Linux
- 馬司系統調優
- ORA-38029: 物件統計資訊已鎖定處理物件
- ORACLE表統計資訊與列統計資訊Oracle
- MySQl 配置InnoDB持久化的優化器統計資訊MySql持久化優化
- 【STATS】Oracle匯入匯出優化器統計資訊Oracle優化
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 瞭解Java物件，簡單聊聊JVM調優分析Java物件JVM
- MySQL調優篇 | 索引知識解讀（2）MySql索引
- Spark效能調優-RDD運算元調優篇（深度好文，面試常問，建議收藏）Spark面試
- 效能調優概述，這是一篇最通俗易懂的效能調優總結
- 【統計資訊】全面檢視錶所有統計資訊SQLSQL