[20190329]探究sql语句相关mutexes补充2.txt
--//昨天测试sql语句相关mutexes,看看如果出现多个子光标的情况.
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_caching= &v_a;
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
--//这样建立3个子光标.
2.测试:
$ seq 150 | xargs -I {} -P 150 bash -c "sqlplus -s -l scott/book @m2.txt 1e6 f2_150 {} >/dev/null"
$ seq 150 | xargs -I {} -P 150 bash -c "sqlplus -s -l scott/book @m2.txt 1e6 g2_150 {} >/dev/null"
SYS@book> @ mutexy 6
HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100
---------- ---------- ---------- ------------------------------ -------------------- ---------------- ------------- -------- ---------------------------------------
1692266099 14495552 3.6572E+11 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007BD3EC98 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10
1692266099 7893620 2.0592E+11 kksfbc [KKSCHLFSP2] Cursor Pin 000000007BD3EC98 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10
1692266099 4307308 8.8088E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007BD3F260 a31kd5tkdvvmm S