Oracle 統計資訊

Oracle最佳化器的最佳化分為兩類，即基於規則的最佳化方式（Rule-Based Optimization,簡稱RBO）和基於代價的最佳化方式（Cost-Based Optimization,簡稱CBO）

1. RBO方式：最佳化器在分析SQL語句時，根據資料庫中表和索引等定義資訊，遵循的是Oracle內部預定的一些規則。比如我們常見的：當一個where 子句中的一列有索引時去走索引而不走全表掃描。
2. CBO方式：它是看語句的代價（cost）。基於代價的查詢，資料庫根據收集的表和索引的資料統計資訊（統計資訊透過analyze 命令或使用dbms_stats包來蒐集）綜合來決定選取一個資料庫認為最優的執行計劃（實際上不一定是最優）。統計資訊給出表的大小、有多少行、每行的長度等資訊。
注意：這些統計資訊起初在庫內是沒有的，是根據analyze命令或者dbms_stats包來定期蒐集後才出現的，所以很多的時候過期統計資訊會令最佳化器做出一個錯誤的執行計劃，因此我們需要及時的更新這些資訊。為了使用基於成本的最佳化器（CBO），需要經常的執行analyze或dbms_stats命令，以增加資料庫中的物件統計資訊（object statistics）的準確性。
在oracle8以後的版本，oracle強烈推薦使用CBO方式。
1. 如何檢視物件統計資訊(object statistics)
對CBO模式，物件統計資訊至關重要。如何檢視物件統計資訊(object statistics)
Oracle中關於表的統計資訊是在資料字典中的，可以用以下SQL查詢到，eg：
SELECT table_name,num_rows, blocks, empty_blocks AS empty, avg_space, chain_cnt, avg_row_len
FROM dba_tables
WHERE owner ='schema'
AND table_name ='xxxxx'
在輸出的結果中檢視num_rows的值與以下sql的輸出結果進行比較，如果差別較大就需要進行更新。
我們下SQL驗證一下：
select count(*) from schema.table_name;
在差別較大的情況下，如果某個View用到此Table，且系統使用CBO的方式，則可能導致Oracle的optimizer給出效率低下的執行計劃。
此時可以用ANALYZE去重新統計xxxxx這個表，可以下SQL：
ANALYZE TABLE schema.xxxxx COMPUTE STATISTICS;

2. 併發請求： 統計資料收集模式(FNDGSCST) / Gather Schema Statistics
Oracle ERP中有幾個與Gather有關的標準Request：
Gather All Column Statistics –FND_STATS.GATHER_ALL_COLUMN_STATS()
Gather Column Statistics –FND_STATS.GATHER_COLUMN_STATS()
Gather Schema Statistics –FND_STATS.GATHER_SCHEMA_STATS()
Gather Table Statistics –FND_STATS.GATHER_TABLE_STATS()
檢視FND_STATS 這個Package的寫法，其實它就是在呼叫Oracle DB中Standard的Package dbms_stats 中的某些Function。
Oracle DB中常用的Gather有以下一些，DBA也可以直接在Database級別上定期Run這些Function，以便能讓Oracle統計到最新的資料庫狀況：
dbms_stats.gather_database_stats();
dbms_stats.gather_schema_stats();
dbms_stats.gather_table_stats();
dbms_stats.gather_index_stats();

Oracle CBO需要系統定期分析統計表/索引。 只有這樣CBO才能使用正確的SQL訪問路徑，提高查詢效率。 因此在Instance Level的optimizer_mode = choose ，定期執行ANALYZE 或dbms_stats是非常重要的，尤其是當上次統計後，資料量已發生較大變化之後。
注意：統計操作是很耗資源的動作，要在系統Loading小的時候進行。