PL/SQL 包编译时hang住的处理

PL/SQL 包编译时hang住的处理

转自：http://blog.csdn.net/leshami/article/details/7616923

最近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 operation  
 
Elapsed: 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 operation  
 
Elapsed: 00:06:58.05  
 
SQL> select * from v$mystat where rownum<2;  
 
    SID STATISTIC#          VALUE  
------ ---------- ----------   
  1056                  0                  1  
 
Elapsed: 00:00:00.01  

SQL> select sid,serial#,username from v$session where sid=1056;  
 
    SID      SERIAL#        Oracle User  
------ ---------- ---------------   
  1056          57643 GOEX_ADMIN  
 
Elapsed: 00:00:00.01  
 
2、故障分析  
-->在session 2中监控，没有任何对象被锁住   
SQL> @locks_blocking   
no 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的session   
SQL> @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_sql  
Enter value for sid: 1086  
old    8:    AND s.sid = &&sid  
new    8:    AND s.sid = 1086  
 
SQL_TEXT  
--------------------------------------------------------------------------------   
SELECT BO_SYN_DATA_PKG.GEN_NEW_RECID AS REC_ID, TO_CHAR( GOATOTIMESTAMP, 'yyyymm  
dd' ) AS TRADE_DATE, 'DMA' AS TRANS_TYPE, TO_CHAR( GOATOACTIONID ) AS EXEC_KEY,  
GOATOGROUPREFNUM AS GRP_REF_NUM, GOATOL1ORDERID AS L1_ORDER_ID, GOATOCLORDID AS  
CLORDER_ID, TO_CHAR( GOATOACTION ) AS ACTION, GOATOACTIONSTATUS AS ACTION_STATUS  
, GOATOACCNUM AS ACC_NUM, GOATOPLCD AS PL_CD, GOATOTIMESTAMP AS ENTRY_DT, GOATOE  
NDTIMESTAMP AS EXEC_TIMESTAMP, GOATOBUYORSELL AS ORDER_SIDE, LTRIM( GOATOSTOCKCO  
DE, '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_PRI  
CE, GOATOQTY AS TOTAL_TRADED_QTY, GOATOUNSETTLEDAMT AS UNSETTLED_AMT, GOATOALLOR  
NONE AS IS_ALL_OR_NONE, GOATOTIMEINFORCE AS TIME_IN_FORCE, GOATOTRADETYPE AS TRA  
DE_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;   
 
-->下面是该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                            5840  
RUN_CHAIN$MY_CHAIN2                                                                  075 19:55:03.52  
RUN_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 kill  
 
Elapsed: 00:01:00.03  
 
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 KILLED    oracle        UNKNOWN                oracle@Dev-DB-0 DBMS_SCHEDULER  STP1_PERFORM_SYNC_DA WAITING  
                                                                                                                  4 (J000)                                              TA  
 
-->再次编译时还是被hang住，应该是session还没有被彻底kill   
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 operation  
 
-->再次kill session   
SQL> alter system kill session '1086,59678' immediate;  
 
System altered.  
 
-->此时包编译通过   
SQL> alter package bo_syn_data_pkg compile;  
 
Package altered.  
 
Elapsed: 00:00:00.32  
 
SQL> 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之后再编译  

-->Author: Robinson Cheng     
-->Blog  : http://blog.csdn.net/robinson_0612