ITPub博客

首页 > 数据库 > Oracle > [20190320]测试相同语句遇到导致cursor pin S的情况.txt

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

原创 Oracle 作者:lfree 时间:2019-03-20 15:28:09 0 删除 编辑

[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                       150                  19380       2907006


--//奇怪的是,测试实际上测试1反而慢一点.从这个测试可以看出,如果如果应用真有大量语句出现cursor争用,通过打散语句的执行,

--//可能未必能提高执行效率.


--//test1的awr报表:

              Snap Id      Snap Time      Sessions Curs/Sess

            --------- ------------------- -------- ---------

Begin Snap:      1681 20-Mar-19 09:53:01        27       1.2

  End Snap:      1682 20-Mar-19 09:56:23        28       1.2

   Elapsed:                3.37 (mins)

   DB Time:              497.85 (mins)


...

Top 10 Foreground Events by Total Wait Time

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                            Tota    Wait   % DB           

Event                                 Waits Time Avg(ms)   time Wait Class

------------------------------ ------------ ---- ------- ------ ----------

latch: shared pool                  233,755 18.6      79   62.2 Concurrenc

DB CPU                                      4751           15.9           

library cache: mutex X                  828 13.7      17     .0 Concurrenc

cursor: pin S wait on X                  68  1.4      20     .0 Concurrenc

library cache load lock                  94  1.1      12     .0 Concurrenc

log file sync                           141   .5       4     .0 Commit    

wait list latch free                     50   .3       6     .0 Other     

enq: SQ - contention                      2    0      10     .0 Configurat

library cache lock                        2    0       8     .0 Concurrenc

SQL*Net message to client             2,560    0       0     .0 Network   


--//出现了latch: shared pool大量争用.反而测试2使用mutex的效率更高.


--//test2的awr报表:

             Snap Id      Snap Time      Sessions Curs/Sess

            --------- ------------------- -------- ---------

Begin Snap:      1682 20-Mar-19 09:56:23        28       1.2

  End Snap:      1683 20-Mar-19 09:59:40        28       1.2

   Elapsed:                3.28 (mins)

   DB Time:              484.76 (mins)


...

Top 10 Foreground Events by Total Wait Time

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                            Tota    Wait   % DB           

Event                                 Waits Time Avg(ms)   time Wait Class

------------------------------ ------------ ---- ------- ------ ----------

cursor: pin S                       585,684 12.1      21   41.6 Concurrenc

DB CPU                                      4611           15.9           

library cache: mutex X                  525  8.6      16     .0 Concurrenc

latch: shared pool                      117  1.5      13     .0 Concurrenc

latch free                               45  1.3      28     .0 Other     

log file sync                           129   .5       4     .0 Commit    

cursor: pin S wait on X                  44   .4       9     .0 Concurrenc

library cache load lock                  57   .3       6     .0 Concurrenc

row cache lock                           18   .2      10     .0 Concurrenc

enq: SQ - contention                      3    0      11     .0 Configurat


--//对比测试2的cursor: pin S使用12.1秒.而测试1的latch: shared pool使用18.6秒,差距并不大.

--//可以看出使用oracle使用mutex效率更高.


--//另外从一个侧面说明,如果应用大量重复语句执行出现cursor: pin S争用,通过分散的方式也许更加并不是最佳的.

--//减少语句的执行次数才是比较正确的处理问题方式,或者找到为什么执行次数这么高的原因.


--//我又重复测试1次.test1修改testa,test2修改testb.


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)

-------------------- ---------- ---------------------- -------------

test2                       150                  19380       2907006

testb                       150                  19648       2947223

testa                       150                  19884       2982666

test1                       150                  19897       2984502


--//结论依旧.

--//如果减少测试用户连接数量呢?测试并发用户50的情况:

$ cat aa3.txt

exec dbms_workload_repository.create_snapshot();

host seq 50 | xargs -I{} -P 50 bash -c  "sqlplus -s -l scott/book @mutex.sql  1e6 test50a {} >/dev/null"

exec dbms_workload_repository.create_snapshot();

host seq 50 | xargs -I{} -P 50 bash -c  "sqlplus -s -l scott/book @mutex1.sql 1e6 test50b {} >/dev/null"

exec dbms_workload_repository.create_snapshot();


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)

-------------------- ---------- ---------------------- -------------

test50b                      50                   6437        321825

test50a                      50                   6791        339554

test2                       150                  19380       2907006

testb                       150                  19648       2947223

testa                       150                  19884       2982666

test1                       150                  19897       2984502

6 rows selected.


--//你可以发现在并发用户50的情况下,情况不变,结论依旧.改成并发用户10的情况呢?

--//还可以发现现在同样的工作量,50个并发的情况下,6X秒就可以完成.


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)

-------------------- ---------- ---------------------- -------------

testi10b                     10                   1872         18724

testi10a                     10                   2003         20028

test50b                      50                   6437        321825

test50a                      50                   6791        339554

test2                       150                  19380       2907006

testb                       150                  19648       2947223

testa                       150                  19884       2982666

test1                       150                  19897       2984502

8 rows selected.


--//有点奇怪为什么测试1会出现大量的latch: shared pool.

--//这个测试有点像按下葫芦起了瓢,也说明任何问题都给辩证的看.


总结:

--//在测试前我一直以为测试1会块一些,实际情况正好相反.

--//不过为什么这样,我还不是很清楚....


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2638857/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2854
  • 访问量
    6642511