oracle 编译包就锁表,DDL的锁，编译包经常hang住的场景

参考ORACLE官方文档 ID

122793.1

共享池(Shared

Pool)用于缓存最近执行过的SQL语句、PL/SQL程序和数据字典信息，是对SQL语句、PL/SQL程序进行语法分析、编译、执行的区域，其的主要组件有library

cache和dictionary

cache。library

cache 储存了最近的SQL/PLSQL代码的可执行模式(解析或编译版本)。

X$KGLLK   ##主要用来查看library cache 的对象的锁(v$session.event=’library

cache lock’)

x$kglpn   ##主要用来处理library cache pin holder(v$session_wait.event=’library

cache pin’)

X$KGLPN--[K]ernel [G]eneric

[L]ibrary Cache Manager object [P]i[N]s

最简单的 SQL

select * from V$SESSION_BLOCKERS

V$SESSION_BLOCKERS displays the blocker sessions for each blocked session

最常用的SQL

select sid,status,LOGON_TIME,sql_id,blocking_session

"死锁直接源",FINAL_BLOCKING_SESSION

"死锁最终源",event,seconds_in_wait

"会话锁住时间_S",LAST_CALL_ET

"会话STATUS持续时间_S" from

v$session where event like

'library

cache%' and state='WAITING'

and

BLOCKING_SESSION_STATUS='VALID'

and

FINAL_BLOCKING_SESSION_STATUS='VALID'

查询Cache Pin锁(最准确的一种)

x$kglpn.KGLPNHDL对应v$session_wait中的v$session_wait.P1RAW

x$kglpn.KGLPNUSE对应v$session.saddr

查询DDL pin锁的sql

SELECT s.sid,s.event,s.p1raw,s.seconds_in_wait,kglpnmod

"Mode",

kglpnreq "Req", s.LOGON_TIME

FROM x$kglpn p,

v$session s

WHERE p.kglpnuse=s.saddr

and kglpnhdl in

(SELECT

p1raw FROM v$session WHERE

event like 'library

cache %')

kglpnmod "Mode"表示持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)

kglpnreq "Req"表示请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)

查询Cache Lock锁(最准确的一种)

x$kgllk.KGLLKUSE还是x$kgllk.KGLLKSES对应v$session.saddr，好像两者都行

x$kgllk.KGLLKSNM对应v$session.sid

查询DDL lock锁的sql(含锁住的对象)

select s.sid,s.SERIAL#,s.event,s.seconds_in_wait,kgllkmod

Mod,kgllkreq

Request, x$kgllk.USER_NAME，kglnaobj

Object,s.LOGON_TIME

from x$kgllk ,v$session

s where kgllkses=s.saddr

and

KGLLKHDL in (select

p1raw from v$session where

event= 'library

cache lock') order

by

s.LOGON_TIME

kgllkmod表示持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)

kgllkreq表示请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)

查询锁住的DDL对象

selectd.session_id,s.SERIAL#,d.namefromdba_ddl_locks d,v$session

swhered.owner='MKLMIGEM'andd.SESSION_ID=s.sid