Oracle資料的優化器有兩種優化方法：

Oracle資料的優化器有兩種優化方法：基於代價/成本的優化器（CBO）和基於規則的優化器（RBO），系統在對SQL進行優化的時候，使用哪種優化決定於初始化引數OPTIMIZER_MODE，該引數值可以設定為：CHOOSE，RULE，ALL_ROWS，FIRST_ROWS。在Oracle9i 以後的版本中還引入了新的優化引數命令：FIRST_ROWS_1，FIRST_ROWS_10， FIRST_ROWS_100，FIRST_ROWS_1000。（具體的說明將在以後的BLOG文章中介紹）Oracle推薦使用CBO優化方式，當系統使用CBO方式優化SQL的時候，要使其執行計劃達到最優化，需要定期執行資料統計，必須保證統計資料的及時性，否則可能得不到預計的優化效果，或與預計的優化效果相差懸殊。

　　要對資料庫物件生成統計資訊，可以有以下方法：

　　完全統計法：analyze table table_name compute statistics;

　　抽樣估計法：analyze table table_name estimate statistics sample 30 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的所有表，簇表和索引作統計分析

Oracle中的CBO方式
　　Oracle的優化器有兩種優化方式,即基於規則的優化方式(Rule-Based Optimization，簡稱為RBO)和基於代價的優化方式(Cost-Based Optimization，簡稱為CBO)，在Oracle8及以後的版本,Oracle強列推薦用CBO的方式
　　RBO方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。
　　CBO方式：它是看語句的代價(Cost),這裡的代價主要指Cpu和記憶體。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做analyze後才出現的，很多的時候過期統計資訊會令優化器做出一個錯誤的執行計劃,因些應及時更新這些資訊。
　　Examda提示：主索引不一定就是優的，比如一個表只有兩行資料，一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時全表掃描(full table scan)是最好
　　優化模式包括Rule、Choose、First rows、All rows四種方式：
　　Rule：基於規則的方式。
　　Choose：預設的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式。
　　First Rows：它與Choose方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。
　　All Rows:也就是我們所說的Cost的方式，當一個表有統計資訊時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統計資訊則走RBO的方式。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-660701/，如需轉載，請註明出處，否則將追究法律責任。