[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.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.
–//我导入作者的测试结果在链接https://andreynikolaev.wordpress.com/上可以找到.
–//https://andreynikolaev.wordpress.com/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