[20190320]测试相同语句遇到导致cursor pin S的情况.txt

--//前面测试链接:http://blog.itpub.net/267265/viewspace-2636342/
--//各个会话执行语句相同的,很容易出现cursor: pin S等待事件.看看如果各个会话执行的语句不同.
--//测试结果如何呢?

-//后记:补充说明测试不严谨,请参考链接:http://blog.itpub.net/267265/viewspace-2639097/

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

2.建立测试脚本:
create table job_times (sid number, time_ela number,method varchar2(20));

$ cat mutex.sql
set verify off
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;
declare
v_id number;
v_d date;
begin
    for i in 1 .. &&1 loop
        select /*+ &&3 */ sysdate from into v_date dual;
        --select  sysdate from into v_date dual;
    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

$ cat mutex1.sql
set verify off
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;
declare
v_id number;
v_d date;
begin
    for i in 1 .. &&1 loop
        --select /*+ &&3 */ sysdate from into v_date dual;
        select  sysdate from into v_date dual;
    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,产生每个会话执行语句不同,对比看看.

3.测试:
exec dbms_workload_repository.create_snapshot();
host seq 150 | xargs -I{} -P 150 bash -c  "sqlplus -s -l scott/book @mutex.sql  1e6 test1 {} >/dev/null"
exec dbms_workload_repository.create_snapshot();
host seq 150 | xargs -I{} -P 150 bash -c  "sqlplus -s -l scott/book @mutex1.sql 1e6 test2 {} >/dev/null"
exec dbms_workload_repository.create_snapshot();

--//测试1,执行时等待事件集中在latch: shared pool.
--//测试2,执行时等待事件集中在cursor: pin S.

SCOTT@book> select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
test1                       150                  19897       2984502
test2