Library cache pin
当一个Session要更改library cache中的对象,它必须先获取pin, 在这个session编译或者分析PL/SQL存储过程和视图的过程中oracle会提交library cache pin事件。
(这段不太明白)What actions to take to reduce these waits depend heavily on what blocking scenario is occurring. A common problem scenario is the use of DYNAMIC SQL from within a PL/SQL procedure where the PL/SQL code is recompiled and the DYNAMIC SQL calls something that depends on the calling procedure.
如果出现blocking的情况,可以使用如下方法找到requesting pin的对象:
Select P1 from v$session_wait where event='library cache pin';
select s.sid, kglpnmod "Mode", kglpnreq "Req"
from x$kglpn p, v$session s
where p.kglpnuse=s.saddr
and kglpnhdl='&P1RAW' ;
x$kglpn——[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
sys@ORALOCAL(192.168.0.22)> desc x$kglpn
##主要用来处理library cache pin holder
Name Null? Type
----------------------------------------------------- -------- ------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4)
KGLPNSES RAW(4)
KGLPNHDL RAW(4)
##关联v$session_wait中event为library cache pin的P1RAW,再关联v$session,可以查出sid和serial#
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER
##如果值为3,表示为library cache pin的holder;如果值为0,表示为waiter
KGLPNREQ NUMBER
##如果值为0,表示为library cache pin的holder;如果值为2,表示为waiter
KGLPNDMK NUMBER
KGLPNSPN NUMBER
参数:P1-被pin对象地址,P2-address of load lock, P3 Contains the mode plus the namespace (mode indicates which data pieces of the object are to be loaded; namespace is the object namespace as displayed in V$DB_OBJECT_CACHE view)
Library cache lock
A session must acquire a library cache lock on an object handle to prevent other sessions from accessing it at the same time, or to maintain a dependency for a long time, or to locate an object in the library cache.
与library cache pin 有何区别呢?
Log buffer space
Session向log buffer中写入数据时发现没有足够的空间就会产生log buffer space, 这一事件说明LGWR向redo log写入的速度没有应用产生redo日志的速度快。可能的原因:log buffer过小或者redo log file 存在IO竞争。
Log file parallel write
由LGWR进程提交该事件,when the session waits for LGWR process to write redo from log buffer to all the log members of the redo log group,就会出现这个事件。
在异步IO情况下,LGWR进程同时向所有log file member写入,否则,顺序写入。
有该事件出现时说明磁盘设备速度比较慢或者redo log所在的盘有IO竞争。
参数:P1-number of log files to write to,P2-number of OS blocks to write to,P3-number of IO requests.
Log file sequential read
当Arch进程从online redo log file读取数据块有等待时就会产生log file sequential read事件。
参数:P1-Relative sequence number of the redo log file within the redo log group,P2-block number to start reading from, P3- number of os blocks to read
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/55472/viewspace-433000/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/55472/viewspace-433000/