[20190416]完善shared latch測試指令碼2.txt

--//昨天測試shared latch,連結:http://blog.itpub.net/267265/viewspace-2641414/,感覺有點開竅了.^_^.

For the shared latches Oracle 10g uses kslgetsl(laddr, wait, why, where, mode) function. Oracle 11g has kslgetsl_w()

function with the same interface, but internally uses ksl_get_shared_latch(). Like in my previous post, I guess the

meaning of kslgetsl() arguments as:

--//對於共享鎖存，Oracle 10g使用kslgetsl(laddr，wait，why，where，mode)函式。Oracle 11g具有相同介面的kslgetsl_w()函式，但

--//在內部使用ksl_get_share_latch()。與上一篇文章一樣，我認為kslgetsl()引數的含義是：

--//注:我以前一直以為還是kslgetsl,原來11g已經改為kslgetsl_w,不過內部使用還是ksl_get_shared_latch().

laddress -- address of latch in SGA

wait -- flag. If not 0, then willing-to-wait latch get

why -- context why the latch is acquired at this where.

where -- location from where the latch is acquired (x$ksllw.indx)

And the last one is:

mode – Exclusive or shared mode

the mode argument took only two values:

8 -- "SHARED"

16 -- "EXCLUSIVE"

--//我當時的測試針對'gcs partitioned table hash' 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

$ cat peek.sh

#! /bib/bash

# 引數如下:latch_name Monitoring_duration

sqlplus -s -l / as sysdba <<EOF

col laddr new_value laddr

SELECT sysdate,addr laddr FROM v\$latch_parent WHERE NAME='$1';

oradebug setmypid

$(seq $2|xargs -I{} echo -e 'oradebug peek 0x&laddr 8\nhost sleep 1' )

EOF

$ cat shared_latch.txt

/* 引數如下: @ latch.txt latch_name willing why where mode sleep_num */

connect / as sysdba

col laddr new_value laddr

col vmode new_value vmode

select decode(lower('&&5'),'s',8,'x',16,'8',8,'16',16) vmode from dual ;

SELECT addr laddr FROM v$latch_parent WHERE NAME='&&1';

oradebug setmypid

oradebug call kslgetsl_w 0x&laddr &&2 &&3 &&4 &vmode

host sleep &&6

oradebug call kslfre 0x&laddr

exit

$ cat latch_free.sql

/*

This file is part of demos for "Contemporary Latch Internals" seminar v.18.09.2010

Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)

This query shows trees of processes currently holding and waiting for latches

Tree output enumerates these processes and latches as following:

Process <PID1>

...

...

Process <PID2>

...

*/

set head off

set feedback off

set linesize 120

select sysdate from dual;

select LPAD(' ', (LEVEL - 1) )

||case when latch_holding is null then 'Process '||pid

else 'holding: '||latch_holding||' "'||name||'" lvl='||level#||' whr='||whr||' why='||why ||', SID='||sid

end

|| case when latch_waiting is not null then ', waiting for: '||latch_waiting||' whr='||whr||' why='||why

end latchtree

from (

/* Latch holders */

select ksuprpid pid,ksuprlat latch_holding, null latch_waiting, to_char(ksuprpid) parent_id, rawtohex(ksuprlat) id,

ksuprsid sid,ksuprllv level#,ksuprlnm name,ksuprlmd mode_,ksulawhy why,ksulawhr whr from x$ksuprlat

union all

/* Latch waiters */

select indx pid,null latch_holding, ksllawat latch_waiting,rawtohex(ksllawat) parent_id,to_char(indx) id,

null,null,null,null,ksllawhy why,ksllawer whr from x$ksupr where ksllawat !='00'

union all

/* The roots of latch trees: processes holding latch but not waiting for latch */

select pid, null, null, null, to_char(pid),null,null,null,null,null,null from (

select distinct ksuprpid pid from x$ksuprlat

minus

select indx pid from x$ksupr where ksllawat !='00')

) latch_op

connect by prior id=parent_id

start with parent_id is null;

$ cat /usr/local/bin/timestamp.pl

#!/usr/bin/perl

while (<>) {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();

printf("%02d:%02d:%02d", $hour, $min, $sec);

print ": $_";

#print localtime() . ": $_";

}

--//使用timestamp.pl在開始標註時間.這樣更加清晰.

2.測試:

--//補充測試 X mode,S mode ,X 模式的情況.

$ cat f1.sh

#! /bin/bash

source peek.sh 'gcs partitioned table hash' 30 | timestamp.pl >| /tmp/peeks.txt &

seq 30 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >| /tmp/latch_free.txt &

# 引數如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 s 6 > /dev/null &

sleep 0.1

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5 x 6 > /dev/null &

wait

$ grep -v '^.*: $' /tmp/peeks.txt

09:28:38: SYSDATE LADDR

09:28:38: ------------------- ----------------

09:28:38: 2019-04-16 09:28:38 0000000060018A18

09:28:38: Statement processed.

09:28:38: [060018A18, 060018A20) = 0000001C 20000000

09:28:39: [060018A18, 060018A20) = 0000001C 20000000

09:28:40: [060018A18, 060018A20) = 0000001C 20000000

09:28:41: [060018A18, 060018A20) = 0000001C 20000000

09:28:42: [060018A18, 060018A20) = 0000001C 20000000

09:28:43: [060018A18, 060018A20) = 0000001C 20000000

09:28:44: [060018A18, 060018A20) = 00000001 00000000

09:28:45: [060018A18, 060018A20) = 00000001 00000000

09:28:46: [060018A18, 060018A20) = 00000001 00000000

09:28:47: [060018A18, 060018A20) = 00000001 00000000

09:28:48: [060018A18, 060018A20) = 00000001 00000000

09:28:49: [060018A18, 060018A20) = 00000001 00000000

09:28:50: [060018A18, 060018A20) = 00000001 00000000

09:28:51: [060018A18, 060018A20) = 00000001 00000000

09:28:52: [060018A18, 060018A20) = 00000001 00000000

09:28:53: [060018A18, 060018A20) = 00000001 00000000

09:28:54: [060018A18, 060018A20) = 00000001 00000000

09:28:55: [060018A18, 060018A20) = 00000001 00000000

09:28:56: [060018A18, 060018A20) = 0000001F 20000000

09:28:57: [060018A18, 060018A20) = 0000001F 20000000

09:28:58: [060018A18, 060018A20) = 0000001F 20000000

09:28:59: [060018A18, 060018A20) = 0000001F 20000000

09:29:00: [060018A18, 060018A20) = 0000001F 20000000

09:29:01: [060018A18, 060018A20) = 0000001F 20000000

09:29:02: [060018A18, 060018A20) = 00000000 00000000

09:29:03: [060018A18, 060018A20) = 00000000 00000000

09:29:04: [060018A18, 060018A20) = 00000000 00000000

09:29:05: [060018A18, 060018A20) = 00000000 00000000

09:29:06: [060018A18, 060018A20) = 00000000 00000000

09:29:07: [060018A18, 060018A20) = 00000000 00000000

$ grep -v '^.*: $' /tmp/peeks.txt | cut -c10- | uniq -c

1 SYSDATE LADDR

1 ------------------- ----------------

1 2019-04-16 09:28:38 0000000060018A18

1 Statement processed.

6 [060018A18, 060018A20) = 0000001C 20000000

12 [060018A18, 060018A20) = 00000001 00000000

6 [060018A18, 060018A20) = 0000001F 20000000

6 [060018A18, 060018A20) = 00000000 00000000

--//僅僅注意一個細節,peek值的後4位並沒有出現0x40000000的情況.

$ cat /tmp/latch_free.txt

2019-04-16 09:28:38

2019-04-16 09:28:39

Process 28

holding: 0000000060018A18 "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

2019-04-16 09:28:40

Process 28

holding: 0000000060018A18 "gcs partitioned table hash" lvl=6 whr=5 why=4, SID=58

Process 29, waiting for: 0000000060018A18 whr=5 why=4

Process 30, waiting for: 0000000060018A18 whr=5 why=4