[20190416]process allocation latch.txt

╰+哭是因爲堅強的太久メ 2022-01-10 02:55 171阅读 0赞

\[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次数如何获得.

转载于:https://www.cnblogs.com/lfree/p/10721811.html