PL/SQL語句是Oracle數據庫執行的優化器，它有基于代價的相關的優化器（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; 

 

對表作完全計算所花的時間相當于做全表掃描，抽樣估算法由于采用抽樣，比完全計算法的生成統計速度要快，如果不是要求要有精確數據的話，盡量采用抽樣分析法。建議對表分析采用抽樣估算，對索引分析可以采用完全計算。

 

我們可以采用以下兩種方法，對Oracle數據庫的表和索引及簇表定期分析生成統計信息，保證應用的正常性能。

 

1. 在系統設置定時任務，執行分析腳本。

 

在Oracle數據庫服務器端，我們以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加入，以上文件，設置為每個月或合適的時間段運行。

2. 利用Oracle提供的程序包（PACKAGE）對相關的Oracle數據庫對象進行分析。

有以下的程序包可以對表，索引，簇表進行分析。

 

包中的存儲過程的相關參數解釋如下：

 

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用于對整個Oracle數據庫進行分析統計。

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的方式，定時對Oracle數據庫中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的所有表，簇表和索引作統計分析

文章出自:http://database.51cto.com/art/200703/43583.htm

【編輯推薦】

1. Oracle數據庫外部的身份認證的正確方法
2. Oracle監聽器出現的6種連接問題及其解決方法
3. 對Oracle數據庫的外部身份認證的2種方案
4. Oracle數據庫表的大小修改5方案
5. Oracle數據庫文件移動的實現