SQL*Net more data from dblink引起library cache pin

今天論壇中發現一個問題在進行編譯或者刪除儲存過程的時候一直卡住，

當然這個很可能是LIBRARY CACHE PIN引起的。概念如下：

An Oracle instance has a library cache that contains the description of  
different types of objects e.g. cursors, indexes, tables, views, procedures,  
... Those objects cannot be changed when they are used. They are locked by a  
mechanism based on library locks and pins. A session that need to use an object  
will first acquire a library lock in a certain mode (null, shared or exclusive)  
on the object, in order to prevent other sessions from accessing the same  
object (e.g. exclusive lock when recompiling a package or view) or to maintain  
the object definition for a long time. Locking an object is sometimes referred
as the job to locate it in the library cache and lock it in a certain mode.
If the session wants to modify or examine the object, it must acquire after  
the lock also a pin in a certain mode (again null, shared or exclusive).  
透過語句

select distinct decode(lob.kglobtyp,
                       0,
                       'NEXT OBJECT',
                       1,
                       'INDEX',
                       2,
                       'TABLE',
                       3,
                       'CLUSTER',
                       4,
                       'VIEW',
                       5,
                       'SYNONYM',
                       6,
                       'SEQUENCE',
                       7,
                       'PROCEDURE',
                       8,
                       'FUNCTION',
                       9,
                       'PACKAGE',
                       11,
                       'PACKAGE BODY',
                       12,
                       'TRIGGER',
                       13,
                       'TYPE',
                       14,
                       'TYPE BODY',
                       19,
                       'TABLE PARTITION',
                       20,
                       'INDEX PARTITION',
                       21,
                       'LOB',
                       22,
                       'LIBRARY',
                       23,
                       'DIRECTORY',
                       24,
                       'QUEUE',
                       28,
                       'JAVA SOURCE',
                       29,
                       'JAVA CLASS',
                       30,
                       'JAVA RESOURCE',
                       32,
                       'INDEXTYPE',
                       33,
                       'OPERATOR',
                       34,
                       'TABLE SUBPARTITION',
                       35,
                       'INDEX SUBPARTITION',
                       40,
                       'LOB PARTITION',
                       41,
                       'LOB SUBPARTITION',
                       42,
                       'MATERIALIZED VIEW',
                       43,
                       'DIMENSION',
                       44,
                       'CONTEXT',
                       46,
                       'RULE SET',
                       47,
                       'RESOURCE PLAN',
                       48,
                       'CONSUMER GROUP',
                       51,
                       'SUBSCRIPTION',
                       52,
                       'LOCATION',
                       55,
                       'XML SCHEMA',
                       56,
                       'JAVA DATA',
                       57,
                       'SECURITY PROFILE',
                       59,
                       'RULE',
                       62,
                       'EVALUATION CONTEXT',
                       'UNDEFINED') object_type,
                lob.KGLNAOBJ object_name,
                pn.KGLPNMOD lock_mode_held,
                pn.KGLPNREQ lock_mode_requested,
                ses.sid,
                ses.serial#,
                ses.username,
                ses.process,
                vp.spid
  FROM x$kglpn        pn,
       v$session      ses,
       x$kglob        lob,
       v$session_wait vsw,
       v$process      vp
 WHERE pn.KGLPNUSE = ses.saddr
   and pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and ses.PADDR = vp.ADDR
   and vsw.event = 'library cache pin'
 order by lock_mode_held desc;
 檢視PIN的物件，確實有一個阻塞源。原來是在跑JOB，進一步分析發現了

SQL*Net more data from dblink

這個應該是網路問題，在INSERT到遠端庫的時候引起的，由於在JOB中有這個過程，所以JOB遲遲不能完成，同時儲存過程也不能完成。

下面是METALINK的原文

Insert Into Remote Table Using DBLINK Over VPN Tunnel Hangs on Large Number of Rows [ID 742535.1]

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

  修改時間 19-MAR-2010     型別 PROBLEM     狀態 PUBLISHED   

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References



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



Applies to:
Oracle Net Services - Version: 9.2.0.1.0 to 11.1.0.7.0
Information in this document applies to any platform.
Checked for relevance on 19-MAR-2010.
Symptoms
Suddenly an INSERT (as) SELECT FROM DBLINK hangs with source database waiting on "SQL*Net more data from dblink" wait event and the remote (target) shadow process disappearing.


SQL> INSERT INTO TABLEX (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 281);
280 rows created.

SQL> INSERT INTO TABLEX (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 282);
This hangs indefinitely with source in "SQL*Net more data from dblink" wait event.

Similarly, create table as select over the DBLINK also hangs:


SQL> CREATE TABLE TEST1 AS (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 32767);

Table created.
---------------

SQL> CREATE TABLE TEST1 AS (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 32768);

-- This also hangs indefinitely with source in "SQL*Net more data from dblink" wait event.

A PL/SQL doing an explicit cursor just fetching (not inserting) fails after 20 or so rows.

The only thing that runs to completion is plain SELECT from Sqlplus.


Changes
Database Links to databases on Local Area Network (LAN) do not exhibit this problem.  This issue is limited to a database link where the target is a remote database accessed via a VPN Tunnel using default port 1521.
Cause
Problem was isolated to use of port 1521 over a VPN Tunnel that utilizes Cisco 5400/5500 Series
Adaptive Security Appliances (ASA) where the Cisco SQLnet fixup protocol/Sql Inspector was
enabled. However, on port 1522 where this Sql packet Inspection was not enabled, the problem did
not reproduce. Note: The Cisco 5400/5500 Series Adaptive Security Appliances (ASA) have this SQLnet fixup protocol/Sql Inspection enabled by default for port 1521.