[20190402]對比_mutex_wait_scheme不同模式cpu消耗.txt

--//前幾天做了sql語句在mutexes上的探究.今天對比不同_mutex_wait_scheme模式cpu消耗.

1.環境:

SYS@book> @ hide mutex

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

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

_mutex_spin_count Mutex spin count TRUE 255 255

_mutex_wait_scheme Mutex wait scheme TRUE 2 2

_mutex_wait_time Mutex wait time TRUE 1 1

--//注:_mutex_wait_time=1,相當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

$ cat m2.txt

set verify off

column a noprint new_value v_a;

--select mod ( &&3 ,3) a from dual ;

--alter session set optimizer_index_cost_adj= &&3;

host sleep $(echo &&3/50| bc -l )

insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;

commit ;

declare

v_id number;

v_d date;

begin

for i in 1 .. &&1 loop

--select 1 into v_id from dual ;

--select sysdate into v_d from dual ;

select deptno into v_id from dept where deptno=10;

end loop;

end ;

/

update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method='&&2';

commit;

--quit

$ seq 150 | xargs -I {} -P 150 bash -c "sqlplus -s -l scott/book @m2.txt 1e6 f2_150 {} >/dev/null"

SYS@book> @ mutexy 6 a31kd5tkdvvmm

HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100

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

1692266099 2263154 4.6431E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 2183544 4.4733E+10 kksfbc [KKSCHLFSP2] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 5256 600010918 kksfbc [KKSCHLPIN1] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 6 45955498 kksHeapReadUnlock [KKSCHLRDUP] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

--//MUTEX_ADDR=000000007C88E330.

2.測試一:

--//測試_mutex_wait_scheme=2的情況:

--//session 1:

SCOTT@book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

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

295 5 65222 DEDICATED 65223 21 3 alter system kill session '295,5' immediate;

--//session 2:

SYS@book> oradebug setmypid

Statement processed.

SYS@book> oradebug peek 0x000000007C88E330 8

[07C88E330, 07C88E338) = 00000000 00000000

SYS@book> oradebug poke 0x000000007C88E330 8 0x0000000200000127

BEFORE: [07C88E330, 07C88E338) = 00000000 00000000

AFTER: [07C88E330, 07C88E338) = 00000127 00000002

--//session 1:

SCOTT@book> @ m2.txt 1 c1 0

1 row created.

Commit complete.

--//掛起!!

$ top -p 65223

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

65223 oracle 20 0 857m 29m 25m S 0.3 0.0 0:00.41 oracle

--//CPU消耗0.3.

--//另外我執行如下:

SYS@book> @ mutexy 5 a31kd5tkdvvmm

HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100

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

1692266099 2263154 4.6431E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 2183544 4.4733E+10 kksfbc [KKSCHLFSP2] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 19266 600013714 kksfbc [KKSCHLPIN1] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 6 45955498 kksHeapReadUnlock [KKSCHLRDUP] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

--//等一會...

SYS@book> @ mutexy 5 a31kd5tkdvvmm

HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100

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

1692266099 2263154 4.6431E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 2183544 4.4733E+10 kksfbc [KKSCHLFSP2] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 26386 600013714 kksfbc [KKSCHLPIN1] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 6 45955498 kksHeapReadUnlock [KKSCHLRDUP] Cursor Pin 000000007C88E330 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

--//僅僅LOCATION=kksfbc [KKSCHLPIN1],SUM_SLEEPS數量在增加.

AFTER: [07C88E330, 07C88E338) = 00000127 00000002

SYS@book> oradebug poke 0x000000007C88E330 8 0x0

BEFORE: [07C88E330, 07C88E338) = 00000127 00000002

AFTER: [07C88E330, 07C88E338) = 00000000 00000000

3.測試二:

--//測試_mutex_wait_scheme=1的情況:

--//基本重複上面的步驟,不再列出執行步驟.

SYS@book> alter system set "_mutex_wait_scheme"=1 scope=memory;

System altered.

$ top -p 65223

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

65223 oracle 20 0 857m 29m 25m S 3.0 0.0 0:01.29 oracle

--//CPU消耗3.

4.測試三:

--//測試_mutex_wait_scheme=1的情況:

--//基本重複上面的步驟,不再列出執行步驟.

SYS@book> alter system set "_mutex_wait_scheme"=0 scope=memory;

System altered.

$ top -p 65223

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

65223 oracle 20 0 857m 29m 25m S 39.9 0.0 0:06.99 oracle

--//CPU消耗39.9

5.總結:

--//畫一個表格如下:

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

_mutex_wait_scheme CPU使用率描述

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

2 .3 2秒時間內,semtimedop 182次 getrusage 2次 _mutex_spin_count=255

12 2秒時間內,semtimedop 167次 getrusage 2次 _mutex_spin_count=65535

1 3 2秒時間內, select 16XX次(每次呼叫0.001秒).,getrusage 2次

0 39.9 呼叫99次sched_yield,然後1次seelct(每次呼叫0.001秒).

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

--//注:這是我當前硬體條件下的測試結果,而且我一直阻塞sql語句執行並且_mutex_wait_time=1的情況.

--//一些測試資料參考連結:

http://blog.itpub.net/267265/viewspace-2639675/

http://blog.itpub.net/267265/viewspace-2640003/

[20190402]對比_mutex_wait_scheme不同模式cpu消耗.txt

相關文章