1. 首页
  2. IT资讯

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-680780/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183213.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code