关于shared pool的深入探讨(五)

关于shared pool的深入探讨(五)

原文链接:

http://www.eygle.com/internal/shared_pool-5.htm

Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin. Lock比pin具有更高的级别.

Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定. 锁定主要有三种模式: Null,share,Exclusive. 在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定. 在修改对象时,需要获得Exclusive(排他)锁定.

在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象. 同样pin有三种模式,Null,shared和exclusive. 只读模式时获得共享pin,修改模式获得排他pin.

通常我们访问、执行过程、Package时获得的都是共享pin,如果排他pin被持有,那么数据库此时就要产生等待. 在很多statspack的report中,我们可能看到以下等待事件:

Top 5 Wait Events ~~~~~~~~~~~~~~~~~                                             Wait     % Total Event                                               Waits  Time (cs)   Wt Time -------------------------------------------- ------------ ------------ ------- library cache lock                                 75,884    1,409,500   48.44 latch free                                     34,297,906    1,205,636   41.43 library cache pin                                     563      142,491    4.90 db file scattered read                            146,283       75,871    2.61 enqueue                                             2,211       13,003     .45           -------------------------------------------------------------

这里的library cache lock和library cache pin都是我们关心的.接下来我们就研究一下这几个等待事件.         

(一).LIBRARY CACHE PIN等待事件

Oracle文档上这样介绍这个等待事件: "library cache pin" 是用来管理library cache的并发访问的,pin一个object会引起相应的heap被 载入内存中(如果此前没有被加载),Pins可以在三个模式下获得:NULL,SHARE,EXCLUSIVE,可以认为pin是一种特定 形式的锁. 当Library Cache Pin等待事件出现时,通常说明该Pin被其他用户已非兼容模式持有.

"library cache pin"的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.         "library cache pin"的参数如下,有用的主要是P1和P2:                 P1 - KGL Handle address.                 P2 - Pin address                 P3 - Encoded Mode & Namespace "LIBRARY CACHE PIN"通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的, 如安装应用程序,升级,安装补丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效, 可以通过object的"LAST_DDL"观察这些变化. 当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin 到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新 编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.

下面让我们通过一个例子来模拟及解释这个等待:

1.创建测试用存储过程

[oracle@jumper udump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 14:16:57 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup ORACLE instance started.

Total System Global Area   47256168 bytes Fixed Size                   451176 bytes Variable Size              29360128 bytes Database Buffers           16777216 bytes Redo Buffers                 667648 bytes Database mounted. Database opened. SQL> create or replace PROCEDURE pining   2  IS   3  BEGIN   4          NULL;   5  END;   6  /

Procedure created.

SQL> SQL> create or replace procedure calling   2  is   3  begin   4          pining;   5          dbms_lock.sleep(3000);   6  end;   7  /

Procedure created.

SQL>

2.模拟 首先执行calling过程,在calling过程中调用pining过程 此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待 直到calling执行完毕.

session 1:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:13:43 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> exec calling

此时calling开始执行

session 2:

[oracle@jumper udump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:14:16 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> grant execute on pining to eygle;

此时session 2挂起

ok,我们开始我们的研究:

从v$session_wait入手,我们可以得到哪些session正在经历library cache pin的等待

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state   2  from v$session_wait where event like 'library%';

 SID       SEQ# EVENT                       P1 P1RAW            P2 P2RAW            P3  WAIT_TIME SECONDS_IN_WAIT STATE ---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- -------------------    8        268 library cache pin   1389785868 52D6730C 1387439312 52B2A4D0        301          0               2 WAITING

等待3秒就超时,seq#会发生变化

SQL>

 SID       SEQ# EVENT                       P1 P1RAW            P2 P2RAW            P3  WAIT_TIME SECONDS_IN_WAIT STATE ---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- -------------------    8        269 library cache pin   1389785868 52D6730C 1387439312 52B2A4D0        301          0               2 WAITING

SQL>

 SID       SEQ# EVENT                       P1 P1RAW            P2 P2RAW            P3  WAIT_TIME SECONDS_IN_WAIT STATE ---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- -------------------    8        270 library cache pin   1389785868 52D6730C 1387439312 52B2A4D0        301          0               0 WAITING

在这个输出中,P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示

我们看到,library cache pin等待的对象的handle地址为:52D6730C 通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:

Note: X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject

col KGLNAOWN for a10 col KGLNAOBJ for a20 select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from X$KGLOB where KGLHDADR ='52D6730C' /

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ -------- -------- -------- ---------- -------------------- ---------- -------- 404F9FF0 52D6730C 52D6730C SYS        PINING               2300250318 52D65BA4

这里KGLNAHSH代表该对象的Hash Value

由此我们知道,在PINING对象上正经历library cache pin的等待.

然后我们引入另外一个内部视图X$KGLPN:

Note:X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL, b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ from v$session a,x$kglpn b where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0 /

  SID USERNAME    PROGRAM                                  ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ ----- ----------- ---------------------------------------- -------- -------- -------- -------- -------- -------- ---------- ----------    13 SYS         sqlplus@jumper.hurray.com.cn (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8          2          0

通过联合v$session,可以获得当前持有该handle的用户信息. 对于我们的测试sid=13的用户正持有该handle

那么这个用户正在等什么呢?

SQL> select * from v$session_wait where sid=13;

       SID       SEQ# EVENT               P1TEXT            P1 P1RAW    P2TEXT          P2 P2RAW    P3TEXT          P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT STATE ---------- ---------- ------------------- --------- ---------- -------- ------- ---------- -------- ------- ---------- -------- ---------- --------------- -------         13         25 PL/SQL lock timer   duration      120000 0001D4C0                  0 00                        0 00                0            1200 WAITING

Ok,这个用户正在等待一次PL/SQL lock timer计时.

得到了sid,我们就可以通过v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段关联v$sqltext,v$sqlarea等视图获得当前session正在执行的操作.

SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777';

SQL_TEXT -------------------------------------------------------------------------------- BEGIN calling; END;

这里我们得到这个用户正在执行calling这个存储过程,接下来的工作就应该去检查calling在作什么了.

我们这个calling作的工作是dbms_lock.sleep(3000) 也就是PL/SQL lock timer正在等待的原因

至此就找到了Library Cache Pin的原因.

简化一下以上查询:

1.获得Library Cache Pin等待的对象

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj   FROM x$kglob  WHERE kglhdadr IN (SELECT p1raw                       FROM v$session_wait                      WHERE event LIKE 'library%') /

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ -------- -------- -------- ---------- -------------------- ---------- -------- 404F2178 52D6730C 52D6730C SYS        PINING               2300250318 52D65BA4

2.获得持有等待对象的session信息

SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,        b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq   FROM v$session a, x$kglpn b  WHERE a.saddr = b.kglpnuse    AND b.kglpnmod <> 0    AND b.kglpnhdl IN (SELECT p1raw                         FROM v$session_wait                        WHERE event LIKE 'library%') / SQL>

       SID USERNAME   PROGRAM                                          ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ ---------- ---------- ------------------------------------------------ -------- -------- -------- -------- -------- -------- ---------- ----------         13 SYS        sqlplus@jumper.hurray.com.cn (TNS V1-V3)         404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8          2          0

3.获得持有对象用户执行的代码

SELECT sql_text   FROM v$sqlarea  WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (           SELECT sql_address, sql_hash_value             FROM v$session            WHERE SID IN (                     SELECT SID                       FROM v$session a, x$kglpn b                      WHERE a.saddr = b.kglpnuse                        AND b.kglpnmod <> 0                        AND b.kglpnhdl IN (SELECT p1raw                                             FROM v$session_wait                                            WHERE event LIKE 'library%'))) /

SQL_TEXT -------------------------------------------------------------------------------- BEGIN calling; END;

在grant之前和之后我们可以转储一下shared pool的内容观察比较一下:

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';

Session altered.

在grant之前:

从前面的查询获得pining的Handle是52D6730C:

****************************************************** BUCKET 67790:   LIBRARY OBJECT HANDLE: handle=52d6730c   name=SYS.PINING   hash=891b08ce timestamp=09-06-2004 16:43:51   namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]   kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1 --在Object上存在共享pin --在handle上存在Null模式锁定,此模式允许其他用户继续以Null/shared模式锁定该对象   lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]   pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]   ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]     LIBRARY OBJECT: object=52d65ba4     type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0     DATA BLOCKS:     data#     heap  pointer status pins change    alloc(K)  size(K)     ----- -------- -------- ------ ---- ------     -------- --------         0 52d65dac 52d65c90 I/P/A     0 NONE       0.30     0.55         4 52d65c40 52d67c08 I/P/A     1 NONE       0.44     0.48

在发出grant命令后:

****************************************************** BUCKET 67790:   LIBRARY OBJECT HANDLE: handle=52d6730c   name=SYS.PINING   hash=891b08ce timestamp=09-06-2004 16:43:51   namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]   kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1 --由于calling执行未完成,在object上仍让保持共享pin --由于grant会导致重新编译该对象,所以在handle上的排他锁已经被持有 --进一步的需要获得object上的Exclusive pin,由于shared pin被calling持有,所以library cache pin等待出现.   lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]   pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]   ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]     LIBRARY OBJECT: object=52d65ba4     type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0     DATA BLOCKS:     data#     heap  pointer status pins change    alloc(K)  size(K)     ----- -------- -------- ------ ---- ------     -------- --------         0 52d65dac 52d65c90 I/P/A     0 NONE       0.30     0.55         4 52d65c40 52d67c08 I/P/A     1 NONE       0.44     0.48

实际上recompile过程包含以下步骤,我们看一下lock和pin是如何交替发挥作用的: 1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的 exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象. 2.以shared模式pin该对象,以执行安全和错误检查. 3.共享pin被释放,重新以排他模式pin该对象,执行重编译. 4.使所有依赖该过程的对象失效 5.释放exclusive lock和exclusive pin

(二).LIBRARY CACHE LOCK等待事件

如果此时我们再发出一条grant或compile的命令,那么library cache lock等待事件将会出现:

session 3:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 7 17:05:25 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> alter procedure pining compile;

此进程挂起,我们查询v$session_wait视图可以获得以下信息:

SQL> select * from v$session_wait;

 SID SEQ# EVENT               P1TEXT                  P1 P1RAW    P2TEXT               P2 P2RAW    P3TEXT              P3 P3RAW     WAIT_TIME SECONDS STATE  ---- ---- ------------------- --------------- ---------- -------- ------------ ---------- -------- ---------------- -------------- ---------- ------  ---   11  143 library cache pin   handle address  1390239716 52DD5FE4 pin address  1387617456 52B55CB0 100*mode+namespace 301 0000012D          0       6 WAITING   13   18 library cache lock  handle address  1390239716 52DD5FE4 lock address 1387433984 52B29000 100*mode+namespace 301 0000012D          0       3 WAITING    8  415 PL/SQL lock timer   duration        120000     0001D4C0                       0 00                            0 00                0      63 WAITING ....

13 rows selected

由于handle上的lock已经被session 2以exclusive模式持有,所以session 3产生了等待.

我们可以看到,在生产数据库中权限的授予、对象的重新编译都可能会导致library cache pin等待的出现. 所以应该尽量避免在高峰期进行以上操作.

另外我们测试的案例本身就说明:如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现. 所以在应用开发的过程中,我们也应该注意这方面的内容.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏C/C++基础

C++利用MSQL API连接和操作数据库

在Windows平台,我们可以使用ADO、ODBC或者MySQL API进行连接和操作。ADO (ActiveX Data Objects,ActiveX数据对...

3471
来自专栏岑玉海

Hive Tunning(二)优化存储

接着上一章我们讲的hive的连接策略,现在我们讲一下hive的数据存储。 下面是hive支持的数据存储格式,有我们常见的文本,JSON,XML,这里我们主要...

4094
来自专栏大数据学习笔记

Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.5 HQL:DDL数据定义)(草稿)

第11章 Hive:SQL on Hadoop 11.5 HQL:DDL数据定义 HQL中数据定义部分,也就是DDL,主要包括数据库定义和数据表的定义。 前面创...

2509
来自专栏java学习

使用idea2017搭建SSM框架

我这里列出的是搭建完了之后所有的目录和文件,诸位先把目录文件建起来,然后我在给出文件内容

3572
来自专栏乐沙弥的世界

配置sqlnet.ora限制IP访问Oracle

--========================== -- 配置sqlnet.ora 限制IP访问Oracle --===================...

1024
来自专栏逸鹏说道

SQL Server 使用全文索引进行页面搜索

概述 全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一...

3667
来自专栏乐沙弥的世界

SQL*Loader使用方法

SQL*Loader由一个输入控制文件来控制整个装载的相关描述信息,一个或多个数据文件作为原始数据,其详细组成结构包括

982
来自专栏哲学驱动设计

Rafy 框架 - 使用 SqlTree 查询

本文介绍如何使用 Rafy 框架中的 Sql Tree 查询: 除了开发者常用的 Linq 查询,Rafy 框架还提供了 Sql 语法树的方式来进行查询。 这种...

2127
来自专栏互联网杂技

SQL注入攻防入门详解

毕业开始从事winfrm到今年转到 web ,在码农届已经足足混了快接近3年了,但是对安全方面的知识依旧薄弱,事实上是没机会接触相关开发……必须的各种借口。这几...

56110
来自专栏游戏杂谈

node-mysql操作transactions的实例

如果执行commit后,再执行node.js脚本(执行的是一句更新sql--UPDATE user_info SET userName = ‘张一’ WHERE...

1301

扫码关注云+社区

领取腾讯云代金券