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