PL/SQL 包編譯時間hang住的處理

       最近PL/SQL包在編譯時間被hang住，起初以為是所依賴的對象被鎖住。結果出乎意料之外。下面直接看代碼示範。

1、在SQL*Plus下編譯包時被hang住SQL> alter package bo_syn_data_pkg compile;alter package bo_syn_data_pkg compile*ERROR at line 1:ORA-01013: user requested cancel of current operationElapsed: 00:04:52.65                                  -->強行中斷，此時編譯時間已經超過4分鐘SQL> alter package bo_syn_data_pkg compile body;      -->編譯Body時也被hang住>alter package bo_syn_data_pkg compile body*ERROR at line 1:ORA-01013: user requested cancel of current operationElapsed: 00:06:58.05SQL> select * from v$mystat where rownum<2;   SID STATISTIC#      VALUE------ ---------- ----------  1056          0          1Elapsed: 00:00:00.01SQL> select sid,serial#,username from v$session where sid=1056;   SID    SERIAL#     Oracle User------ ---------- ---------------  1056      57643 GOEX_ADMINElapsed: 00:00:00.012、故障分析-->在session 2中監控，沒有任何對象被鎖住SQL> @locks_blockingno rows selected-->監控編譯的session時發現出現library cache pin事件SQL> select sid,seq#,event,p3text,wait_class from v$session_wait where event like 'library cache pin';       SID       SEQ# EVENT                     P3TEXT                                   WAIT_CLASS---------- ---------- ------------------------- ---------------------------------------- --------------------      1056         69 library cache pin         100*mode+namespace                       Concurrency-->來看看library cache pin-->The library cache pin wait event is associated with library cache concurrency. It occurs when the-->session tries to pin an object in the library cache to modify or examine it. The session must acquire a-->pin to make sure that the object is not updated by other sessions at the same time. Oracle posts this-->event when sessions are compiling or parsing PL/SQL procedures and views.-->上面的描述即是需要將對象pin到library cache，且此時這個對象沒有被其他對象更新或持有。對我們的這個包而言，即此時沒有其它對象-->修改該或者其依賴的對象沒有被鎖住。而此時出現該等待事件意味著包或其依賴對象一定被其它session所持有。前面的查詢沒有找到任何-->鎖定對象，看來一定包被其它session所持有。-->查看當前資料庫所有的session的情況-->發現有一個unknow的sessionSQL> @sess_users_active+----------------------------------------------------+| Active User Sessions (All)                         |+----------------------------------------------------+   SID Serial ID    Status    Oracle User     O/S User  O/S PID Session Program              Terminal   Machine------ --------- --------- -------------- ------------ -------- -------------------------- ---------- ---------  1086     59678    ACTIVE     GOEX_ADMIN       oracle   5840   oracle@Dev-DB-04 (J000)      UNKNOWN  Dev-DB-04  1093     54214    ACTIVE     GOEX_ADMIN       oracle   3847   sqlplus@Dev-DB-04 (TNS V1-      pts/1 Dev-DB-04-->查詢該session啟動並執行SQL語句-->經驗證下面的SQL語句正是所編譯包中的一部分SQL> @sess_query_sqlEnter value for sid: 1086old   8:   AND s.sid = &&sidnew   8:   AND s.sid = 1086SQL_TEXT--------------------------------------------------------------------------------SELECT BO_SYN_DATA_PKG.GEN_NEW_RECID AS REC_ID, TO_CHAR( GOATOTIMESTAMP, 'yyyymmdd' ) AS TRADE_DATE, 'DMA' AS TRANS_TYPE, TO_CHAR( GOATOACTIONID ) AS EXEC_KEY,GOATOGROUPREFNUM AS GRP_REF_NUM, GOATOL1ORDERID AS L1_ORDER_ID, GOATOCLORDID ASCLORDER_ID, TO_CHAR( GOATOACTION ) AS ACTION, GOATOACTIONSTATUS AS ACTION_STATUS, GOATOACCNUM AS ACC_NUM, GOATOPLCD AS PL_CD, GOATOTIMESTAMP AS ENTRY_DT, GOATOENDTIMESTAMP AS EXEC_TIMESTAMP, GOATOBUYORSELL AS ORDER_SIDE, LTRIM( GOATOSTOCKCODE, '0' ) AS STOCK_CD, GOATOORDERQTY AS ORDER_QTY, GOATOORDERTYPE AS ORDER_TYPE, GOATOINPUTSOURCE AS ORDER_CHANNEL, GOATOINPUTSOURCE AS INPUTSOURCE, GOATOQTY AS TRADED_QTY, GOATOUNITPRICE AS TRADED_PRICE, GOATOUNITPRICE AS ACTUAL_TRADED_PRICE, GOATOQTY AS TOTAL_TRADED_QTY, GOATOUNSETTLEDAMT AS UNSETTLED_AMT, GOATOALLORNONE AS IS_ALL_OR_NONE, GOATOTIMEINFORCE AS TIME_IN_FORCE, GOATOTRADETYPE AS TRADE_TYPE, GOATOTRADEAEID AS AE_ID, 'N' AS IS_INDIRECT_TRADE, SYSDATE AS SYN_TIME, NULL AS PROCESS_TIME, NULL AS PROCESS_M-->進一步觀察Session的詳細情況-->發現該session的MODULE為DBMS_SCHEDULER，即為一Oracle job，且ACTION與STATE均有描述-->由此推論，編譯包時的Hang住應該是由該job引起的SQL> SELECT username  2        ,command  3        ,status  4        ,osuser  5        ,terminal  6        ,program  7        ,module  8        ,action  9        ,state 10  FROM   v$session 11  WHERE  sid = 1086;USERNAME      COMMAND STATUS   OSUSER     TERMINAL        PROGRAM         MODULE          ACTION               STATE---------- ---------- -------- ---------- --------------- --------------- --------------- -------------------- ----------GOEX_ADMIN          3 ACTIVE   oracle     UNKNOWN         oracle@Dev-DB-0 DBMS_SCHEDULER  STP1_PERFORM_SYNC_DA WAITING                                                          4 (J000)                        TA-->查看job中定義的情況，該job正好調用了該包SQL> select job_name,job_type,enabled,state,job_action from dba_scheduler_jobs where job_name like 'STP1%';JOB_NAME                       JOB_TYPE         ENABL STATE------------------------------ ---------------- ----- ----------JOB_ACTION------------------------------------------------------------------------------------------------------------------STP1_PERFORM_SYNC_DATA         PLSQL_BLOCK      TRUE  RUNNING DECLARE                                                                  err_num NUMBER;                                                                  err_msg VARCHAR2(32767);                                                                BEGIN                                                                  err_num := NULL;                                                                  err_msg := NULL;                                                                  BO_SYN_DATA_PKG.perform_sync_data ( err_num, err_msg );                                                                  COMMIT;                                                                END;-->Author: Robinson Cheng -->Blog  : http://blog.csdn.net/robinson_0612-->下面是該job啟動並執行詳細情況SQL> SELECT job_name  2        ,session_id  3        ,slave_process_id sl_pid  4        ,elapsed_time  5        ,slave_os_process_id sl_os_id  6  FROM   dba_scheduler_running_jobs;JOB_NAME                       SESSION_ID     SL_PID ELAPSED_TIME                   SL_OS_ID------------------------------ ---------- ---------- ------------------------------ ------------STP1_PERFORM_SYNC_DATA               1086         20 +009 00:51:17.79               5840RUN_CHAIN$MY_CHAIN2                                  +075 19:55:03.52RUN_CHAIN$MY_CHAIN1                                  +075 19:57:45.91-->ELAPSED_TIME列， Elapsed time since the Scheduler job was started -->即該job一直處於運行狀態，導致包編譯失敗3、解決-->將job對應的session kill掉SQL> alter system kill session '1086,59678';alter system kill session '1086,59678'*ERROR at line 1:ORA-00031: session marked for killElapsed: 00:01:00.03SQL> SELECT username  2        ,command  3        ,status  4        ,osuser  5        ,terminal  6        ,program  7        ,module  8        ,action  9        ,state 10  FROM   v$session 11  WHERE  sid = 1086;USERNAME      COMMAND STATUS   OSUSER     TERMINAL        PROGRAM         MODULE          ACTION               STATE---------- ---------- -------- ---------- --------------- --------------- --------------- -------------------- ----------GOEX_ADMIN          3 KILLED   oracle     UNKNOWN         oracle@Dev-DB-0 DBMS_SCHEDULER  STP1_PERFORM_SYNC_DA WAITING                                                          4 (J000)                        TA-->再次編譯時間還是被hang住，應該是session還沒有被徹底killSQL> alter package bo_syn_data_pkg compile;alter package bo_syn_data_pkg compile*ERROR at line 1:ORA-01013: user requested cancel of current operation-->再次kill sessionSQL> alter system kill session '1086,59678' immediate;System altered.-->此時包編譯通過SQL> alter package bo_syn_data_pkg compile;Package altered.Elapsed: 00:00:00.32SQL> alter package bo_syn_data_pkg compile body;Package body altered.Elapsed: 00:00:00.18        4、總結-->包編譯時間被hang住，在排除代碼自身編寫出錯的情形下，應考慮是否有對象或依賴對象被其它session所持有-->其次，包的編譯需要將包pin到library cache，會產生library cahce pin等待事件-->對於引起異常的session將其kill之後再編譯     

更多參考

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫