[20190416]process allocation latch.txt
[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次数如何获得.
上一篇： 2018欧美19禁片排行榜 尺度最大的欧美电影推荐
下一篇： 花钱就能查到微信聊天记录：真的吗？