Oracle資料庫的資料統計(Analyze)

Oracle資料庫的資料統計(Analyze)

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;
對錶使用抽樣估計法要比完全統計法的產生統計速度要快，但是統計資料可能不夠精確。在開發過程中，我們可能要涉及很多的表的查詢，而我們在使用CBO的時候就需要經常對這些表執行分析統計，得到CBO所需要的統計資料。通常有以下幾種方法來收集統計資訊：
1．匯出所有需要分析的表的語句指令碼，然後執行該指令碼。
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');
然後調整一下指令碼，並執行：
SQL>@C:/ANALYZE_TAB.SQL
可以將該指令碼放到伺服器端並設定自動執行。

2．使用Oracle提供的過程：DBMS_DDL.ANALYZE_OBJECT，該過程可以對某個特定使用者的特定表執行統計。例如：
完全統計：
SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','COMPUTE');

PL/SQL procedure successfully completed

SQL>

50%抽樣統計

SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','ESTIMATE',NULL,50);

PL/SQL procedure successfully completed

SQL>

可以使用該過程，產生分析統計資料庫物件的指令碼，並定時執行該指令碼。

3．使用Oracle提供的過程DBMS_UTILITY.ANALYZE_SCHEMA該過程執行對某個特定使用者下的TABLE，INDEX和CLUSTER的分析統計。如：

完全統計SCHEMA下的所有對象：

SQL> execute dbms_utility.analyze_schema('DINYAR','COMPUTE');

PL/SQL procedure successfully completed

Executed in 6.9 seconds
SQL>

抽樣50%統計SCHEMA下的所有對象：

SQL> execute dbms_utility.analyze_schema('DINYAR','ESTIMATE',NULL,50);

PL/SQL procedure successfully completed

Executed in 1.933 seconds

SQL>

從執行的時間上看，抽樣統計的時間要比完全統計所花費的時間要短，執行的更快。

4．使用Oracle提供的過程DBMS_UTILITY.ANALYZE_DATABASE，該過程可以對整個資料庫中的對象進行分析統計。但需要當前登陸使用者具備足夠的許可權，否則系統將提示出錯。如：

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

SQL>

改換有DBA許可權的使用者登陸：
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

SQL>

從上面的錯誤資訊可以看出，不支援對外部表格的分析統計，查看Oracle的解決辦法，Oracle稱不要試圖做這項操作。
DBMS_UTILITY.ANALYZE_DATABASE的抽樣分析統計和上例中類似。

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分別執行對索引、表、某個schema、資料庫、系統的統計資訊。例如：

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
SQL>

（該包中還有其他的一些過程，可以對資料庫的對象進行操作，不在這裡討論。）

6．定時執行分析統計，使用DBMS_JOB包，建立一個JOB，定時執行過程，對資料庫物件進行分析統計：

PL/SQL procedure successfully completed

Executed in 0.581 seconds

job_num
---------
41

SQL>

這樣就可以在資料庫中定時執行資料庫物件統計資訊的收集，保證了使用CBO最佳化器最佳化時最佳化路徑的準確性。

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