library cache pin(2)

详细分析步骤

下面给出两种方法来分析”library cache pin”,目的是找出哪些session在等待资源,哪些session 正占着这些资源,而占着资源的这些session又在做什么,从而找到问题的根源并加以解决.

方法1.

(1). 通过查询V$SESSION_WAIT找出正在等待”library cache pin”的session,其SQL语句如下:

sql>select sid,substr(event,1,30),p1,p2,p3 from v$session_wait

where wait_time=0 and event like 'library cache pin%';

SID SUBSTR(EVENT,1,30) P1 P2 P3

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

9 library cache pin 15417016 10090832 20

154 library cache pin 15417016 11224168 20

341 library cache pin 15417016 11449936 20

349 library cache pin 15417016 16489792 20

390 library cache pin 15417016 11992536 20

160 library cache pin 15417016 6166600 20

20 library cache pin 15417016 10868760 20

其中:

P1 列是Library Cache Handle Address

P2 列是Library Cache Pin Address.

(2). 把P1的值转换为十六进制,即15417016 --&gt EB3EB8,然后查询X$KGLPN表

(Library Cache Object Pin),可找到相关session,其SQL语句如下 (即把V$SESSION_WAIT中的P1与X$KGLPN中的KGLPNHDL相关连):

sql>select ADDR , INDX , KGLPNADR,KGLPNUSE,KGLPNSES,KGLPNHDL,

kGLPNLCK, KGLPNMOD, KGLPNREQ from x$kglpn where KGLPNHDL like '%EB3EB8%';

ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ

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

05B0CB30 1 00FB9D40 0011B830 0011B830 00EB3EB8 003ECD10 0 2

05B0CB88 2 00AB4468 000E56E0 000E56E0 00EB3EB8 004473A0 0 2

05B0CBE0 3 005E1848 000E7180 000E7180 00EB3EB8 00A30F60 0 2

05B0CC38 4 00B6FDD8 00126E20 00126E20 00EB3EB8 00DD6738 0 2

05B0CC90 5 0099F950 000BD370 000BD370 00EB3EB8 00E07F78 0 2

05B0CCE8 6 00AEB650 001194B0 001194B0 00EB3EB8 00DB6BC8 0 2

05B0CD40 7 00A5D818 000C0440 000C0440 00EB3EB8 0043A1A8 0 2

05B0CD98 8 004001E8 00103A90 00103A90 00EB3EB8 004CA428 3 0

其中:

KGLPNHDL --- Library Cache Handle Address

KGLPNADR --- Library Cache Pin Address.

KGLPNSES --- 识别锁住此pin 的session

KGLPNREQ --- Pin 请求

KGNMODE --- Pin 锁

从以上查询结果可以看到,有一个session正占着pin锁(KGNMOD=3),而其它session正等待此pin锁(KGLPNREQ=2):

ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ

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

05B0CD98 8 004001E8 00103A90 00103A90 00EB3EB8 004CA428 3 0

(3). 查询X$KGLOB (Library Cache Object),可找到相关的object,其SQL语句如下(即把V$SESSION_WAIT中的P1与X$KGLOB中的KGLHDADR相关连):

sql> select * from X$KGLOB where KGLHDADR like '%EB3EB8%';

ADDR INDX KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNADLK KGLNAHSH KGLNATIM KGLNAPTM

KGLHDNSP KGLHDLMD KGLHDPMD KGLHDFLG KGLHDOBJ KGLHDLDC KGLHDIVC KGLHDEXC KGLHDLKC

KGLHDKMK KGLHDDMK K GLHDAMK KGLOBFLG KGLOBSTA KGLOBTYP KGLOBCNU KGLOBHS0 KGLOBHS1 KGLO

BHS2 KGLOBHS3 KGLOBHS4 KGLOBHS5 KGLOBHS6 KGLOBHS7 KGLOBPC0 KGLOBPC6 KGLOBMSZ KGLOBPSZ KGLOBPUS KGLOBCXF KGLOBSOR KGLOBPRS KGLOBDSK KGLOBBUF KGLOBUID KGLOBSID KGLOBOCT

05B0A230 30 00EB3EB 8 00EB3EB8

select count(*) from fa_facturas@decmovi where fa_cuenta -> KGLNAOBJ

= :1

1569934176 22-ENE-97 0 1 3 1342242

816 00EB3C30 2 0 67014 9 0 1

0 1 1 0 65535 1470 0

0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 -4 -4 3

其中:

KGLNAOBJ ---- 相关object的名字(取前面80个字符)

这样,即可查出”library cache pin”是针对哪个object的(此处为”fa_facturas”).

(4). 把V$SESSION的SADDRL列与X$KGLPN的 KGLPNUSE 列相关连,并查询V$SESSION_WAIT,即可查出占着pin锁的session目前正在做什么,其SQL语句如下:

sql>select a.sid,a.username,a.program from v$session a,x$kglpn b where

a.saddr=b.kglpnuse and b.kglpnhdl like '%EB3EB8%' and b.kgnmod<>0;

sql>select event from v$session_wait where sid=;

通过以上查询,可看到此session正等待”Client Message”,即此用户可能已离开客户终端,这样通过把此session杀掉即可解决问题.

方法2

(1). 通过查询V$SESSION_WAIT找到正等待”library cache pin”的session(即等待者),其SQL语句如下:

sql>select sid Waiter,

substr(rawtohex(p1),1,30) Handle,

substr(rawtohex(p2),1,30) Pin_addr

from v$session_wait where wait_time=0 and event like 'library cache pin%';

(2). 通过查询DBA_LOCK_INTERNAL和V$SESSION_WAIT,可得到与”library cache pin” 等待相关的object的名字,其SQL语句如下:

sql>select to_char(SESSION_ID,'999') sid ,

substr(LOCK_TYPE,1,30) Type,

substr(lock_id1,1,23) Object_Name,

substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,

lock_id2 Lock_addr

from dba_lock_internal

where

mode_requested<>'None'

and mode_requested<>mode_held

and session_id in ( select sid from v$session_wait where wait_time=0

and event like 'library cache pin%') ;

(3). 查出”library cache pin”占有者(即阻塞者)的session id,其SQL语句如下:

sql>select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req

from x$kglpn , v$session

where KGLPNHDL in (select p1raw from v$session_wait

where wait_time=0 and event like 'library cache pin%')

and KGLPNMOD <> 0

and v$session.saddr=x$kglpn.kglpnuse ;

(4). 查出”library cache pin”占有者(阻塞者)正在等什么?

sql>select sid,substr(event,1,30),wait_time

from v$session_wait

where sid in (select sid from x$kglpn , v$session

where KGLPNHDL in (select p1raw from v$session_wait

where wait_time=0 and event like 'library cache pin%')

and KGLPNMOD <> 0

and v$session.saddr=x$kglpn.kglpnuse )

;

(5). 查出阻塞者正执行的SQL语句:

sql>select sid,sql_text

from v$session, v$sqlarea

where v$session.sql_address=v$sqlarea.address

and sid=<阻塞者的sid> ;

这样,就可找到”library cache pin”等待的根源,从而解决由此引起的性能问题

另外,也可通过 system dump来分析”library cache pin”等待举例如下:

系统状态:

9 : waiting for 'library cache pin' [PIN: handle=EB3EB8]

154: waiting for 'library cache pin' [PIN: handle=EB3EB8]

341: waiting for 'library cache pin' [PIN: handle=EB3EB8]

341: waiting for 'library cache pin' [PIN: handle=EB3EB8]

390: waiting for 'library cache pin' [PIN: handle=EB3EB8]

160: waiting for 'library cache pin' [PIN: handle=EB3EB8]

20 : waiting for 'library cache pin' [PIN: handle=EB3EB8]

阻塞者(Blockers):

~~~~~~~~--------

PIN: handle=EB3EB8 263: last wait for 'client message'

Object 名字:

~~~~~~~~~~~~

PIN: handle=EB3EB8 CRSR:select count(*) from fa_facturas@decmovi

where fa_cuenta

= :1

[@more@]

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

转载于:http://blog.itpub.net/18921899/viewspace-1017597/