[20190416]檢視shared latch gets的變化.txt

[20190416]檢視shared latch gets的變化.txt

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

SYS@book> 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')||'%';

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        630         69         73              0                0             0                   0          0  541058918

--//補充一點我發現空掃描v$latch也會導致gets的增加.掃描 v$latch_parent不會.

2.測試:

$ cat peek.sh

#! /bib/bash

# 引數如下:latch_name Monitoring_duration

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 60\nhost sleep 1' )

EOF

--//修改peek檢視的長度60.

$ cat i3.sh

#! /bin/bash

zdate=$(date '+%H%M%S')

echo $zdate

source peek.sh 'gcs partitioned table hash' 36 | timestamp.pl >| /tmp/peeks_${zdate}.txt &

seq 36 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt &

# 引數如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5  8 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8  8 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10 8 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 8 6 > /dev/null &

wait

$ grep  -v '^.*: $' /tmp/peeks_153425.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 15:34:25 0000000060018A18

      1  Statement processed.

      2  [060018A18, 060018A54) = 00000001 00000000 00000277 00000096 00000006 00000005 00000004 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

      2  [060018A18, 060018A54) = 00000002 00000000 00000278 00000096 00000006 00000008 00000007 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

      2  [060018A18, 060018A54) = 00000003 00000000 00000279 00000096 00000006 0000000A 00000009 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

      2  [060018A18, 060018A54) = 00000003 00000000 0000027A 00000096 00000006 0000000C 0000000B 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

      2  [060018A18, 060018A54) = 00000002 00000000 0000027A 00000096 00000006 0000000C 0000000B 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

      2  [060018A18, 060018A54) = 00000001 00000000 0000027A 00000096 00000006 0000000C 0000000B 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

     24  [060018A18, 060018A54) = 00000000 00000000 0000027A 00000096 00000006 0000000C 0000000B 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

SYS@book> 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')||'%';

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        634         69         73              0                0             0                   0          0  541058918

--//0x27A=634

--//0x277=631

--//0x45=69

--//0x49=73

--//上下對比可以發現MISSES,SLEEPS次數沒有變化.gets增加4次.WAIT_TIME的時間也沒有變化.

$ cat i4.sh

#! /bin/bash

zdate=$(date '+%H%M%S')

echo $zdate

source peek.sh 'gcs partitioned table hash' 36 | timestamp.pl >| /tmp/peeks_${zdate}.txt &

seq 36 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1'  | bash >| /tmp/latch_free_${zdate}.txt &

# 引數如下: @ latch.txt latch_name willing why where mode sleep_num

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 5  x 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 7 8  s 6 > /dev/null &

sleep 2

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 9 10 s 6 > /dev/null &

sleep 1.9

sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 11 12 s 6 > /dev/null &

wait

SYS@book> 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')||'%';

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        634         69         73              0                0             0                   0          0  541058918

$ grep  -v '^.*: $' /tmp/peeks_153730.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 15:37:30 0000000060018A18

      1  Statement processed.

      6  [060018A18, 060018A54) = 00000015 20000000 0000027B 00000096 00000006 00000005 00000004 00000000 00000000 00000045 00000000 00000000 203FE766 00000000 00000049

      6  [060018A18, 060018A54) = 00000001 00000000 0000027C 00000096 00000006 00000008 00000007 00000000 00000000 00000046 00000000 00000000 207CF91C 00000000 0000004A

      6  [060018A18, 060018A54) = 00000001 00000000 0000027D 00000096 00000006 0000000A 00000009 00000000 00000000 00000047 00000000 00000000 20F71FAA 00000000 0000004B

      6  [060018A18, 060018A54) = 00000001 00000000 0000027E 00000096 00000006 0000000C 0000000B 00000000 00000000 00000048 00000000 00000000 21AFED4D 00000000 0000004C

     12  [060018A18, 060018A54) = 00000000 00000000 0000027E 00000096 00000006 0000000C 0000000B 00000000 00000000 00000048 00000000 00000000 21AFED4D 00000000 0000004C

SYS@book> 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')||'%';

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        638         72         76              0                0             0                   0          0  565177677

--//上下比較,可以發現GETS增加4次,MISSES,SLEEPS增加3次.WAIT_TIME從565177677=>541058918, 相減等於24118759,可以猜測這個單位是微秒.換算後大約24秒.基本符合.

--//也可以發現持有該latch時,gets數量增加.

3.繼續測試:

--//如果大量的併發S mode會出現什麼情況呢?

$ 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 &

# 引數如下: @ latch.txt latch_name willing why where mode sleep_num

sleep 1

seq 100 | xargs -I {} -P 100 sqlplus /nolog @ shared_latch.txt 'gcs partitioned table hash' 1 4 {}  s 6 > /dev/null

wait

--//100個會話併發的情況呢?

SYS@book> 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')||'%';

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        840         73         76              0                0             0                   0          1  565177677

$ grep  -v '^.*: $' /tmp/peeks_155301.txt | cut -c10- | uniq -c

      1  SYSDATE             LADDR

      1  ------------------- ----------------

      1  2019-04-16 15:53:01 0000000060018A18

      1  Statement processed.

      2  [060018A18, 060018A54) = 00000000 00000000 00000348 00000096 00000006 00000055 00000004 00000000 00000000 00000049 00000000 00000000 21AFED4D 00000000 0000004C

      6  [060018A18, 060018A54) = 00000064 00000000 000003AC 00000096 00000006 0000001C 00000004 00000000 00000000 0000004C 00000000 00000000 21AFED4D 00000000 0000004C

      2  [060018A18, 060018A54) = 00000000 00000000 000003AC 00000096 00000006 0000001C 00000004 00000000 00000000 0000004C 00000000 00000000 21AFED4D 00000000 0000004C

SYS@book> 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')||'%';

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        940         76         76              0                0             0                   0          4  565177677

--//沒有問題計數正確!!沒有丟失.

--//0x21AFED4D=565177677,對應的是wait_time總和.