[20190419]shared latch spin count.txt

cuixun7780

于 2019-04-19 11:15:49 发布

阅读量142

点赞数

[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是否一样.

--//链接 :http://andreynikolaev.wordpress.com/2011/01/14/spin-tales-part-2-shared-latches-in-oracle-9-2-11g/

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

----------

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

----------

LADDR

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

0000000060009A18

Statement processed.

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

(gdb) c

Continuing.

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在那个寄存器中.

--//跟踪半天不对,看了链接https://fritshoogland.wordpress.com/2015/07/17/oracle-12-and-latches-part-2/

--//应该在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

end

break kslges

commands

silent

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

end

break skgpwwait

commands

silent

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

end

break sskgpwwait

commands

silent

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

end

break semop

commands

silent

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

end

break *0x93f9ddc

commands

silent

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

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

end

break kslgess

commands

silent

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

end

break kslskgs

commands

silent

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

end

break *0xa875be

commands

silent

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

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