[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/