pl/sql儲存過程優化一例

問題提出：

etl中有一過程qmsressummt0_test，用來抽取各地xxx應用的資料，目前處於測試階段，暫時存放2個廠區一個月的測試資料，目前該過程執行一次要30分鐘，按此狀況，日後如再抽取其他廠區的資料，預計執行三個小時以上，小組開發人員請求協助優化。

優化前的執行情況

9:31:20 SQL> set serveroutput on;

9:31:25 SQL> exec qmsressummt0_test;

PL/SQL procedure successfully completed

10:00:54 SQL>

執行一次，耗時在30分鐘

調優步驟：

步驟一：調整sql的優化器由rbo變為採用cbo方式

檢視procedure涉及到table的統計資訊，三個表都沒有統計資訊

10:33:35 SQL> select * from user_tables where table_name in ('QMS310T0','QMS311T0',UPPER('qmsressummt0'));

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE

------------------------------ --------------------------- ---------- ------------ ----------

QMS310T0 QMSS

QMS311T0 QMSS

QMSRESSUMMT0 QMSS

Oracle9i，10g的sql優化器優先採用cbo方式，cbo方式下，oracle優化器可以更智慧更合理的選擇執行計劃，從而使sql的執行效率更高，而若相關table和index沒有統計資訊的話，則會轉而採用傳統的rbo方式，在此例子中，任意檢視過程中的某個sql，發現都是採用rbo。

接下來分析該過程涉及到的三個表，收集相關統計資訊。

對這三個表做分析，收集統計資訊

10:00:54 SQL> analyze table qms310t0 compute statistics for table for all indexes;

Table analyzed

10:33:11 SQL> analyze table qms311t0 compute statistics for table for all indexes;

Table analyzed

10:33:21 SQL> analyze table qmsressummt0 compute statistics for table for all indexes;

Table analyzed

10:33:35 SQL>

可以試驗比對一下，採用rbo和cbo的執行效率對比情況

10:41:43 SQL> exec qmsressummt0_test;

PL/SQL procedure successfully completed

11:03:37 SQL>

可以發現，現在的執行時間在20分鐘，在此過程中，效率提高了接近1/3，由此，我們可以看到相同的sql語句，優化器採用cbo比採用rbo效率要提高不少。

步驟二：合理利用索引，進一步提高sql執行效率

分析procedure和table，相關的表需要建立索引，這一步需要根據以往的經驗，建立相關索引（開發人員並沒有考慮新增索引，甚至主鍵都沒有），並抽取sql語句，檢視索引新增前後的執行計劃，最終建立的索引（主鍵索引）如下

SQL> alter table QMS310t0

add constraint PK1_QMS310T0 primary key (BRANCH_NO, CHECK_NO,vend_id,mtrl_no)

Using Index tablespace indx;

Table altered

SQL> alter table QMS311t0

add constraint PK1_QMS311T0 primary key (BRANCH_NO, CHECK_NO, SAMPL_NO,mtrl_no, CHK_NO)

Using Index tablespace indx ;

Table altered

SQL>alter table qmsressummt0 add constraint pk_qmsresssummt0 primary key (yyyymm,branch_no,vend_id,mtrl_no,chk_no) using index tablespace indx;

Table altered

SQL> create index idx_qms310t0_ymd on qms310t0(yymmdd) tablespace indx;

Index created

再次執行過程

11:43:51 SQL> exec qmsressummt0_test;

PL/SQL procedure successfully completed

11:49:34 SQL>

時間大約為6分鐘。

步驟三：合理的改變業務處理方式，亦可提高程式執行效率

對該過程的深入分析可以發現，其中的max 最大值，min 最小值，和count(*) 三個sql語句可以合併成一個sql語句，這樣同樣可以達到減少sql的執行次數和資料塊的io數量，提高效率和降低執行時間的目的。

修改前的sql語句 | 修改後的sql語句

select max(b.item_val)                    | select max(b.item_val),min(b.item_val),Count(*)
      into v_chk_max                      |        into v_chk_max,v_chk_min,v_cnt_01
      from qms310t0 a, qms311t0 b         |    from qms310t0 a, qms311t0 b
     where a.check_no = b.check_no        |    where a.check_no = b.check_no
       and a.branch_no = b.branch_no      |      and a.branch_no = b.branch_no
       and a.mtrl_no = r1.mtrl_no         |      and a.mtrl_no = r1.mtrl_no
       and a.vend_id = r1.vend_id         |      and a.vend_id = r1.vend_id
       and a.branch_no = r1.branch_no     |      and a.branch_no = r1.branch_no
       and b.chk_no = r1.chk_no;          |      and b.chk_no = r1.chk_no;

|

執行的情況：

15:31:13 SQL> exec qmsressummt0_test

PL/SQL procedure successfully completed

15:33:42 SQL>

最終，該過程執行的時間<3分鐘。

至此，程式執行效率提高10倍左右，調整優化結束。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-154887/，如需轉載，請註明出處，否則將追究法律責任。

pl/sql儲存過程優化一例

相關文章