[20190415]11g下那些latch是共享的.txt
http://andreynikolaev.wordpress.com/2010/11/23/shared-latches-by-oracle-version/
--//oracle并没有文档准确说明那些latch是支持共享,作者的链接通过使用orderbug手工调用kslgetsl()函数(10g)或者kslgetsl_w()函
--//数(11g),确定那些latch支持共享模式.我仅仅重复测试看看.
1.环境:
SYS@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立测试脚本:
--//drop table shared_latches purge;
create table shared_latches(
version varchar2(50), /* Oracle version */
latch# number, /* latch# */
name varchar2(200), /* ltach name */
shared varchar2(1) /* if 'Y' then latch is shared */
);
--//建立表shared_latches.
spool list_shared_latches.sh
select 'sqlplus /nolog @is_latch_shared 0x'||addr||' '||latch#||' "'||translate(name,'''',' ')||'"'
from v$latch_parent;
spool off
--//获得latch list.仅仅测试latch parent就ok了.适当编辑整理脚本.
$ cat is_latch_shared.sql
/*
This file is part of demos for "Contemporary Latch Internals" seminar v.24.08.2010
Copyright: (c) Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) RDTEX
http://AndreyNikolaev.wordpress.com
for 32bit Oracle 10g and above
*/
connect / as sysdba
set verify off
WHENEVER SQLERROR EXIT;
set pagesize 0
alter session set max_dump_file_size=0;
/*
before 11g shared latch get function was named kslgetsl()
in 11g - kslgetsl_w()
*/
col shared_latch_function new_value shared_latch_function
var db_v varchar2(100);
var db_comp varchar2(100);
begin dbms_utility.db_version(:db_v,:db_comp); end;
/
select decode(sign(replace(:db_v,'.','')-110000),1,'kslgetsl_w', 'kslgetsl') shared_latch_function from dual;
--//11g use kslgetsl_w,other use kslgetsl.
oradebug setmypid
/* try to get latch as shared in S mode */
oradebug call &shared_latch_function &1 1 2 3 8
/* if ORA-00600: [545] was raised in previous statement then latch was exclusive. The error terminates the script */
/* free the latch */
oradebug call kslfre &1
/* if we are here, the latch was shared */
insert into shared_latches(version,latch#,name,shared) select version,latch#,name,'Y' from v$latch,v$instance where latch#=&2;
commit;
exit
--//说明:作者利用oradebug调用报错,后面的插入语句不会执行,来完整获得那些shared latch的列表.
3.执行:
$ . list_shared_latches.sh
--//略.
4.结果:
VERSION LATCH# NAME S
---------- ---------- ---------------------------------------- -
11.2.0.4