[20190416]process allocation latch.txt

lfree發表於2019-04-17

[20190416]process allocation latch.txt


--//看連結:

--//裡面提到:

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指令碼原始連結也可以在下載.

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引數.摘要一些連結:


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類的列似乎根本不被使用。



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次數如何獲得.


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2641608/,如需轉載,請註明出處,否則將追究法律責任。

相關文章