[20190419]shared latch spin count.txt

--//昨天測試exclusive latch spin count = 20000(預設).

--//今天測試shared latch spin count的情況,看了一些文章測試等於2 *_spin_count.

--//有了昨天的測試經驗,今天測試應該相對容易一些,不過shared latch有兩種模式:

For the shared latches Oracle 10g uses kslgetsl(laddr, wait, why, where, mode) function. Oracle 11g has kslgetsl_w()

function with the same interface, but internally uses ksl_get_shared_latch(). Like in my previous post, I guess the

meaning of kslgetsl() arguments as:

--//對於共享鎖存，Oracle 10g使用kslgetsl(laddr，wait，why，where，mode)函式。Oracle 11g具有相同介面的kslgetsl_w()函式，但

--//在內部使用ksl_get_share_latch()。與上一篇文章一樣，我認為kslgetsl()引數的含義是：

--//注:我以前一直以為還是kslgetsl,原來11g已經改為kslgetsl_w,不過內部使用還是ksl_get_shared_latch().

laddress -- address of latch in SGA

wait -- flag. If not 0, then willing-to-wait latch get

why -- context why the latch is acquired at this where.

where -- location from where the latch is acquired (x$ksllw.indx)

And the last one is:

mode – Exclusive or shared mode

the mode argument took only two values:

8 -- "SHARED"

16 -- "EXCLUSIVE"

--//當時自己的總結:

--//A. S mode 下: peek記錄的前4位持有S mode的數量.後4位是0x0. (這裡針對的64位的系統)

--//B. S mode 下,如果出現X mode,peek記錄的前4位持有S mode的數量.後4位是0x40000000.

--// 一旦X mode持有,前4位持有會話PID號,後4位0x20000000.

--//C. X mode 持有,會導致順序的S mode 序列化.從調優角度講這是最"可怕"的事情.

--//D. 從以上測試可以看出 shared latch最佳化的重點就是減少X mode出現的頻次.

--//也就是測試要至少測試三種SX,XX,XS情況.是否這些情況下spin count是否一樣.

--//連結 :

sqlplus /nolog @latch_spin_trace.sql 102

...

LATCH_FUNC ADDR LNAME

---------- --------- --------------------------------------------------

kslgetsl 50009BAC gcs_partitioned_table_hash

...

pid: 7270

kslgetsl - KSL GET Shared Latch

kslgess - wait latch get

kslskgs - shared latch spin get

sskgslspin - spinning function

sskgslspin

...

--//說明一下:作者測試環境10g,10g shared latch呼叫函式是kslgetsl,11g shared latch呼叫函式是kslgetsl_w.

--//latch spin 使用sskgslspin函式呼叫,可是linux下使用intel cpu並沒有對應的oracle內部函式.

(gdb) b sskgslspin

Function "sskgslspin" not defined.

Make breakpoint pending on future shared library load? (y or [n]) y

Breakpoint 3 (sskgslspin) pending.

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> @ hide spin_count

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

----------------- ---------------------------------- ------------- ------------- ------------

_mutex_spin_count Mutex spin count TRUE 255 255

_spin_count Amount to spin waiting for a latch TRUE 2000 2000

$ cat shared_latch.txt

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

--//connect / as sysdba

col laddr new_value laddr

col vmode new_value vmode

select decode(lower('&&5'),'s',8,'x',16,'8',8,'16',16) vmode from dual ;

SELECT addr laddr FROM v$latch_parent WHERE NAME='&&1';

oradebug setmypid

oradebug call kslgetsl_w 0x&laddr &&2 &&3 &&4 &vmode

host sleep &&6

oradebug call kslfre 0x&laddr

--//exit

--//注:我前幾天的測試指令碼有connect / as sysdba,exit這兩行,我為了調式方便,先註解這2行,避免反覆退出進入會話.

2.測試:

--//選擇一個shared latch測試,我選擇"test shared non-parent l0" latch測試:

SYS@book> select * from shared_latches where name='test shared non-parent l0';

VERSION LATCH# NAME S

---------- ------ ------------------------- -

11.2.0.4.0 6 test shared non-parent l0 Y

--//首先測試 XX 情況.

--//session 1:

SYS@book> @ shared_latch.txt "test shared non-parent l0" 1 1 2 x 100000

VMODE

----------

16

LADDR

----------------

0000000060009A18

Statement processed.

Function returned 1

--//後面的引數是sleep的秒數,數值大一些,避免跟蹤時退出.想繼續按ctrl+c就可以中斷sleep.

--//session 2:

SYS@book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

44 23 30318 DEDICATED 30319 27 10 alter system kill session '44,23' immediate;

--//記下SPID=30319.在開啟一個終端視窗執行如下:

--//暫且稱為window 3:

$ rlwrap gdb -p 30319

--//session 2:

SYS@book> @ shared_latch.txt "test shared non-parent l0" 1 3 4 x 1

--//掛起!!

--//windows 3:

(gdb) c

Continuing.

--//session 2:

SYS@book> @shared_latch.txt "test shared non-parent l0" 1 3 4 x 1

VMODE

----------

16

LADDR

----------------

0000000060009A18

Statement processed.

--//回到window 3,按ctrl+c中斷:

(gdb) c

Continuing.

^C

Program received signal SIGINT, Interrupt.

0x00000037990d6407 in semop () from /lib64/libc.so.6

(gdb) bt 8

#0 0x00000037990d6407 in semop () from /lib64/libc.so.6

#1 0x0000000009809c0f in sskgpwwait ()

#2 0x00000000098089ce in skgpwwait ()

#3 0x0000000000a86d3f in kslgess ()

#4 0x00000000093faca5 in ksl_get_shared_latch ()

#5 0x0000000004a2c53e in kslgetsl_w ()

#6 0x0000000007d74142 in skdxcall ()

#7 0x00000000076c96aa in ksdxcall ()

(More stack frames follow...)

--//可以確定函式呼叫的堆疊或者稱為順序,當前停在semop睡眠上,可以發現呼叫kslgetsl_w後,ksl_get_shared_latch,

--//緊接著的是kslgess.這樣猜測spin計數在呼叫kslgess或者ksl_get_shared_latch函式里面.

2.重複前面測試,在gdb下設定斷點:

--//在session 1按ctrl+c,退出window 3的gdb程式,重新執行gdb.

--//window 3:

$ rlwrap gdb -p 30319

(gdb) break kslgess

Breakpoint 1 at 0xa865ca

--//設定斷點在kslgess函式呼叫上.然後在session 1,2分別執行(後面不再說明):

--//再次掛起!在window 3,執行如下:

--//如果我執行ni 1000應該不會錯過什麼,可以這時看暫存器應該猜測spin count在那個暫存器中.

--//跟蹤半天不對,看了連結

--//應該在kslskgs函式中.

(gdb) break kslskgs

Breakpoint 1 at 0xa874fa

(gdb) c

Continuing.

Breakpoint 1, 0x0000000000a874fa in kslskgs ()

(gdb) ni 100

..

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875e3 0xa875e3 <kslskgs+237>

(gdb) ni

0x0000000000a875be in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875be 0xa875be <kslskgs+200>

(gdb) ni

0x0000000000a875c8 in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875c8 0xa875c8 <kslskgs+210>

(gdb) ni

0x0000000000a875cb in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875cb 0xa875cb <kslskgs+213>

(gdb) ni

0x0000000000a875d1 in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875d1 0xa875d1 <kslskgs+219>

(gdb) ni

0x0000000000a875d4 in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875d4 0xa875d4 <kslskgs+222>

(gdb) ni

0x0000000000a875da in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875da 0xa875da <kslskgs+228>

(gdb) ni

0x0000000000a875dd in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875dd 0xa875dd <kslskgs+231>

(gdb) ni

0x0000000000a875df in kslskgs ()

(gdb) info regi r13 rip

r13 0x764 1892

rip 0xa875df 0xa875df <kslskgs+233>

(gdb) ni

0x0000000000a875e3 in kslskgs ()

(gdb) info regi r13 rip

r13 0x763 1891

rip 0xa875e3 0xa875e3 <kslskgs+237>

--//可以確定迴圈開頭在0xa875be地址.也是9條指令.

(gdb) disassemble kslskgs

--//反彙編看看.

...

0x0000000000a875be <kslskgs+200>: mov $0x4000000000000000,%rsi

0x0000000000a875c8 <kslskgs+210>: cmp %rsi,%rbx

0x0000000000a875cb <kslskgs+213>: je 0xa876db <kslskgs+485>

0x0000000000a875d1 <kslskgs+219>: test %rbx,%rbx

0x0000000000a875d4 <kslskgs+222>: je 0xa876db <kslskgs+485>

0x0000000000a875da <kslskgs+228>: mov (%r14),%rbx

0x0000000000a875dd <kslskgs+231>: pause

0x0000000000a875df <kslskgs+233>: add $0xffffffffffffffff,%r13d

0x0000000000a875e3 <kslskgs+237>: jne 0xa875be <kslskgs+200>

3.重複前面測試,先寫出gdb指令碼:

$ cat spin_s.gdb

break kslgetl

commands

silent

printf "kslgetl %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break kslges

commands

silent

printf "kslges %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break skgpwwait

commands

silent

printf "skgpwwait %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break sskgpwwait

commands

silent

printf "sskgpwwait %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break semop

commands

silent

printf "semop %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break *0x93f9ddc

commands

silent

printf " spin count loop: %d %d %x\n", $rax,$rcx,$rip

c

end

##今天加入的內容

break ksl_get_shared_latch

commands

silent

printf "ksl_get_shared_latch laddr:%x, willing:%d, where:%d, why:%d, mode:%d\n", $rdi, $rsi, $rdx, $rcx, $r8

c

end

break kslgess

commands

silent

printf "kslgess %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break kslskgs

commands

silent

printf "kslskgs %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx

c

end

break *0xa875be

commands

silent

printf " spin count loop: %d %x\n", $r13,$rip

c

end

--//重複測試:

--//window 3:

$ rlwrap gdb -p 30319 -x spin_s.gdb

...

Breakpoint 1 at 0x93f97a8

Breakpoint 2 at 0x93f9b74

Breakpoint 3 at 0x9808932

Breakpoint 4 at 0x9809840

Breakpoint 5 at 0x37990d6400

Breakpoint 6 at 0x93f9ddc

Breakpoint 7 at 0x93faa36

Breakpoint 8 at 0xa865ca

Breakpoint 9 at 0xa874fa

Breakpoint 10 at 0xa875be

(gdb) c

Continuing.

ksl_get_shared_latch laddr:85f800a8, willing:1, where:-2043089904, why:95, mode:8

ksl_get_shared_latch laddr:85f800a8, willing:1, where:-2043089904, why:96, mode:16