Hang processing during PL/SQL package Compilation

Recently, PL/SQL packages were hang during compilation and initially thought they were dependent on the object being locked. Unexpected results. The following shows the code demonstration.

1. When compiling the package in SQL * Plus, the 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 --> forced interruption. The compilation time exceeds 4 minutes. SQL> alter package bo_syn_data_pkg compile body; --> hang also occurs when compiling body> 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 1 elapsed: 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. Fault Analysis --> Monitoring in session 2, no objects are locked SQL> @ locks_blockingno rows selected --> library cache pin found during monitoring compiled sessions Event SQL> select Sid, seq #, event, p3text, wait_class from V $ session_wait where event like 'library cache pin '; sid seq # event p3text wait_class ---------- --------------------------- certificate ------------------ 1056 69 library cache pin 100 * mode + namespace concurrency --> let's take a look at the library cache pin wait event is associated library cache concu Rrency. 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. --> the above description indicates that the object needs to be pin to the library cache, and this object is not updated or held by other objects at this time. For our package, there is no other object --> the modified or dependent object is not locked. In this case, the waiting event means that the package or its dependent object must be held by other sessions. The previous query did not find any --> lock object. It seems that it must be held by other sessions. --> View all sessions of the current database --> An unknow sessionsql> @ sess_users_active + -------------------------------------------------------- + | active user sessions (all) | + kernel + Sid serial ID status Oracle user o/s PID session program terminal machine ------ ----------- ------------------ ------------ -------- upper ---------- --------- 1086 active goex_admin Oracle 59678 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 --> query the SQL statement that runs the session --> The following SQL statement is verified to be exactly the compiled package in part of the SQL> @ sess_query_sqlenter value for Sid: 1086old 8: and S. SID = & sidnew 8: and S. SID = same region as rec_id, to_char (goatotimestamp, 'yyyymmdd') as trade_date, 'dma 'As trans_type, to_char (goatoactionid) as exec_key, region as grp_ref_num, region as l1_order_id, export asclorder_id, to_char (goatoaction) as action, goatoactionstatus as action_status, goatoaccnum as acc_num, goatoplcd as pl_cd, region as entry_dt, region as region, region as order_side, ltrim (goatostockcode, '0') as stock_cd, region as order_qty, goatoordertype as order_type, region as order_channel, region as inputsource, goatoqty as region, goatounitprice as traded_price, goatounitprice as region, goatoqty as region, same as execution, goatoallornone as execution, 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 --> further observe the details of the session --> find that the module of the session is DBMS_Scheduler, that is, an oracle job, and the action and state are described --> inferred from this, the hang during package compilation should be caused by the 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 wait waiting 4 (j000) ta --> view the information defined in the job. The job calls the SQL statement of the package> select job_name, job_type, enabled, state, job_action from dba_scheduler_jobs where job_name like 'stp1% '; job_name job_type enabl state unknown ----- ---------- 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; struct (err_num, err_msg ); commit; end; --> author: Robinson Cheng --> blog: http://blog.csdn.net/robinson_0612 --> The following is the details of the job running 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 region; job_name session_id sl_pid elapsed_time sl_ OS _id region ---------- region ------------ stp1_1_m_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 column, elapsed time since the scheduler job was started --> that is, the job is always running, cause package compilation failure 3. Solution --> kill the session corresponding to the job SQL> alter system kill session '201801078 '; alter system kill session '2017 78 '* 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 wait waitinging 4 (j000) ta --> re-compilation or hang, should be session has not been thoroughly 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 again> alter system kill session '2017 78 'immediate; System altered. --> in this case, the package is compiled using 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. Conclusion --> the package is hang during compilation. In the case of code writing errors excluded, check whether any object or dependent object is held by other sessions. --> Secondly, to compile a package, pin the package to the library cache, which generates a library cahce pin wait event --> kill the abnormal session before compiling

More references

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan