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

[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 ([e-mail]) 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