Oracle數(shù)據(jù)操作與控制語言的步驟講解

作者：佚名 2010-05-04 17:17:49

我們今天主要向大家介紹的是Oracle數(shù)據(jù)庫中的統(tǒng)計(jì)數(shù)據(jù)與其實(shí)際生成的方式。你如果對其有興趣的話你就可以點(diǎn)擊以下的文章。望你對其會(huì)有所收獲。

以下的文章主要介紹的是Oracle數(shù)據(jù)庫中的PL/SQL語句的相關(guān)執(zhí)行的實(shí)際應(yīng)用優(yōu)化器的介紹，它是一套具有嚴(yán)格的相關(guān)的語法規(guī)則，我們只要按照其規(guī)則寫出相應(yīng)的語句即可，不管數(shù)據(jù)表和索引的內(nèi)容是否發(fā)生變化，不會(huì)影響PL/SQL語句的"執(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ù)，依賴于數(shù)據(jù)表中數(shù)據(jù)的統(tǒng)計(jì)分布，Oracle數(shù)據(jù)庫本身對該統(tǒng)計(jì)分布是不清楚的，須要分析表和相關(guān)的索引，才能搜集到CBO所需的數(shù)據(jù)。

CBO是Oracle推薦使用的優(yōu)化方式，要想使用好CBO，使SQL語句發(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;

對表作完全計(jì)算所花的時(shí)間相當(dāng)于做全表掃描，抽樣估算法由于采用抽樣，比完全計(jì)算法的生成統(tǒng)計(jì)速度要快，如果不是要求要有精確數(shù)據(jù)的話，盡量采用抽樣分析法。建議對表分析采用抽樣估算，對索引分析可以采用完全計(jì)算。

我們可以采用以下兩種方法，對Oracle數(shù)據(jù)庫的表和索引及簇表定期分析生成統(tǒng)計(jì)信息，保證應(yīng)用的正常性能。

1. 在系統(tǒng)設(shè)置定時(shí)任務(wù)，執(zhí)行分析腳本。

在數(shù)據(jù)庫服務(wù)器端，我們以UNIX用戶Oracle，運(yùn)行腳本analyze,在analyze中，我們生成待執(zhí)行sql腳本，并運(yùn)行。（假設(shè)我們要分析scott用戶下的所有表和索引）

Analyze腳本內(nèi)容如下：

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平臺(tái)上crontab加入，以上文件，設(shè)置為每個(gè)月或合適的時(shí)間段運(yùn)行。

2. 利用Oracle提供的程序包（PACKAGE）對相關(guān)的Oracle數(shù)據(jù)庫對象進(jìn)行分析。

有以下的程序包可以對表，索引，簇表進(jìn)行分析。

包中的存儲(chǔ)過程的相關(guān)參數(shù)解釋如下：

TYPE可以是：TABLE，INDEX，CLUSTER中其一。

SCHEMA為：TABLE，INDEX，CLUSTER的所有者，NULL為當(dāng)前用戶。

NAME為：相關(guān)對象的名稱。

METHOD是：ESTIMATE，COMPUTE，DELETE中其一，當(dāng)選用ESTIMATE，

下面兩項(xiàng)，ESTIMATE_ROWS和ESTIMATE_PERCENT不能同

時(shí)為空值。

ESTIMATE_ROWS是：估算的抽樣行數(shù)。

ESTIMATE_PERCENT是：估算的抽樣百分比。

METHOD_OPT是：有以下選項(xiàng)，

FOR TABLE /*只統(tǒng)計(jì)表*/

[FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只統(tǒng)計(jì)有索引的表列*/

FOR ALL INDEXES /*只分析統(tǒng)計(jì)相關(guān)索引*/

PARTNAME是：指定要分析的分區(qū)名稱。

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 ) ;

該存儲(chǔ)過程可對特定的表，索引和簇表進(jìn)行分析。

例如，對SCOTT用戶的EMP表，進(jìn)行50%的抽樣分析，參數(shù)如下：

DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL,50);

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用于對某個(gè)用戶擁有的所有TABLE，INDEX和CLUSTER的分析統(tǒng)計(jì)。

ANALYZE_DATABASE用于對整個(gè)Oracle數(shù)據(jù)庫進(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_STATS  
DBMS_STATS.GATHER_INDEX_STATS  
DBMS_STATS.GATHER_SCHEMA_STATS  
DBMS_STATS.GATHER_DATABASE_STATS

在這里，我們以數(shù)據(jù)庫JOB的方式，定時(shí)對Oracle數(shù)據(jù)庫中SCOTT模式下所有的表和索引進(jìn)行分析：

在SQL*PLUS下運(yùn)行：

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