程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle數據庫的表與索引保證性能的方案介紹

Oracle數據庫的表與索引保證性能的方案介紹

編輯:Oracle數據庫基礎

PL/SQL語句是Oracle數據庫執行的優化器,它有基於代價的相關的優化器(CBO)與基於規則的相關的優化器(RBO)。我們大家都知道RBO的相關優化方式,都是依賴於一套嚴格的語法規則。

只要按照規則寫出的語句,不管數據表和索引的內容是否發生變化,不會影響PL/SQL語句的"執行計劃"。

CBO自Oracle 7版被引入,Oracle自7版以來采用的許多新技術都是只基於CBO的,如星型連接排列查詢,哈希連接查詢,反向索引,索引表,分區表和並行查詢等。CBO計算各種可能"執行計劃"的"代價",即cost,從中選用cost最低的方案,作為實際運行方案。

各"執行計劃"的cost的計算根據,依賴於數據表中數據的統計分布,Oracle數據庫本身對該統計分布是不清楚的,須要分析表和相關的索引,才能搜集到CBO所需的數據。

CBO是Oracle推薦使用的優化方式,要想使用好CBO,使SQL語句發揮最大效能,必須保證統計數據的及時性。

統計信息的生成可以有完全計算法和抽樣估算法。SQL例句如下:

完全計算法:

  1. analyze table abc compute statistics; 

抽樣估算法(抽樣20%):

  1. analyze table abc estimate statistics sample 20 percent; 

對表作完全計算所花的時間相當於做全表掃描,抽樣估算法由於采用抽樣,比完全計算法的生成統計速度要快,如果不是要求要有精確數據的話,盡量采用抽樣分析法。建議對表分析采用抽樣估算,對索引分析可以采用完全計算。

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

1. 在系統設置定時任務,執行分析腳本。

在Oracle數據庫服務器端,我們以UNIX用戶Oracle,運行腳本analyze,在analyze中,我們生成待執行sql腳本,並運行。(假設我們要分析scott用戶下的所有表和索引)

Analyze腳本內容如下:

  1. sqlplus scott/tiger << EOF 
  2. set pagesize 5000  
  3. set heading off  
  4. SPOOL ANALYTAB.SQL  
  5. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||

    ' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;' FROM USER_TABLES;  
  6. SPOOL OFF  
  7. SPOOL ANALYIND.SQL  
  8. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||

    ' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES;  
  9. SPOOL OFF  
  10. SPOOL ANALYZE.LOG  
  11. @ANALYTAB.SQL  
  12. @ANALYIND.SQL  
  13. SPOOL OFF  
  14. EXIT  

在UNIX平台上crontab加入,以上文件,設置為每個月或合適的時間段運行。

2. 利用Oracle提供的程序包(PACKAGE)對相關的Oracle數據庫對象進行分析。

有以下的程序包可以對表,索引,簇表進行分析。

包中的存儲過程的相關參數解釋如下:

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. 1)   
  2. DBMS_DDL.ANALYZE_OBJECT(  
  3. TYPE VARCHAR2,  
  4. SCHEMA VARCHAR2,  
  5. NAME VARCHAR2,  
  6. METHOD VARCHAR2,  
  7. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  8. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  9. METHOD_OPT VARCHAR2 DEFAULT NULL,  
  10. PARTNAME VARCHAR2 DEFAULT NULL ) ; 

該存儲過程可對特定的表,索引和簇表進行分析。

例如,對SCOTT用戶的EMP表,進行50%的抽樣分析,參數如下:

  1. DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL,50);   
  2. 2)   
  3. DBMS_UTILITY.ANALYZE_SCHEMA (  
  4. SCHEMA VARCHAR2,  
  5. METHOD VARCHAR2,  
  6. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  7. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  8. METHOD_OPT VARCHAR2 DEFAULT NULL ) ;  
  9. DBMS_UTILITY.ANALYZE_DATABASE (  
  10. METHOD VARCHAR2,  
  11. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  12. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  13. METHOD_OPT VARCHAR2 DEFAULT NULL ) ;   

其中,ANALYZE_SCHEMA用於對某個用戶擁有的所有TABLE,INDEX和CLUSTER的分析統計。

ANALYZE_DATABASE用於對整個Oracle數據庫進行分析統計。

3) DBMS_STATS是在Oracle8I中新增的程序包,它使統計數據的生成和處理更加靈活方便,並且可以並行方式生成統計數據。在程序包中的以下過程分別分析統計TABLE,INDEX,SCHEMA,DATABASE級別的信息。

  1. DBMS_STATS.GATHER_TABLE_STATS  
  2. DBMS_STATS.GATHER_INDEX_STATS  
  3. DBMS_STATS.GATHER_SCHEMA_STATS  
  4. DBMS_STATS.GATHER_DATABASE_STATS  

在這裡,我們以數據庫JOB的方式,定時對Oracle數據庫中SCOTT模式下所有的表和索引進行分析:

在SQL*PLUS下運行:

  1. VARIABLE jobno number;  
  2. BEGIN  
  3. DBMS_JOBS.SUBMIT ( :jobno ,  
  4. ' dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ',  
  5. sysdate, 'sysdate+30');  
  6. commit;  
  7. end;  
  8. /  
  9. Statement processed.  
  10. Print jobno  
  11. JOBNO  
  12. 16 

以上作業,每隔一個月用DBMS_UTILITY.ANALYZE_SCHEMA對用戶SCOTT的所有表,簇表和索引作統計分析

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved