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

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