Analyze table對Oracle效能的提升

觀察analyze table compute statistics 都對什麼物件統計了資訊

analyze table compute statistics = analyze table compute statistics for table for all indexes for all columns;

比analyze table compute statistics for table for all indexes for all indexed columns 分析了更多的資訊

[@more@]

但這裡並不是鼓勵使用 analyze table 的方法進行分析。

SQL> create table t as select * from all_objects; Table created.

SQL> create index t_idx on t(object_id); Index created.

SQL> analyze table t compute statistics for table 2 for all indexes for all indexed columns; Table analyzed.

SQL> select t.num_rows, i.num_rows, c.cnt 2 from (select num_rows from user_tables where table_name = 'T') t, 3 (select num_rows from user_indexes where table_name = 'T' ) i, 4 (select count(distinct column_name) cnt from user_tab_histograms where tab le_name = 'T' ) c 5 / NUM_ROWS NUM_ROWS CNT

---------- ---------- ----------

31213 31213 1 《------在這裡 因為只有1列建立有索引，因此user_tab_histograms 只統計了object_id列的資料分佈，這裡object_id因為唯一，所以是均勻分佈的

SQL> analyze table t delete statistics; Table analyzed.

SQL> select t.num_rows, i.num_rows, c.cnt 2 from (select num_rows from user_tables where table_name = 'T') t, 3 (select num_rows from user_indexes where table_name = 'T' ) i, 4 (select count(distinct column_name) cnt from user_tab_histograms where ta ble_name = 'T' ) c; NUM_ROWS NUM_ROWS CNT

---------- ---------- ---------- 0

SQL> analyze table t compute statistics; Table analyzed.

SQL> select t.num_rows, i.num_rows, c.cnt 2 from (select num_rows from user_tables where table_name = 'T') t, 3 (select num_rows from user_indexes where table_name = 'T' ) i, 4 (select count(distinct column_name) cnt from user_tab_histograms where ta ble_name = 'T' ) c; NUM_ROWS NUM_ROWS CNT

---------- ---------- ----------

31213 31213 13 〈------統計了所有的列，但這些列並不是都用在 where col='X' 上的，因此很多資訊其實都沒有實際意義。

DBMS_STATS 和TABLE的MONITOR結合，可以當表資料量發生10%的變化的時候，自動重新收集統計資訊。 我平常更喜歡使用SIZE SKEWONLY 分析histograms 以下引用 ASKTOM中對“what kind of analyze is best”的回覆 DBMS_STATS in general always gets table statistics (gather_table,gather_schema,gather_database all do). If you use CASCADE=>TRUE, it'll get indexes. METHOD_OPT dictates how histograms are done. Take the analyze command for example: analyze table T compute statistics for table TRUE or you use gather index stats directly for all columns size 1

ORACLE資料庫的PL/SQL語句執行的最佳化器，有基於代價的最佳化器（CBO）和基於規則的最佳化器（RBO）。
RBO的最佳化方式，依賴於一套嚴格的語法規則，只要按照規則寫出的語句，不管資料表和索引的內容是否發生變化，不會影響PL/SQL語句的"執行計劃"。
CBO自ORACLE 7版被引入，ORACLE自7版以來採用的許多新技術都是隻基於CBO的，如星型連線排列查詢，雜湊連線查詢，反向索引，索引表，分割槽表和並行查詢等。CBO計算各種可能"執行計劃"的"代價"，即cost，從中選用cost最低的方案，作為實際執行方案。各"執行計劃"的cost的計算根據，依賴於資料表中資料的統計分佈，ORACLE資料庫本身對該統計分佈是不清楚的，須要分析表和相關的索引，才能蒐集到CBO所需的資料。
CBO是ORACLE推薦使用的最佳化方式，要想使用好CBO，使SQL語句發揮最大效能，必須保證統計資料的及時性。
統計資訊的生成可以有完全計演算法和抽樣估演算法。SQL例句如下：
完全計演算法： analyze table abc compute statistics;
抽樣估演算法(抽樣20%)： analyze table abc estimate statistics sample 20 percent;
對錶作完全計算所花的時間相當於做全表掃描，抽樣估演算法由於採用抽樣，比完全計演算法的生成統計速度要快，如果不是要求要有精確資料的話，儘量採用抽樣分析法。建議對錶分析採用抽樣估算，對索引分析可以採用完全計算。
我們可以採用以下兩種方法，對資料庫的表和索引及簇表定期分析生成統計資訊，保證應用的正常效能。

1. 在系統設定定時任務，執行分析指令碼。
在資料庫伺服器端，我們以UNIX使用者oracle，執行指令碼analyze,在analyze中，我們生成待執行sql指令碼，並執行。（假設我們要分析scott使用者下的所有表和索引）
Analyze指令碼內容如下：
sqlplus scott/tiger << EOF
set pagesize 5000
set heading off
SPOOL ANALYTAB.SQL
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;' FROM USER_TABLES；
SPOOL OFF
SPOOL ANALYIND.SQL
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES；
SPOOL OFF
SPOOL ANALYZE.LOG
@ANALYTAB.SQL
@ANALYIND.SQL
SPOOL OFF
EXIT

2. 利用ORACLE提供的程式包（PACKAGE）對相關的資料庫物件進行分析。
有以下的程式包可以對錶，索引，簇表進行分析。
包中的儲存過程的相關引數解釋如下：
TYPE可以是：TABLE，INDEX，CLUSTER中其一。
SCHEMA為：TABLE，INDEX，CLUSTER的所有者，NULL為當前使用者。
NAME為：相關物件的名稱。
METHOD是：ESTIMATE，COMPUTE，DELETE中其一，當選用ESTIMATE，
下面兩項，ESTIMATE_ROWS和ESTIMATE_PERCENT不能同
時為空值。
ESTIMATE_ROWS是：估算的抽樣行數。
ESTIMATE_PERCENT是：估算的抽樣百分比。
METHOD_OPT是：有以下選項，
FOR TABLE /*只統計表*/
[FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只統計有索引的表列*/
FOR ALL INDEXES /*只分析統計相關索引*/

PARTNAME是：指定要分析的分割槽名稱。

1)

DBMS_DDL.ANALYZE_OBJECT(
TYPE VARCHAR2,
SCHEMA VARCHAR2,
NAME VARCHAR2,
METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL,
PARTNAME VARCHAR2 DEFAULT NULL ) ;
該儲存過程可對特定的表，索引和簇表進行分析。
例如，對SCOTT使用者的EMP表，進行50%的抽樣分析，引數如下：
DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL,50);

2)

DBMS_UTILITY.ANALYZE_SCHEMA (
SCHEMA VARCHAR2,
METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL ) ;
DBMS_UTILITY.ANALYZE_DATABASE (
METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL ) ;

其中，ANALYZE_SCHEMA用於對某個使用者擁有的所有TABLE，INDEX和CLUSTER的分析統計。
ANALYZE_DATABASE用於對整個資料庫進行分析統計。

3) DBMS_STATS是在ORACLE8I中新增的程式包，它使統計資料的生成和處理更加靈活方便，並且可以並行方式生成統計資料。在程式包中的以下過程分別分析統計TABLE，INDEX，SCHEMA，DATABASE級別的資訊。

DBMS_STATS.GATHER_TABLE_STATS
DBMS_STATS.GATHER_INDEX_STATS
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_DATABASE_STATS