ITPub博客

首页 > 数据库 > Oracle > [20190415]10g下那些latch是共享的.txt

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

原创 Oracle 作者:lfree 时间:2019-04-15 09:31:31 0 删除 编辑

[20190415]10g下那些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@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 

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


--//我导入作者的测试结果在链接https://andreynikolaev.wordpress.com/上可以找到.

--//https://andreynikolaev.wordpress.com/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], [], [], [], []


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2641371/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2421
  • 访问量
    6191368