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，定時執行過程，對資料庫物件進行分析統計：

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的所有表，簇表和索引作統計分析。

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

分析前的dba_tables中下列欄位是空，分析後得到結果
select num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       sample_size,
       last_analyzed
  from dba_tables
 where table_name = '表名';

也可以透過該表，查詢哪些表多久沒有分析過了：

select t.table_name, last_analyzed
  from dba_tables t
 where to_char(t.last_analyzed, 'yyyy-MM-dd') <= '2010-01-01'
   and t.owner = 'MS'
 order by t.last_analyzed;

Oracle提供的包中的其他功能請參考文件:<>