[20190423]简单测试latch nowilling等待模式.txt
–//我对这个问题的理解就是如果参数willing=0,表示无法获取该latch，直接退出，再寻找类似的latch。
–//我仅仅知道redo copy latch具有这个特性：
> select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch_children where lower(name) like ‘%’||lower(‘redo copy’)||’%’ ;
addr name level# latch# gets misses sleeps immediate_gets immediate_misses waiters_woken waits_holding_latch spin_gets wait_time
—————- ———- —— ———- ———- ———- ———- ————– —————- ————- ——————- ———- ———-
00000012d720ada8 redo copy 4 208 53 0 0 500627938 304381 0 0 0 0
00000012d720acd0 redo copy 4 208 53 0 0 497827706 323330 0 0 0 0
..
00000012d72086d8 redo copy 4 208 53 0 0 491448415 365472 0 0 0 0
00000012d7208600 redo copy 4 208 53 0 0 508008338 391955 0 0 0 0
48 rows selected.
–//你可以发现nowait latch 的一个特点,就是immediate_gets会相对很高.注我查询的生产系统的情况.测试环境不会这么高的.
http://andreynikolaev.wordpress.com/2010/04/12/latch-internals-information-sources/
–//参数如下，另外我前面blog写错了，where是最后1个参数。
kslgetl(laddr, wait, why, where) – get exclusive latch
more precisely, to request the latch oracle kernel needs:
–//更准确地说，要请求闩锁oracle内核需要：
laddress — address of latch in sga
wait — flag. if true, this is latch get in willing-to-wait mode..
why — context why the latch is acquired at this where.
where — code for location from where the latch is acquired.
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
–//参考链接如下:http://blog.itpub.net/267265/viewspace-2641548/=>[20190416]exclusive latch测试脚本.txt
$ cat peek.sh
#! /bib/bash
# 参数如下:latch_name monitoring_duration or laddr
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 exclusive_latch.txt
/* 参数如下: @ exclusive_latch.txt latch_name willing why where sleep_num */
–//connect / as sysdba
col laddr new_value laddr
select addr laddr from v$latch_parent where name=’&&1′;
oradebug setmypid
oradebug call kslgetl 0x&laddr &&2 &&3 &&4
host sleep &&5
oradebug call kslfre 0x&laddr
–//exit
$ cat y1.sh
#! /bin/bash
zdate=$(date ‘+%y%m%d%h%m%s’)
echo $zdate
source peek.sh ‘test excl. parent2 l0’ 20 | timestamp.pl >| /tmp/peekx_${zdate}.txt &
seq 20 | xargs -i{} echo -e ‘sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1’ | bash >| /tmp/latch_freeo_${zdate}.txt &
# 参数如下: @ exclusive_latch.txt latch_name willing why where sleep_num
sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 1 2 6” > /dev/null &
sleep 2
sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 3 4 6” > /dev/null &
sleep 4.1
sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 5 6 6” > /dev/null &
wait
$ . y1.sh
20190424091250
[3]- done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 1 2 6” > /dev/null
[1] done source peek.sh ‘test excl. parent2 l0’ 20 | timestamp.pl >|/tmp/peekx_${zdate}.txt
[4]- done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 3 4 6” > /dev/null
[5]+ done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 5 6 6” > /dev/null
[2]+ done seq 20 | xargs -i{} echo -e ‘sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1’ | bash >|/tmp/latch_freeo_${zdate}.txt
2.测试:
$ grep -v ‘^.*: $’ /tmp/peekx_20190424091250.txt | cut -c10- | uniq -c
1 sysdate laddr
1 ——————- —————-
1 2019-04-24 09:12:50 0000000060009978
1 statement processed.
6 [060009978, 060009980) = 00000015 00000000
1 [060009978, 060009980) = 00000000 00000000
6 [060009978, 060009980) = 00000015 00000000
7 [060009978, 060009980) = 00000000 00000000
–//你可以发现第1个会话申请成功,第2个会话没有申请成功,直接退出.第3个会话申请成功(因为sleep 2+4.1秒).
–//cat /tmp/latch_freeo_20190424091250.txt
2019-04-24 09:12:50
2019-04-24 09:12:51
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=2 why=1, sid=295
2019-04-24 09:12:52
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=2 why=1, sid=295
2019-04-24 09:12:53
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=2 why=1, sid=295
2019-04-24 09:12:54
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=2 why=1, sid=295
2019-04-24 09:12:55
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=2 why=1, sid=295
2019-04-24 09:12:56
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=6 why=5, sid=295
2019-04-24 09:12:57
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=6 why=5, sid=295
2019-04-24 09:12:58
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=6 why=5, sid=295
2019-04-24 09:13:00
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=6 why=5, sid=295
2019-04-24 09:13:01
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=6 why=5, sid=295
2019-04-24 09:13:02
process 21
holding: 0000000060009978 “test excl. parent2 l0” lvl=0 whr=6 why=5, sid=295
2019-04-24 09:13:03
2019-04-24 09:13:04
–//21=0x15,与peek看到的一致.
3.手工测试看看函数的返回值
–//session 1:
sys@book> @ exclusive_latch.txt ‘test excl. parent2 l0’ 0 1 2 60
old 1: select addr laddr from v$latch_parent where name=’&&1′
new 1: select addr laddr from v$latch_parent where name=’test excl. parent2 l0′
laddr
—————-
0000000060009978
statement processed.
function returned 1
function returned 0
–//session 2:
sys@book> @ exclusive_latch.txt ‘test excl. parent2 l0’ 0 3 4 6
old 1: select addr laddr from v$latch_parent where name=’&&1′
new 1: select addr laddr from v$latch_parent where name=’test excl. parent2 l0′
laddr
—————-
0000000060009978
statement processed.
function returned 0
ora-00600: internal error code, arguments: [510], [0x060009978], [test excl. parent2 l0], [], [], [], [], [], [], [], [], []
–//可以发现申请成功函数返回值是1.失败是0.只所以session 2报错主要原因是没有申请成功,kslfre肯定报错.
4.看看latch统计信息的情况:
sys@book> select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch_parent where lower(name) like ‘%’||lower(‘test excl. parent2 l0′)||’%’;
addr name level# latch# gets misses sleeps immediate_gets immediate_misses waiters_woken waits_holding_latch spin_gets wait_time
—————- ——————— —— ———- ———- ———- ———- ————– —————- ————- ——————- ———- ———-
0000000060009978 test excl. parent2 l0 0 5 4 0 0 4 3 0 0 0 0
–//重复测试:
$ . y1.sh
20190424094217
[3]- done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 1 2 6” > /dev/null
[1] done source peek.sh ‘test excl. parent2 l0’ 20 | timestamp.pl >|/tmp/peekx_${zdate}.txt
[4]- done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 3 4 6” > /dev/null
[5]+ done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 5 6 6” > /dev/null
[2]+ done seq 20 | xargs -i{} echo -e ‘sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1’ | bash >|/tmp/latch_freeo_${zdate}.txt
sys@book> select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch_parent where lower(name) like ‘%’||lower(‘test excl. parent2 l0′)||’%’;
addr name level# latch# gets misses sleeps immediate_gets immediate_misses waiters_woken waits_holding_latch spin_gets wait_time
—————- ——————— —— ———- ———- ———- ———- ————– —————- ————- ——————- ———- ———-
0000000060009978 test excl. parent2 l0 0 5 4 0 0 6 4 0 0 0 0
–//可以发现immediate_gets增加2次,immediate_misses增加1次.
$ . y1.sh
20190424094448
[3]- done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 1 1 2 6” > /dev/null
–//注意我修改为willing=1方式获取.
[1] done source peek.sh ‘test excl. parent2 l0’ 20 | timestamp.pl >|/tmp/peekx_${zdate}.txt
[4]- done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 3 4 6” > /dev/null
[5]+ done sqlplus / as sysdba <<< “@ exclusive_latch.txt ‘test excl. parent2 l0’ 0 5 6 6” > /dev/null
[2]+ done seq 20 | xargs -i{} echo -e ‘sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1’ | bash >|/tmp/latch_freeo_${zdate}.txt
sys@book> select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch_parent where lower(name) like ‘%’||lower(‘test excl. parent2 l0′)||’%’;
addr name level# latch# gets misses sleeps immediate_gets immediate_misses waiters_woken waits_holding_latch spin_gets wait_time
—————- ——————— —— ———- ———- ———- ———- ————– —————- ————- ——————- ———- ———-
0000000060009978 test excl. parent2 l0 0 5 5 0 0 7 5 0 0 0 0
–//可以发现gets增加1,immediate_gets增加1次,immediate_misses增加1次.
–//我希望这个这个帖子对于理解latch的统计有所帮助.以前我对于这些统计参数的理解一片混乱.
–//大家可以适当调整sleep参数,测试看看各种情况.