[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验证这种情况.