[20190415]10g下那些latch是共享的.txt

--//oracle並沒有文件準確說明那些latch是支援共享,作者的連結透過使用orderbug手工呼叫kslgetsl()函式(10g)或者kslgetsl_w()函

--//數(11g),確定那些latch支援共享模式.我僅僅重複測試看看.

1.環境:

SYS@test> @ &r/ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

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

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 > /dev/null

--//略.

4.結果:

SYS@test> select * from shared_latches;

VERSION LATCH# NAME S

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

10.2.0.4.0 7 session idle bit Y

10.2.0.4.0 8 client/application info Y

10.2.0.4.0 35 resmgr group change latch Y

10.2.0.4.0 36 channel handle pool latch Y

10.2.0.4.0 37 channel operations parent latch Y

10.2.0.4.0 38 message pool operations parent latch Y

10.2.0.4.0 51 SGA mapping latch Y

10.2.0.4.0 52 active service list Y

10.2.0.4.0 97 name-service namespace bucket Y

10.2.0.4.0 101 gcs remastering latch Y

10.2.0.4.0 102 gcs partitioned table hash Y

10.2.0.4.0 103 gcs pcm hashed value bucket hash Y

10.2.0.4.0 106 recovery domain hash list Y

10.2.0.4.0 109 Memory Management Latch Y

10.2.0.4.0 118 buffer pool Y

10.2.0.4.0 122 cache buffers chains Y

10.2.0.4.0 131 object queue header operation Y

10.2.0.4.0 150 KCL gc element parent latch Y

10.2.0.4.0 193 In memory undo latch Y

10.2.0.4.0 194 KTF sga latch Y

10.2.0.4.0 196 Change Notification Hash table latch Y

10.2.0.4.0 204 global KZLD latch for mem in SGA Y

10.2.0.4.0 207 Policy Refresh Latch Y

10.2.0.4.0 208 Policy Hash Table Latch Y

10.2.0.4.0 209 OLS label cache Y

10.2.0.4.0 210 instance information Y

10.2.0.4.0 211 policy information Y

10.2.0.4.0 212 global ctx hash table latch Y

10.2.0.4.0 221 library cache hash chains Y

10.2.0.4.0 229 resmgr:active threads Y

10.2.0.4.0 238 resmgr:plan CPU method Y

10.2.0.4.0 244 Shared B-Tree Y

10.2.0.4.0 245 Memory Queue Y

10.2.0.4.0 246 Memory Queue Subscriber Y

10.2.0.4.0 271 JOX SGA heap latch Y

10.2.0.4.0 284 hash table column usage latch Y

10.2.0.4.0 291 compile environment latch Y

10.2.0.4.0 314 KWQP Prop Status Y

10.2.0.4.0 315 AQ Propagation Scheduling Proc Table Y

10.2.0.4.0 316 AQ Propagation Scheduling System Load Y

10.2.0.4.0 319 rules engine rule set statistics Y

10.2.0.4.0 320 rules engine rule statistics Y

10.2.0.4.0 325 kwqbsn:qsga Y

10.2.0.4.0 327 bufq statistics Y

10.2.0.4.0 329 queue sender's info. latch Y

10.2.0.4.0 330 bq:time manger info latch Y

10.2.0.4.0 333 KWQMN job cache list latch Y

10.2.0.4.0 334 KWQMN to-be-Stopped Buffer list Latch Y

10.2.0.4.0 392 JS Sh mem access Y

10.2.0.4.0 393 PL/SQL warning settings Y

50 rows selected.

--//我匯入作者的測試結果在連結上可以找到.

--//summary-tables/shared_latches-ctl/

--//10.2.0.4.0結果如下:

SYS@book> select count(*) from scott.shared_latches where version='10.2.0.4.0';

COUNT(*)

----------

51

--//相差1個.

SYS@192.168.100.33:1521/test> @ ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SYS@192.168.100.33:1521/test> @ laddr.sql 'parameter table allocation management'

ADDR NAME

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

0000000060009298 parameter table allocation management

SYS@book> select * from scott.shared_latches where version='10.2.0.4.0' minus select * from sys.shared_latches@test033;

VERSION LATCH# NAME S

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

10.2.0.4.0 15 parameter table allocation management Y

--//我手工執行發現報錯,

SYS@192.168.100.33:1521/test> oradebug setmypid

Statement processed.

SYS@192.168.100.33:1521/test> oradebug call kslgetsl 0x0000000060009298 1 2 3 8

ORA-00600: internal error code, arguments: [545], [0x060009298], [15], [8], [], [], [], []

[20190415]10g下那些latch是共享的.txt

相關文章