[20190416]process allocation latch.txt--//看鏈接:http://andreynikolaev.wordpress.com/2010/12/16/hidden-latch-wait-revolution-that-we-missed/--//裡面提到:Orac ...
[20190416]process allocation latch.txt
--//看鏈接:http://andreynikolaev.wordpress.com/2010/12/16/hidden-latch-wait-revolution-that-we-missed/
--//裡面提到:
Oracle no longer uses "repeatedly spin and sleep" approach. The process spins and waits only once. The pseudo code for
contemporary latch acquisition should looks like:
--//Oracle不再使用"反覆旋轉和睡眠"的方法。進程只旋轉一次並等待一次。當代鎖存獲取的偽代碼應該如下所示：
Immediate latch get
Spin latch get
Add the process to the queue of latch waiters
Sleep until posted
Only "process allocation" latch shows different system calls. On Solaris this latch waits using pollsys() system call.
On Linux and HP-UX it uses select():
--//只有"process allocation"鎖存顯示不同的系統調用。在Solaris上，這個閂鎖使用pollsys()系統調用等待。在Linux和HP-UX上，它
--//使用select()：
--//先不管其它latch的改進,先看看process allocation的情況,通過測試說明問題.
--//另外說明千萬不要在生產系統做這樣的測試,阻塞process allocation 拴鎖會導致全部用戶無法登錄!!
1.環境:
SCOTT@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> @ laddr 'process allocation'
old 1: select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch where lower(name) like '%'||lower('&&1')||'%'
new 1: select addr,name,level#,latch#,gets,misses,sleeps,immediate_gets,immediate_misses,waiters_woken,waits_holding_latch,spin_gets,wait_time from v$latch where lower(name) like '%'||lower('process allocation')||'%'
ADDR NAME LEVEL# LATCH# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS WAIT_TIME
---------------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ----------
0000000060009F88 process allocation 7 8 8595 32 25339 4103 15 0 0 15 206377434
0000000060011F20 OS process allocation 4 95 74998 2 0 0 0 0 0 2 0
old 1: 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('&&1')||'%'
new 1: 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('process allocation')||'%'
ADDR NAME LEVEL# LATCH# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS WAIT_TIME
---------------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ----------
0000000060009F88 process allocation 7 8 8595 32 25339 4103 15 0 0 15 206377434
0000000060011F20 OS process allocation 4 95 74998 2 0 0 0 0 0 2 0
old 1: 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('&&1')||'%'
new 1: 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('process allocation')||'%'
--//可以看出這個拴鎖沒有子拴鎖.僅僅1個父拴鎖.
SYS@book> select * from exclusive_latches where name='process allocation';
VERSION LATCH# NAME S
----------- ------ ---------------------------------------- -
11.2.0.4.0 8 process allocation N
--//拴鎖類型是exclusive類型.測試可以參考鏈接:
http://blog.itpub.net/267265/viewspace-2641370/ => [20190415]11g下那些latch是共用的.txt
http://blog.itpub.net/267265/viewspace-2641482/ => [20190416]11g下那些latch是Exclusive的.txt
2.測試:
--//建立測試腳本,註意測試前最好預先登錄幾個會話sys用戶,避免測試後無法在登錄.
$ cat p1.sh
#! /bin/bash
vdate=$(date '+%H%M%S')
echo $vdate
source peek.sh "$1" 12 | timestamp.pl >| /tmp/peekx_${vdate}.txt &
seq 12 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >| /tmp/latch_free_${vdate}.txt &
sleep 1
# 參數如下: @ exclusive_latch.txt latch_name willing why where sleep_num
sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null &
sleep 1
strace -ftT -o /tmp/pp_${vdate}.txt sqlplus -s -l scott/book <<< 'select sysdate from dual ;' &
wait
$ . p1.sh 'process allocation'
171724
SYSDATE
-------------------
2019-04-16 17:17:35
[1] Done source peek.sh "$1" 12 | timestamp.pl >|/tmp/peekx_${vdate}.txt
[3]- Done sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null
[4]+ Done strace -ftT -o /tmp/pp_${vdate}.txt sqlplus -s -l scott/book <<< 'select sysdate from dual ;'
[2]+ Done seq 12 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >|/tmp/latch_free_${vdate}.txt
$ grep -v '^.*: $' /tmp/peekx_171724.txt | cut -c10- | uniq -c
1 SYSDATE LADDR
1 ------------------- ----------------
1 2019-04-16 17:17:24 0000000060009F88
1 Statement processed.
1 [060009F88, 060009FC4) = 00000000 00000000 00002153 00020008 00000007 0000008A 8620F338 00000000 00000FEE 0000001E 0000000F 00000000 0B2D0C03 00000000 000059FC
10 [060009F88, 060009FC4) = 0000001B 00000000 00002155 00020008 00000007 00000005 00000004 00000000 00000FEF 0000001E 0000000F 00000000 0B2D0C03 00000000 000059FC
1 [060009F88, 060009FC4) = 00000000 00000000 00002159 00020008 00000007 0000008A 86212560 00000000 00000FF1 00000020 0000000F 00000000 0C4D11DA 00000000 000062FB
--//才發現我的腳本寫的有問題,seq 12 ...那行不停登陸看latch情況也被阻塞.而且這行總是最後完成也說明這點.這樣執行效率太低..
--//不過不影響測試結論.腳本修改如下:
$ cat p2.sh
#! /bin/bash
vdate=$(date '+%H%M%S')
echo $vdate
source peek.sh "$1" 12 | timestamp.pl >| /tmp/peekx_${vdate}.txt &
sqlplus -s -l / as sysdba <<EOF >| /tmp/latch_free_${vdate}.txt &
$(seq 12 | xargs -I {} echo -e '@latch_free \n host sleep 1')
EOF
sleep 1
# 參數如下: @ exclusive_latch.txt latch_name willing why where sleep_num
sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null &
sleep 1
strace -ftT -o /tmp/pp_${vdate}.txt sqlplus -s -l scott/book <<< 'select sysdate from dual ;' &
wait
----------------
$ awk '{print $3}' /tmp/pp_171724.txt | uniq -c | sort -nr | head
1080 select(0,
174 getrusage(RUSAGE_SELF,
79 getrusage(RUSAGE_SELF,
49 getrusage(RUSAGE_SELF,
30 read(4,
30 read(4,
29 read(4,
29 read(4,
27 getrusage(RUSAGE_SELF,
27 getrusage(RUSAGE_SELF,
--//出現1080次調用
$ grep 'select(' /tmp/pp_171724.txt | head
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008083>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008114>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008117>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008148>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008121>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008121>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008119>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008126>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008120>
18887 17:17:26 select(0, [], [], [], {0, 8000}) = 0 (Timeout) <0.008119>
--//你可以發現語句執行時間在'2019-04-16 17:17:35',與select第1次調用相差9秒.而且每次調用間隔是8000微秒.
--//順便看看select 調用總共花了多少時間.
$ awk '/select/ {print $NF}' /tmp/pp_171724.txt | tr -d '<>' | xargs | sed 's/ /+/g' | bc -l
8.787933
--//基本符合.
3.oracle為什麼這樣設計:
--//我想應該是登錄儘可能快.採用這樣設計延遲更小一些.
SYS@book> @ pt2 'select * from x$kslltr where KSLLTNAM=''process allocation''';
old 6: from table(xmlsequence(cursor( &1 )))
new 6: from table(xmlsequence(cursor( select * from x$kslltr where KSLLTNAM='process allocation' )))
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ------- --------------- -------------------
1 1 ADDR 00007F0270051660
2 INDX 8
3 INST_ID 1
4 KSLLTADDR 0000000060009F88
5 KSLLTNUM 8
6 KSLLTNGT 4096
7 KSLLTNFA 15
8 KSLLTWGT 8574
9 KSLLTWFF 32
10 KSLLTWSL 25339
11 KSLLTHST0 15
12 KSLLTCNM 0
13 KSLLTWHR 138
14 KSLLTWHY 2250314920
15 KSLLTWTT 206377434
16 CLASS_KSLLT 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
17 KSLLTLVL 7
18 KSLLTHSH 2600548697
19 KSLLTNAM process allocation
20 KSLLTWKC 0
21 KSLLTWTH 0
22 KSLLTMSX 0
23 KSLLTMXS 0
24 KSLLTMSW 0
25 KSLLTWSX 0
26 KSLLTWXS 0
27 KSLLTWSW 0
28 KSLLTHST1 0
29 KSLLTHST2 0
30 KSLLTHST3 0
31 KSLLTHST4 0
32 KSLLTHST5 0
33 KSLLTHST6 0
34 KSLLTHST7 0
35 KSLLTHST8 0
36 KSLLTHST9 0
37 KSLLTHST10 0
38 KSLLTHST11 0
39 KSLLTHDT 0
40 KSLLTDNT 0
41 KSLLTWTW 0
42 YIELDS_KSLLT 0
43 MISSES_WL_KSLLT 0
44 YIELDS_WL_KSLLT 0
45 SLEEPS_WL_KSLLT 0
45 rows selected.
--//latch_name='process allocation'使用拴鎖類CLASS_KSLLT=2.
SYS@book> select CLASS_KSLLT,count(*) from x$kslltr group by CLASS_KSLLT;
CLASS_KSLLT COUNT(*)
----------- ----------
2 1
0 581
--//可以發現僅僅1個CLASS_KSLLT=2的情況.就是'process allocation'.
SYS@book> select * from x$ksllclass ;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
---------------- ---- ------- ----- ----- -------- ------ ------ ------ ------ ------ ------ ------ ------
00000000861986C0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
00000000861986EC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000
0000000086198718 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000086198744 3 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000
0000000086198770 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
000000008619879C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
00000000861987C8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
00000000861987F4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
8 rows selected.
3.更改拴鎖類看看:
--//前面已經知道name=process allocation'的latch#=8.安全起見建立pfile,修改它啟動看看.
--//alter system set "_latch_classes"='8:3' scope=spfile;
SYS@book> create pfile='/tmp/@.ora' from spfile ;
File created.
--//修改/tmp/book.ora文件加入如下:
*._latch_classes='8:3'
--//關閉資料庫並使用該參數文件重啟資料庫
SYS@book> startup pfile=/tmp/@.ora
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
SYS@book> select CLASS_KSLLT,KSLLTNAM from x$kslltr where KSLLTNAM='process allocation';
CLASS_KSLLT KSLLTNAM
----------- ------------------
3 process allocation
--//現在是3.
$ . p1.sh 'process allocation'
174817
SYSDATE
-------------------
2019-04-16 17:48:29
[1] Done source peek.sh "$1" 12 | timestamp.pl >|/tmp/peekx_${vdate}.txt
[3]- Done sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null
[4]+ Done strace -ftT -o /tmp/pp_${vdate}.txt sqlplus -s -l scott/book <<< 'select sysdate from dual ;'
[2]+ Done seq 12 | xargs -I{} echo -e 'sqlplus -s -l / as sysdba <<< @latch_free\nsleep 1' | bash >|/tmp/latch_free_${vdate}.txt
$ grep -v '^.*: $' /tmp/peekx_174817.txt | cut -c10- | uniq -c
1 SYSDATE LADDR
1 ------------------- ----------------
1 2019-04-16 17:48:18 0000000060009F88
1 Statement processed.
1 [060009F88, 060009FC4) = 00000000 00000000 0000004E 00030008 00000007 00000081 00000000 00000000 00000024 00000000 00000000 00000000 00000000 00000000 00000000
10 [060009F88, 060009FC4) = 00000018 00000000 00000051 00030008 00000007 00000005 00000004 00000000 00000025 00000000 00000000 00000000 00000000 00000000 00000000
1 [060009F88, 060009FC4) = 00000000 00000000 00000055 00030008 00000007 0000008A 862103F0 00000000 00000026 00000002 00000001 00000000 011F5A13 00000000 00003F80
$ awk '/select/ {print $NF}' /tmp/pp_174817.txt | wc
7356 7356 80916
--//調用次數更多對比前面的測試.
$ grep 'select' /tmp/pp_174817.txt | head
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001103>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001107>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001080>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001103>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001102>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001089>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001056>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001087>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001085>
19323 17:48:20 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001080>
--//現在的時間間隔是0.001000秒.
$ awk '/select/ {print $NF}' /tmp/pp_174817.txt | tr -d '<>' | xargs | sed 's/ /+/g' | bc -l
8.048958
--//select調用次數多了,但是時間消耗上比前面少(8.787933-8.048958 = .738975),
--//可以看出spin的消耗更大.spin次數也增加了.我的理解在每次select之間做spin,次數設計上不是2000次而是20000次.
--//不成功調用select,如何反覆.
--//看select * from x$ksllclass ;的顯示.
4.實際上還可以定製sleep的參數:
--//alter system set "_latch_class_3"="100 0 1 10000 20000 30000 40000 50000 60000 70000 80000" scope=spfile;
--//修改/tmp/book.ora文件加入如下:
*._latch_classes='8:3'
*._latch_class_3='100 0 1 10000 20000 30000 40000 50000 60000 70000 80000'
--//關閉資料庫並使用該參數文件重啟資料庫
SYS@book> startup pfile=/tmp/@.ora
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
SYS@book> show parameter latch
NAME TYPE VALUE
-------------- ------ -------------------------------------------------------
_latch_class_3 string 100 0 1 10000 20000 30000 40000 50000 60000 70000 80000
_latch_classes string 8:3
SYS@book> select * from x$ksllclass ;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00000000861986C0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
00000000861986EC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000
0000000086198718 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
0000000086198744 3 1 100 0 1 10000 20000 30000 40000 50000 60000 70000 80000
0000000086198770 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
000000008619879C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
00000000861987C8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
00000000861987F4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
8 rows selected.
$ . p2.sh 'process allocation'
085808
[1] Done sqlplus -s -l / as sysdba >|/tmp/latch_free.txt <<EOF
$(seq 12 | xargs -I {} echo -e '@latch_free \n host sleep 1')
EOF
SYSDATE
-------------------
2019-04-17 08:58:19
[2] Done source peek.sh "$1" 12 | timestamp.pl >|/tmp/peekx_${vdate}.txt
[4]- Done sqlplus /nolog @ exclusive_latch.txt "$1" 1 4 5 10 > /dev/null
[5]+ Done strace -ftT -o /tmp/pp_${vdate}.txt sqlplus -s -l scott/book <<< 'select sysdate from dual ;'
[3]+ Done sqlplus -s -l / as sysdba >|/tmp/latch_free_${vdate}.txt <<EOF
$(seq 12 | xargs -I {} echo -e '@latch_free \n host sleep 1')
EOF
$ grep -v '^.*: $' /tmp/peekx_085808.txt | cut -c10- | uniq -c
1 SYSDATE LADDR
1 ------------------- ----------------
1 2019-04-17 08:58:08 0000000060009F88
1 Statement processed.
1 [060009F88, 060009FC4) = 00000000 00000000 00000058 00030008 00000007 00000081 00000000 00000000 00000028 00000000 00000000 00000000 00000000 00000000 00000000
10 [060009F88, 060009FC4) = 0000001B 00000000 0000005A 00030008 00000007 00000005 00000004 00000000 00000029 00000000 00000000 00000000 00000000 00000000 00000000
1 [060009F88, 060009FC4) = 00000000 00000000 0000005C 00030008 00000007 00000081 00000000 00000000 0000002A 00000001 00000000 00000000 00889761 00000000 00000073
$ grep 'select' /tmp/pp_085808.txt | head -20
23504 08:58:10 select(0, [], [], [], {0, 10000}) = 0 (Timeout) <0.010084>
23504 08:58:10 select(0, [], [], [], {0, 20000}) = 0 (Timeout) <0.020129>
23504 08:58:10 select(0, [], [], [], {0, 30000}) = 0 (Timeout) <0.030121>
23504 08:58:10 select(0, [], [], [], {0, 40000}) = 0 (Timeout) <0.040120>
23504 08:58:10 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050132>
23504 08:58:10 select(0, [], [], [], {0, 60000}) = 0 (Timeout) <0.060148>
23504 08:58:10 select(0, [], [], [], {0, 70000}) = 0 (Timeout) <0.070153>
23504 08:58:10 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080179>
23504 08:58:10 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080151>
23504 08:58:10 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080155>
23504 08:58:10 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080160>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080155>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080158>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080153>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080163>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080165>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080163>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080157>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080173>
23504 08:58:11 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080152>
...
--//開始select時間是0.01,0.02,0.03,0.04,0.05,0.06,0.07,0.08,0.08....
$ awk '/select/ {print $NF}' /tmp/pp_085808.txt | tr -d '<>' | xargs | sed 's/ /+/g' | bc -l
8.938015
--//非常接近9秒,說明spin消耗很小,每次select之間spin次數是100.而且休眠時間比原來長.
5.繼續測試,修改裡面參數yield對應x$ksllclass.yield看看:
--//修改如下:
*._latch_class_3='100 1 1 10000 20000 30000 40000 50000 60000 70000 80000'
SYS@book> select * from x$ksllclass where indx=3;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000086198744 3 1 100 1 1 10000 20000 30000 40000 50000 60000 70000 80000
--//yield=1
--//執行p2.sh.僅僅貼出測試結果.
--// /tmp/pp_090552.txt
23747 09:05:54 sched_yield() = 0 <0.000019>
23747 09:05:54 select(0, [], [], [], {0, 10000}) = 0 (Timeout) <0.010112>
23747 09:05:54 sched_yield() = 0 <0.000027>
23747 09:05:54 select(0, [], [], [], {0, 20000}) = 0 (Timeout) <0.020130>
23747 09:05:54 sched_yield() = 0 <0.000027>
23747 09:05:54 select(0, [], [], [], {0, 30000}) = 0 (Timeout) <0.030124>
23747 09:05:55 sched_yield() = 0 <0.000032>
23747 09:05:55 select(0, [], [], [], {0, 40000}) = 0 (Timeout) <0.040122>
23747 09:05:55 sched_yield() = 0 <0.000032>
23747 09:05:55 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050173>
23747 09:05:55 sched_yield() = 0 <0.000032>
23747 09:05:55 select(0, [], [], [], {0, 60000}) = 0 (Timeout) <0.060134>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 70000}) = 0 (Timeout) <0.070143>
23747 09:05:55 sched_yield() = 0 <0.000028>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080167>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080165>
23747 09:05:55 sched_yield() = 0 <0.000032>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080168>
23747 09:05:55 sched_yield() = 0 <0.000030>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080151>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080156>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080159>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080163>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080151>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080080>
23747 09:05:55 sched_yield() = 0 <0.000027>
23747 09:05:55 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080152>
23747 09:05:56 sched_yield() = 0 <0.000027>
....
23747 09:06:03 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080141>
23747 09:06:03 sched_yield() = 0 <0.000027>
23747 09:06:03 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080157>
23747 09:06:03 sched_yield() = 0 <0.000029>
23747 09:06:03 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080152>
23747 09:06:03 sched_yield() = 0 <0.000026>
23747 09:06:03 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080152>
23747 09:06:03 geteuid() = 502 <0.000028>
23747 09:06:03 getegid() = 502 <0.000026>
--//在select之間調用sched_yield.
--//修改如下:
*._latch_class_3='100 2 1 10000 20000 30000 40000 50000 60000 70000 80000'
SYS@book> select * from x$ksllclass where indx=3;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000086198744 3 1 100 2 1 10000 20000 30000 40000 50000 60000 70000 80000
--//yield=2
--// cat /tmp/pp_091932.txt
24011 09:19:34 sched_yield() = 0 <0.000017>
24011 09:19:34 sched_yield() = 0 <0.000016>
24011 09:19:34 select(0, [], [], [], {0, 10000}) = 0 (Timeout) <0.010090>
24011 09:19:34 sched_yield() = 0 <0.000017>
24011 09:19:34 sched_yield() = 0 <0.000017>
24011 09:19:34 select(0, [], [], [], {0, 20000}) = 0 (Timeout) <0.020136>
24011 09:19:34 sched_yield() = 0 <0.000019>
24011 09:19:34 sched_yield() = 0 <0.000017>
24011 09:19:34 select(0, [], [], [], {0, 30000}) = 0 (Timeout) <0.030152>
24011 09:19:34 sched_yield() = 0 <0.000018>
24011 09:19:34 sched_yield() = 0 <0.000017>
24011 09:19:34 select(0, [], [], [], {0, 40000}) = 0 (Timeout) <0.040147>
24011 09:19:34 sched_yield() = 0 <0.000019>
24011 09:19:34 sched_yield() = 0 <0.000017>
24011 09:19:34 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050155>
24011 09:19:35 sched_yield() = 0 <0.000021>
24011 09:19:35 sched_yield() = 0 <0.000017>
24011 09:19:35 select(0, [], [], [], {0, 60000}) = 0 (Timeout) <0.060158>
24011 09:19:35 sched_yield() = 0 <0.000019>
24011 09:19:35 sched_yield() = 0 <0.000016>
24011 09:19:35 select(0, [], [], [], {0, 70000}) = 0 (Timeout) <0.070173>
24011 09:19:35 sched_yield() = 0 <0.000018>
24011 09:19:35 sched_yield() = 0 <0.000017>
24011 09:19:35 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080182>
24011 09:19:35 sched_yield() = 0 <0.000019>
24011 09:19:35 sched_yield() = 0 <0.000017>
24011 09:19:35 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080180>
24011 09:19:35 sched_yield() = 0 <0.000018>
24011 09:19:35 sched_yield() = 0 <0.000017>
24011 09:19:35 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080178>
24011 09:19:35 sched_yield() = 0 <0.000020>
24011 09:19:35 sched_yield() = 0 <0.000017>
24011 09:19:35 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080177>
--//在select之間調用sched_yield 2次. 可以猜測是yield參數就是增加調用sched_yield的次數.
--//修改如下,yield=0,WAITTIME=2.
*._latch_class_3='100 0 2 10000 20000 30000 40000 50000 60000 70000 80000'
SYS@book> select * from x$ksllclass where indx=3;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000086198744 3 1 100 0 2 10000 20000 30000 40000 50000 60000 70000 80000
--//yield=0,WAITTIME=2.
--// cat /tmp/pp_092556.txt
24388 09:25:58 select(0, [], [], [], {0, 10000}) = 0 (Timeout) <0.010082>
24388 09:25:58 select(0, [], [], [], {0, 20000}) = 0 (Timeout) <0.020122>
24388 09:25:58 select(0, [], [], [], {0, 30000}) = 0 (Timeout) <0.030121>
24388 09:25:58 select(0, [], [], [], {0, 40000}) = 0 (Timeout) <0.040120>
24388 09:25:58 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050082>
24388 09:25:58 select(0, [], [], [], {0, 60000}) = 0 (Timeout) <0.060109>
24388 09:25:58 select(0, [], [], [], {0, 70000}) = 0 (Timeout) <0.070140>
24388 09:25:58 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080157>
24388 09:25:58 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080149>
24388 09:25:58 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080162>
24388 09:25:58 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080160>
--//waittime=2,表示什麼不懂.我修改到WAITTIME=1000000000看看.
*._latch_class_3='100 0 1000000000 10000 20000 30000 40000 50000 60000 70000 80000'
SYS@book> select * from x$ksllclass where indx=3;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000086198744 3 1 100 0 1000000000 10000 20000 30000 40000 50000 60000 70000 80000
--//也沒有看出來什麼不同..
25049 0.000135 select(0, [], [], [], {0, 10000}) = 0 (Timeout) <0.010082>
25049 0.010165 select(0, [], [], [], {0, 20000}) = 0 (Timeout) <0.020146>
25049 0.020223 select(0, [], [], [], {0, 30000}) = 0 (Timeout) <0.030137>
25049 0.030220 select(0, [], [], [], {0, 40000}) = 0 (Timeout) <0.040108>
25049 0.040188 select(0, [], [], [], {0, 50000}) = 0 (Timeout) <0.050147>
25049 0.050229 select(0, [], [], [], {0, 60000}) = 0 (Timeout) <0.060154>
25049 0.060238 select(0, [], [], [], {0, 70000}) = 0 (Timeout) <0.070168>
25049 0.070260 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080172>
25049 0.080254 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080185>
25049 0.080282 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080144>
25049 0.080226 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080176>
25049 0.080257 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080138>
25049 0.080218 select(0, [], [], [], {0, 80000}) = 0 (Timeout) <0.080180>
總結:
1.主要瞭解process allocation latch與其它的latch不同.
2.大概瞭解一下latch spin的方法.
3.一般不會有這樣需求定製化這樣的操作.
4.腳本peek.sh,latch_free.sql,可以在鏈接http://blog.itpub.net/267265/viewspace-2641497/,我僅僅修改peek長度60.
latch_free.sql腳本原始鏈接也可以在http://andreynikolaev.wordpress.com/下載.
5.另外說明一點可能從9i開始latch spin機制發生很大變化,一般看不到指數回退sleep.實際上spin 一定數量20000次,然後休眠semop函數.
等待阻塞會話執行semctl操作.
shared latch spin數量是2*_spin_count.
SYS@book> select CLASS_KSLLT,count(*) from x$kslltr group by CLASS_KSLLT;
CLASS_KSLLT COUNT(*)
----------- ----------
2 1
0 581
--//其它latch都是類0,但是它使用後面的sleepN參數.摘要一些鏈接:
http://andreynikolaev.wordpress.com/2011/01/18/spin-tales-part-3-non-standard-latch-classes-in-oracle-9-2-11g/
Row 0 values and _latch_class_0 parameter have the special meaning. Class 0 is the only latch class that uses wait
until post. Spin count of standard class exclusive latches is determined by the SPIN column of row 0. SLEEP… columns of
class 0 seem not to be used at all.
--//行0值和_latch_class_0參數具有特殊意義。類0是唯一使用等待到POST的閂鎖類。標準類排他性閂鎖的自旋計數由第0行的自旋列確
--//定。sleep…0類的列似乎根本不被使用。
http://andreynikolaev.wordpress.com/2010/12/16/hidden-latch-wait-revolution-that-we-missed/
This post is about new latch wait post mechanics which appeared in Oracle 9.2. Contemporary latch spins and waits until
posted only once. Since 2002 for almost a decade, we tuned the latch contention assuming its exponential backoff
behavior. I even mistakenly tried to estimate the number of sleeps as a logarithm of "latch free" wait event duration .
This is why I named this post: "Hidden latch revolution that we missed"
--//這篇文章是關於Oracle 9.2中出現的新鎖存等待後機制。當代閂鎖旋轉和等待，直到只張貼一次。自2002年以來，我們對鎖存競爭進
--//行了近十年的調整，假設其指數退避行為。我甚至錯誤地嘗試將睡眠的數量估計為"閂鎖空閑"等待事件持續時間的對數。這就是為什
--//麽我把這篇文章命名為："我們錯過的隱藏鎖革命"。
6.如何測試阻塞時spin的數量一直是我很頭疼的問題,對方使用Dtrace僅僅工作在Solaris平臺.在linux下視乎沒有這樣的工具.
7.剩下的問題還給慢慢探究spin次數如何獲得.
