[20190423]简单测试latch nowilling等待模式.txt

[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参数,测试看看各种情况.