[20210208][20200426]查看shared latch gets的变化.txt

[20210208][20200426]查看shared latch gets的变化.txt --//前年的测试,链接:http://blog.itpub.net/267265/viewspace-2641549/ --//我发现一些细节问题以前没有注意,我重复做一次,当时做的确实有点乱.这次看看整理一下. 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.测试脚本建立: $ cat viewlatch.sql SELECT addr, name, level#, 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('&&1')||'%'; --//补充一点我发现空扫描v$latch也会导致gets的增加.扫描 v$latch_parent不会. --//另外"gcs partitioned table hash" latch是共享的. $ 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 --//定时1秒间隔通过oradebug peek查看该latch地址处的长度60字节内容.. $ cat shared_latch.txt /* 参数如下: @ shared_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)      http://AndreyNikolaev.wordpress.com      This query shows trees of processes currently holding and waiting for latches      Tree output enumerates these processes and latches as following: Process <PID1>  <latch1 holding by PID1>     <processes waiting for latch1>        ...  <latch2 holding by PID1>     <processes waiting for latch2>        ... 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 i5.sh #! /bin/bash zdate=$(date '+%H%M%S') echo $zdate source peek.sh 'gcs partitioned table hash' 10 | timestamp.pl >| /tmp/peeks_${zdate}.txt & seq 10 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt & sleep 1 # 参数如下: @ latch.txt latch_name willing why where mode sleep_num seq 150 | xargs -I {} -P 150 sqlplus -s -l / as sysdba  @ shared_latch.txt 'gcs partitioned table hash' 1 4 {}  s 6 > /dev/null wait $ cat $(which 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() . ": $_"; } 3.测试: SYS@book> @ viewlatch.sql 'gcs partitioned table hash' ADDR             NAME                                         LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH  SPIN_GETS  WAIT_TIME ---------------- ---------------------------------------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- 0000000060018A18 gcs partitioned table hash                        6        296          0          0              0                0             0                   0          0          0 $ . i5.sh 105343 [1]-  Done                    source peek.sh 'gcs partitioned table hash' 10 | timestamp.pl >|/tmp/peeks_${zdate}.txt [2]+  Done                    seq 10 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >|/tmp/latch_free_${zdate}.txt SYS@book> @ viewlatch.sql 'gcs partitioned table hash' ADDR             NAME                                         LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH  SPIN_GETS  WAIT_TIME ---------------- ---------------------------------------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- 0000000060018A18 gcs partitioned table hash                        6        446          1          0              0                0             0                   0          1          0 --//并发执行150个进程获取gcs partitioned table hash latch,可以发现gets增加150，但是注意1个细节，出现1次MISSES，1次 --//SPIN_GETS。我一直以为大量share latch的请求不会出现阻塞进入SPIN，而实际上的测试还是有少量进入spin。 $ grep  -v '^.*: $' /tmp/peeks_105343.txt  | cut -c10- | uniq -c       1  SYSDATE             LADDR       1  ------------------- ----------------       1  2021-02-08 10:53:43 0000000060018A18       1  Statement processed.       2  [060018A18, 060018A54) = 00000000 00000000 00000128 00000096 00000006 00000010 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000       5  [060018A18, 060018A54) = 00000096 00000000 000001BE 00000096 00000006 0000005B 00000004 00000000 00000000 00000001 00000000 00000000 00000000 00000000 00000000       3  [060018A18, 060018A54) = 00000000 00000000 000001BE 00000096 00000006 0000005B 00000004 00000000 00000000 00000001 00000000 00000000 00000000 00000000 00000000 --//150 = 0x96 4.如果看jonathan Lewis<ORALCE 核心技术>中文版本P60: 表4-3 ------------------------------------------------------------------------- 需要的栓锁访问                                   使用方法 ------------------------------------------------------------------------- 以共享模式获取其他进程以共享模式持有的共享栓锁   休眠前仅自旋cou_count+2次. -------------------------------------------------------------------------- --// 英文版本P72: Table 4-3. Summary of Activity When a Latch Isn't Acquired on the First Attempt   ------------------------------------------------------------------------------------------------------------------ Required Latch Access                                        Method Used ------------------------------------------------------------------------------------------------------------------ Get exclusive latch                                          Attempt immediate get, go into spin cycle                                                              once (in this case the process spins 20,000                                                              times), attach to wait list, attempt immediate                                                              get, go to sleep                                                                Get shareable latch in exclusive mode when another           Go into spin cycle (in this case the process process is holding it in some mode (whether shared,          spins 2,000 times each cycle), attach to wait exclusive, or blocking)                                      list, repeat spin cycle, go to sleep if                                                              unsuccessful Get shareable latch in shared mode when another              Don't bother spinning—go straight to the process has got the latch in exclusive or blocking mode      wait list Get shareable latch in shared mode when other                Spin just cpu_count + 2 times before going to processes have the latch in shared mode                      sleep ------------------------------------------------------------------------------------------------------------------ --//问题来了,如何验证 --//Get shareable latch in shared mode when other  processes have the latch in shared mode,Spin just cpu_count + 2 times --//before going to sleep. --//我的理解当大量申请shareable latchin shared mode,也许OS调度不过来,或者要顺序修改某些内存结构，会出现少量的阻塞，进入 --//spin,甚至在spin= cpu_count + 2 ,后进入sleep.另外写一篇blog验证这种情况.