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

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

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.0 6 test shared non-parent l0 Y

11.2.0.4.0 14 session idle bit Y

11.2.0.4.0 15 client/application info Y

11.2.0.4.0 21 ksim group membership cache Y

11.2.0.4.0 24 parameter table management Y

11.2.0.4.0 49 resmgr group change latch Y

11.2.0.4.0 50 channel handle pool latch Y

11.2.0.4.0 51 channel operations parent latch Y

11.2.0.4.0 52 message pool operations parent latch Y

11.2.0.4.0 75 sixteenth spare latch - S non-parent Y

11.2.0.4.0 76 seventeenth spare latch - S non-parent Y

11.2.0.4.0 77 eighteenth spare latch - S non-parent Y

11.2.0.4.0 78 nineteenth spare latch - S non-parent Y

11.2.0.4.0 79 twentieth spare latch - S non-parent Y

11.2.0.4.0 80 twenty-first spare latch - S par Y

11.2.0.4.0 81 twenty-second spare latch - S par Y

11.2.0.4.0 82 twenty-third spare latch - S par Y

11.2.0.4.0 83 twenty-fourth spare latch - S par Y

11.2.0.4.0 84 twenty-fifth spare latch - S par Y

11.2.0.4.0 92 SGA mapping latch Y

11.2.0.4.0 93 active service list Y

11.2.0.4.0 102 storage server table manipulation latch Y

11.2.0.4.0 144 name-service namespace bucket Y

11.2.0.4.0 149 gcs remastering latch Y

11.2.0.4.0 150 gcs partitioned table hash Y

11.2.0.4.0 151 gcs pcm hashed value bucket hash Y

11.2.0.4.0 155 recovery domain hash list Y

11.2.0.4.0 160 gcr global ctx Y

11.2.0.4.0 164 Memory Management Latch Y

11.2.0.4.0 171 kcbtsemkid latch Y

11.2.0.4.0 173 buffer pool Y

11.2.0.4.0 177 cache buffers chains Y

11.2.0.4.0 183 tablespace key chain Y

11.2.0.4.0 189 object queue header operation Y

11.2.0.4.0 215 gc element Y

11.2.0.4.0 275 MinActiveScn Latch Y

11.2.0.4.0 280 In memory undo latch Y

11.2.0.4.0 281 KTF sga latch Y

11.2.0.4.0 283 Change Notification Hash table latch Y

11.2.0.4.0 286 change notification client cache latch Y

11.2.0.4.0 289 lob segment hash table latch Y

11.2.0.4.0 290 lob segment query latch Y

11.2.0.4.0 291 lob segment dispenser latch Y

11.2.0.4.0 296 space background SGA latch Y

11.2.0.4.0 301 kssmov protection latch Y

11.2.0.4.0 305 domain validation update latch Y

11.2.0.4.0 306 kdlx hb parent latch Y

11.2.0.4.0 308 Dedup Write Append Table Latch Y

11.2.0.4.0 317 global KZLD latch for mem in SGA Y

11.2.0.4.0 318 Read Only Database Account Status Y

11.2.0.4.0 320 Policy Refresh Latch Y

11.2.0.4.0 321 Policy Hash Table Latch Y

11.2.0.4.0 322 OLS label cache Y

11.2.0.4.0 323 instance information Y

11.2.0.4.0 324 policy information Y

11.2.0.4.0 325 global ctx hash table latch Y

11.2.0.4.0 326 Role grants to users Y

11.2.0.4.0 327 Role graph Y

11.2.0.4.0 328 Security Class Hashtable Y

11.2.0.4.0 331 third Audit Vault latch Y

11.2.0.4.0 332 fourth Audit Vault latch Y

11.2.0.4.0 344 resmgr:active threads Y

11.2.0.4.0 349 resmgr:plan CPU method Y

11.2.0.4.0 355 Shared B-Tree Y

11.2.0.4.0 356 Memory Queue Y

11.2.0.4.0 357 Memory Queue Subscriber Y

11.2.0.4.0 369 ODM-NFS:Global file structure Y

11.2.0.4.0 373 SGA heap creation lock Y

11.2.0.4.0 375 SGA pool creation lock Y

11.2.0.4.0 378 SGA blob lock Y

11.2.0.4.0 380 kgb latch Y

11.2.0.4.0 382 SGA table lock Y

11.2.0.4.0 383 Event Group Locks Y

11.2.0.4.0 385 Sage HT Latch Y

11.2.0.4.0 409 JOX SGA heap latch Y

11.2.0.4.0 410 JOX JIT latch Y

11.2.0.4.0 425 hash table dml freq tracking latch Y

11.2.0.4.0 426 hash table column usage latch Y

11.2.0.4.0 433 compile environment latch Y

11.2.0.4.0 436 Result Cache: RC Latch Y

11.2.0.4.0 464 KWQP Prop Status Y

11.2.0.4.0 465 KWQS pqueue ctx latch Y

11.2.0.4.0 466 KWQS pqsubs latch Y

11.2.0.4.0 467 AQ Propagation Scheduling Proc Table Y

11.2.0.4.0 468 AQ Propagation Scheduling System Load Y

11.2.0.4.0 471 rules engine rule set statistics Y

11.2.0.4.0 472 rules engine rule statistics Y

11.2.0.4.0 477 kwqbsn:qsga Y

11.2.0.4.0 479 bufq statistics Y

11.2.0.4.0 481 queue sender's info. latch Y

11.2.0.4.0 482 bq:time manger info latch Y

11.2.0.4.0 486 KWQMN job cache list latch Y

11.2.0.4.0 487 KWQMN to-be-Stopped Buffer list Latch Y

11.2.0.4.0 504 XDB NFS Stateful SGA Latch Y

11.2.0.4.0 505 qmne Export Table Latch Y

11.2.0.4.0 507 XDB Byte Lock SGA Latch Y

11.2.0.4.0 508 XDB Mcache SGA Latch Y

11.2.0.4.0 565 WCR: sync Y

11.2.0.4.0 566 WCR: processes HT Y

11.2.0.4.0 578 JS Sh mem access Y

11.2.0.4.0 579 PL/SQL warning settings Y

101 rows selected.

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

--//summary-tables/shared_latches-ctl/,作者沒有做11.2.0.4測試.

--//11.2.0.3結果如下:

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

COUNT(*)

----------

93

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

相關文章