ORACLE 數(shù)據(jù)庫(kù)的統(tǒng)計(jì)數(shù)據(jù)及其生成方式

1、ORACLE數(shù)據(jù)庫(kù)的統(tǒng)計(jì)數(shù)據(jù)及其生成方式ORACLE數(shù)據(jù)庫(kù)的PL/SQL語(yǔ)句執(zhí)行的優(yōu)化器，有基于代價(jià)的優(yōu)化器（CBO ）和基于規(guī)則的優(yōu)化器（RBO ）。RBO的優(yōu)化方式，依賴于一套嚴(yán)格的語(yǔ)法規(guī)則，只要按照規(guī)則寫出的語(yǔ)句，不管數(shù)據(jù)表和索引的內(nèi)容是否 發(fā)生變化，不會(huì)影響PL/SQL語(yǔ)句的執(zhí)行計(jì)劃。CBO自O(shè)RACLE 7版被引入，ORACLE自7版以來采用的許多新技術(shù)都是只基于CBO的，如星型連接排 列查詢，哈希連接查詢，反向索引，索引表，分區(qū)表和并行查詢等。CBO計(jì)算各種可能執(zhí)行計(jì)劃”的 代價(jià)”，即cost，從中選用cost最低的方案，作為實(shí)際運(yùn)行方案。各執(zhí)行計(jì)劃”的cost的計(jì) 算根據(jù)，依賴于

2、數(shù)據(jù)表中數(shù)據(jù)的統(tǒng)計(jì)分布，ORACLE數(shù)據(jù)庫(kù)本身對(duì)該統(tǒng)計(jì)分布是不清楚的，須要分析表和 相關(guān)的索引，才能搜集到CBO所需的數(shù)據(jù)。CBO是ORACLE推薦使用的優(yōu)化方式，要想使用好CBO，使SQL語(yǔ)句發(fā)揮最大效能，必須保證統(tǒng)計(jì)數(shù)據(jù) 的及時(shí)性。統(tǒng)計(jì)信息的生成可以有完全計(jì)算法和抽樣估算法。SQL例句如下：完全計(jì)算法： analyze table abc compute statistics;抽樣估算法（抽樣 20%） : analyze table abc estimate statistics sample 20 percent;對(duì)表作完全計(jì)算所花的時(shí)間相當(dāng)于做全表掃描，抽樣估算法由于采用抽樣，比完全

3、計(jì)算法的生成統(tǒng)計(jì)速度 要快，如果不是要求要有精確數(shù)據(jù)的話，盡量采用抽樣分析法。建議對(duì)表分析采用抽樣估算，對(duì)索引分析 可以采用完全計(jì)算。我們可以采用以下兩種方法，對(duì)數(shù)據(jù)庫(kù)的表和索引及簇表定期分析生成統(tǒng)計(jì)信息，保證應(yīng)用的正常性能。1.在系統(tǒng)設(shè)置定時(shí)任務(wù)，執(zhí)行分析腳本。在數(shù)據(jù)庫(kù)服務(wù)器端，我們以UNIX用戶oracle，運(yùn)行腳本analyze,在analyze中，我們生成待執(zhí)行sql 腳本，并運(yùn)行。（假設(shè)我們要分析scott用戶下的所有表和索引）Analyze腳本內(nèi)容如下：sqlplus scott/tiger EOFset pagesize 5000set heading offSPOOL ANAL

4、YTAB.SQLSELECT ANALYZE TABLE SCOTT.|TABLE_NAME| ESTIMATE STATISTICS SAMPLE 20 PERCENT ; FROMUSER_TABLES ；SPOOL OFFSPOOL ANALYIND.SQLSELECT ANALYZE TABLE SCOTT.|TABLE_NAME| ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALLINDEXES; FROM USER_TABLES ；SPOOL OFFSPOOL ANALYZE.LOGANALYTAB.SQLANALYIND.SQLSPOOL

5、OFFEXIT在UNIX平臺(tái)上crontab加入，以上文件，設(shè)置為每個(gè)月或合適的時(shí)間段運(yùn)行。2.利用ORACLE提供的程序包（PACKAGE）對(duì)相關(guān)的數(shù)據(jù)庫(kù)對(duì)象進(jìn)行分析。有以下的程序包可以對(duì)表，索引，簇表進(jìn)行分析。包中的存儲(chǔ)過程的相關(guān)參數(shù)解釋如下：TYPE 可以是：TABLE，INDEX，CLUSTER 中其一。SCHEMA 為：TABLE， INDEX，CLUSTER 的所有者，NULL 為當(dāng)前用戶。NAME為：相關(guān)對(duì)象的名稱。METHOD 是：ESTIMATE，COMPUTE，DELETE 中其一，當(dāng)選用 ESTIMATE，下面兩項(xiàng)，ESTIMATE_ROWS 和 ESTIMATE_PER

6、CENT 不能同時(shí)為空值。ESTIMATE_ROWS是：估算的抽樣行數(shù)。ESTIMATE_PERCENT是：估算的抽樣百分比。METHOD_OPT 是：有以下選項(xiàng)，F(xiàn)OR TABLE /*只統(tǒng)計(jì)表*/FOR ALL INDEXED COLUMNS SIZE N /* 只統(tǒng)計(jì)有索引的表列 */FOR ALL INDEXES /*只分析統(tǒng)計(jì)相關(guān)索引*/PARTNAME是：指定要分析的分區(qū)名稱。1)DBMS_DDL.ANALYZE_OBJECT(TYPE VARCHAR2,SCHEMA VARCHAR2,NAME VARCHAR2,METHOD VARCHAR2,ESTIMATE_ROWS NUMB

7、ER DEFAULT NULL,ESTIMATE_PERCENT NUMBER DEFAULT NULL,METHOD_OPT VARCHAR2 DEFAULT NULL,PARTNAME VARCHAR2 DEFAULT NULL );該存儲(chǔ)過程可對(duì)特定的表，索引和簇表進(jìn)行分析。例如，對(duì)SCOTT用戶的EMP表，進(jìn)行50%的抽樣分析，參數(shù)如下：DBMS_DDL.ANALYZE_OBJECT(TABLE, SCOTT, EMP, ,ESTIMATE, NULL,50);2)DBMS_UTILITY.ANALYZE_SCHEMA (SCHEMA VARCHAR2,METHOD VARCHAR2,

8、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用于對(duì)某個(gè)用戶擁有的所有TABLE , IND

9、EX和CLUSTER的分析統(tǒng)計(jì)。ANALYZE_DATABASE用于對(duì)整個(gè)數(shù)據(jù)庫(kù)進(jìn)行分析統(tǒng)計(jì)。3) DBMS_STATS是在ORACLE8I中新增的程序包，它使統(tǒng)計(jì)數(shù)據(jù)的生成和處理更加靈活方便，并且可以并 行方式生成統(tǒng)計(jì)數(shù)據(jù)。在程序包中的以下過程分別分析統(tǒng)計(jì)TABLE，INDEX， SCHEMA，DATABASE級(jí) 別的信息。DBMS_STATS.GATHER_TABLE_STATSDBMS_STATS.GATHER_INDEX_STATSDBMS_STATS.GATHER_SCHEMA_STATSDBMS_STATS.GATHER_DATABASE_STATS在這里，我們以數(shù)據(jù)庫(kù)JOB的方式，定時(shí)對(duì)數(shù)據(jù)庫(kù)中SCOTT模式下所有的表和索引進(jìn)行分析：在SQL*PLUS下運(yùn)行：VARIABLE jobno number;BEGINDBMS_JOBS.SUBMIT ( :jobno ,dbms_utility.analyze_schema ( scott”, estim