ORACLE的analyze使用簡介

ORACLE資料庫的PL/SQL語句執行的最佳化器，有基於代價的最佳化器（CBO）和基於規則的最佳化器（RBO）。

RBO的最佳化方式，依賴於一套嚴格的文法規則，只要按照規則寫出的語句，不管資料表和索引的內容是否發生變化，不會影響PL/SQL語句的"執行計畫"。

CBO自ORACLE7版被引入，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

在UNIX平台上crontab加入，以上檔案，設定為每個月或合適的時間段運行。

或者將如下指令碼儲存成analyze.sql,然後在sqlplus裡面執行：

set pagesize 5000
set linesize 300
set trims on
set heading off
set feedback off
SPOOL analyTab.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;'

FROM USER_TABLES;
SPOOL OFF
SPOOL analyIdx.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS

FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF
SPOOL analyLog.log
@@analyTab.sql
@@analyIdx.sql
SPOOL OFF

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

在這裡，我們以資料庫JOB的方式，定時對資料庫中SCOTT模式下所有的表和索引進行分析：

在SQL*PLUS下運行：

VARIABLE jobno number;
BEGIN
DBMS_JOBS.SUBMIT ( :jobno ,
" dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ",
sysdate, "sysdate+30");
commit;
end;
/
Statement processed.
Print jobno
JOBNO
------------- 
16

以上作業，每隔一個月用DBMS_UTILITY.ANALYZE_SCHEMA對使用者SCOTT的所有表，簇表和索引作統計分析。