Oracle數(shù)據(jù)的優(yōu)化器有兩種優(yōu)化方法：基于代價(jià)/成本的優(yōu)化器（CBO）和基于規(guī)則的優(yōu)化器（RBO），系統(tǒng)在對(duì)SQL進(jìn)行優(yōu)化的時(shí)候，使用哪 種優(yōu)化決定于初始化參數(shù)OPTIMIZER_MODE，該參數(shù)值可以設(shè)置為：CHOOSE，RULE，ALL_ROWS，F(xiàn)IRST_ROWS。在 Oracle9i以后的版本中還引入了新的優(yōu)化參數(shù)命令：FIRST_ROWS_1，F(xiàn)IRST_ROWS_10， FIRST_ROWS_100，F(xiàn)IRST_ROWS_1000。（具體的說明將在以后的BLOG文章中介紹）Oracle推薦使用CBO優(yōu)化方式，當(dāng)系 統(tǒng)使用CBO方式優(yōu)化SQL的時(shí)候，要使其執(zhí)行計(jì)劃達(dá)到最優(yōu)化，需要定期執(zhí)行數(shù)據(jù)統(tǒng)計(jì)，必須保證統(tǒng)計(jì)數(shù)據(jù)的及時(shí)性，否則可能得不到預(yù)計(jì)的優(yōu)化效果，或與預(yù) 計(jì)的優(yōu)化效果相差懸殊。

要對(duì)數(shù)據(jù)庫對(duì)象生成統(tǒng)計(jì)信息，可以有以下方法：

完全統(tǒng)計(jì)法：analyze table table_name compute statistics;
抽樣估計(jì)法：analyze table table_name estimate statistics sample 30 percent;

對(duì)表使用抽樣估計(jì)法要比完全統(tǒng)計(jì)法的生成統(tǒng)計(jì)速度要快，但是統(tǒng)計(jì)數(shù)據(jù)可能不夠精確。在開發(fā)過程中，我們可能要涉及很多的表的查詢，而我們?cè)谑褂肅BO的時(shí)候就需要經(jīng)常對(duì)這些表執(zhí)行分析統(tǒng)計(jì)，得到CBO所需要的統(tǒng)計(jì)數(shù)據(jù)。通常有以下幾種方法來收集統(tǒng)計(jì)信息：

1．導(dǎo)出所有需要分析的表的語句腳本，然后執(zhí)行該腳本。

SQL> SPOOL OFF;
SQL> SPOOL C:\ANALYZE_TAB.SQL
SQL> SELECT 'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME||' COMPUTE STATISTICS;' FROM ALL_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM');

然后調(diào)整一下腳本，并執(zhí)行：
SQL>@C:\ANALYZE_TAB.SQL

可以將該腳本放到服務(wù)器端并設(shè)置自動(dòng)執(zhí)行。

2．使用Oracle提供的過程：DBMS_DDL.ANALYZE_OBJECT，該過程可以對(duì)某個(gè)特定用戶的特定表執(zhí)行統(tǒng)計(jì)。例如：

完全統(tǒng)計(jì)：
SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','COMPUTE');

PL/SQL procedure successfully completed
SQL>

50%抽樣統(tǒng)計(jì)
SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','ESTIMATE',NULL,50);

PL/SQL procedure successfully completed

可以使用該過程，生成分析統(tǒng)計(jì)數(shù)據(jù)庫對(duì)象的腳本，并定時(shí)執(zhí)行該腳本。

3．使用Oracle提供的過程DBMS_UTILITY.ANALYZE_SCHEMA該過程執(zhí)行對(duì)某個(gè)特定用戶下的TABLE，INDEX和CLUSTER的分析統(tǒng)計(jì)。如：

完全統(tǒng)計(jì)SCHEMA下的所有對(duì)象：
SQL> execute dbms_utility.analyze_schema('DINYAR','COMPUTE');

PL/SQL procedure successfully completed
Executed in 6.9 seconds

抽樣50%統(tǒng)計(jì)SCHEMA下的所有對(duì)象：
SQL> execute dbms_utility.analyze_schema('DINYAR','ESTIMATE',NULL,50);

PL/SQL procedure successfully completed
Executed in 1.933 seconds

從執(zhí)行的時(shí)間上看，抽樣統(tǒng)計(jì)的時(shí)間要比完全統(tǒng)計(jì)所花費(fèi)的時(shí)間要短，執(zhí)行的更快。

注意： dbms_utility.analyze_schema(SCHEMA,METHOD,ESTIMATE_ROWS,ESTIMATE_PERCENT,METHOD_OPT)中SCHEMA須用戶名大寫，參數(shù)用單引號(hào)''

4．使用Oracle提供的過程DBMS_UTILITY.ANALYZE_DATABASE，該過程可以對(duì)整個(gè)數(shù)據(jù)庫中的對(duì)象進(jìn)行分析統(tǒng)計(jì)。但需要當(dāng)前登陸用戶具備足夠的權(quán)限，否則系統(tǒng)將提示出錯(cuò)。如：

SQL> execute dbms_utility.analyze_database('COMPUTE');

begin dbms_utility.analyze_database('COMPUTE'); end;
ORA-20000: You have insufficient privileges for an object in this database.
ORA-06512: at "SYS.DBMS_UTILITY", line 501
ORA-06512: at line 1

改換有DBA權(quán)限的用戶登陸：
SQL> execute dbms_utility.analyze_database('COMPUTE');

begin dbms_utility.analyze_database('COMPUTE'); end;
ORA-30657: operation not supported on external organized table
ORA-06512: at "SYS.DBMS_DDL", line 179
ORA-06512: at "SYS.DBMS_UTILITY", line 497
ORA-06512: at line 1

從上面的錯(cuò)誤信息可以看出，不支持對(duì)外部表的分析統(tǒng)計(jì)，查看Oracle的解決辦法，Oracle稱不要試圖做這項(xiàng)操作。

DBMS_UTILITY.ANALYZE_DATABASE的抽樣分析統(tǒng)計(jì)和上例中類似。

5．使用Oracle提供的過程：DBMS_STATS，該包中的過程dbms_stats.gather_index_stats，

DBMS_STATS.gather_table_stats，DBMS_STATS.gather_schema_stats，DBMS_STATS.gather_database_stats，DBMS_STATS.gather_system_stats 分別執(zhí)行對(duì)索引、表、某個(gè)schema、數(shù)據(jù)庫、系統(tǒng)的統(tǒng)計(jì)信息。例如：

SQL> execute dbms_stats.gather_table_stats('DINYAR','DINYA_TEST01');

PL/SQL procedure successfully completed
Executed in 0.29 seconds

SQL> execute dbms_stats.gather_schema_stats('DINYAR');
PL/SQL procedure successfully completed
Executed in 7.07 seconds

（該包中還有其他的一些過程，可以對(duì)數(shù)據(jù)庫的對(duì)象進(jìn)行操作，不在這里討論。）

6．定時(shí)執(zhí)行分析統(tǒng)計(jì)，使用DBMS_JOB包，創(chuàng)建一個(gè)JOB，定時(shí)執(zhí)行過程，對(duì)數(shù)據(jù)庫對(duì)象進(jìn)行分析統(tǒng)計(jì)：

1 用DBMS_UTILITY
declare
jobno number;
begin
dbms_job.submit(:jobno,
????????????????? 'dbms_utility.analyze_schema(user,''compute'');',
?????????????????? trunc(sysdate)
????????????????? 'trunc(sysdate)+1' );
commit;
end;

2 用DBMS_DDL
declare
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbms_ddl.analyze_object(''TABLE'',
''schema'', ''user'',
''compute'');',
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;

PL/SQL procedure successfully completed
Executed in 0.581 seconds
job_num
---------
41

這樣就可以在數(shù)據(jù)庫中定時(shí)執(zhí)行數(shù)據(jù)庫對(duì)象統(tǒng)計(jì)信息的收集，保證了使用CBO優(yōu)化器優(yōu)化時(shí)優(yōu)化路徑的準(zhǔn)確性。

Oracle提供的包中的其他功能請(qǐng)參考文檔:<<Oracle9i Supplied PL_SQL Packages and Types Reference >>

Oracle數(shù)據(jù)庫的數(shù)據(jù)統(tǒng)計(jì)(Analyze)