[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)
    
     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驗證這種情況.