library cache pin的简单的场景重现
来源:互联网 发布:淘宝立即购买灰色 编辑:程序博客网 时间:2024/03/29 00:23
测试library cache pin
[root@orahost ~]# su - oracle
[oracle@orahost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 6 17:45:53 2008
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Welcome to Yau's oracle world !
this is a test environment
fighting deal friend !!!
SQL>
SQL>
SQL>
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
SQL> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(1000);
6 end;
7 /
Procedure created.
在第一个会话中 执行calling 存储过程
exec calling
在第二个会话中对pining进行重新编译
alter procedure pining compile;
此时,我们发现第二个会话处于等待状态。
执行以下脚本
select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) ;
select * from v$session_wait where sid=28
alter system kill session '28,22'
select pro.spid from v$session ses,v$process pro where ses.sid=28 and ses.paddr=pro.addr;
[oracle@orahost ~]$ ps -ef | grep 11302
oracle 11302 11301 0 17:53 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 11461 11397 0 18:52 pts/7 00:00:00 grep 11302
[oracle@orahost ~]$ kill -9 11302
SQL> exec calling
BEGIN calling; END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> alter procedure pining compile;
[root@orahost ~]# su - oracle
[oracle@orahost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 6 17:45:53 2008
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Welcome to Yau's oracle world !
this is a test environment
fighting deal friend !!!
SQL>
SQL>
SQL>
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
SQL> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(1000);
6 end;
7 /
Procedure created.
在第一个会话中 执行calling 存储过程
exec calling
在第二个会话中对pining进行重新编译
alter procedure pining compile;
此时,我们发现第二个会话处于等待状态。
执行以下脚本
select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) ;
select * from v$session_wait where sid=28
alter system kill session '28,22'
select pro.spid from v$session ses,v$process pro where ses.sid=28 and ses.paddr=pro.addr;
[oracle@orahost ~]$ ps -ef | grep 11302
oracle 11302 11301 0 17:53 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 11461 11397 0 18:52 pts/7 00:00:00 grep 11302
[oracle@orahost ~]$ kill -9 11302
SQL> exec calling
BEGIN calling; END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> alter procedure pining compile;
Procedure altered.
0 0
- library cache pin的简单的场景重现
- library cache pin/lock的简单解决办法
- library cache pin和lock的区别
- library cache pin的检查和处理
- library cache pin 锁的问题处理
- Library cache的PIN与LOCK
- library cache pin等待事件的处理
- library cache pin和lock的区别
- 一次特别的library cache pin的解决过程
- 一次特别的library cache pin的解决过程
- 分析解决因”library cache pin”的等待
- 一次library cache pin故障的解决过程
- Oracle Library Cache 的 lock 与 pin 说明
- Oracle Library Cache 的 lock 与 pin 说明
- 一次library cache pin故障的解决过程
- Oracle Library Cache 的 lock 与 pin 说明
- 关于library cache pin和lock等待事件的理解
- 一次library cache pin故障的解决过程
- JAVA墙角涂鸦
- 1-7
- plsql_day03
- 【面经】EPIC
- 【Python】Python读取Excel单元格
- library cache pin的简单的场景重现
- [Android][N/A][SMS]PDU解析——利用AT指令发送CLASS分类短信
- 霍夫曼编码压缩
- 蓝桥杯 ALGO-105 黑色星期五(日期)
- WindowsPhone开发如何创建动态启动界面
- 算法——归并排序
- proc_day01
- 算法之美
- iOS系统的一些单例类