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;

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

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