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