ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20201116]测试CURSOR_SPACE_FOR_TIME(10g)(补充).txt

[20201116]测试CURSOR_SPACE_FOR_TIME(10g)(补充).txt

原创 Oracle 作者：lfree 时间：2020-11-16 09:16:13 0 删除 编辑

[20201116]测试CURSOR_SPACE_FOR_TIME(10g)(补充).txt

--//有点好奇测试看看CURSOR_SPACE_FOR_TIME是否能提高性能在10g下，11g不支持已经废除了这个参数。
--//补充测试仅仅1个用户执行的情况，这样没有阻塞。

1.环境：
SYS@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SYS@test> alter system set cursor_space_for_time=true scope=spfile;
System altered.
--//重启略。

SYS@test> show parameter cursor_space_for_time
NAME                  TYPE    VALUE
--------------------- ------- ------
cursor_space_for_time boolean TRUE

2.建立测试脚本：
SCOTT@test> create table job_times (sid number, sessionid number,time_ela number,method varchar2(20));
Table created.

$ cat m2.txt
set verify off
--//host sleep $(echo &&3/50 | bc -l )
insert into job_times values ( sys_context ('userenv', 'sid') ,sys_context ('userenv', 'sessionid'),dbms_utility.get_time ,'&&2') ;
commit ;
declare
v_id number;
v_d date;
m_rowid varchar2(20);
m_data varchar2(32);
begin
--//   m_rowid := '&3';
        v_id := &3;
    for i in 1 .. &&1 loop
        select dname into m_data from dept where deptno = v_id ;
--//    select ename into m_data from emp where rowid =m_rowid ;
    end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and sessionid=sys_context ('userenv', 'sessionid') and method='&&2';
commit;
quit

3.测试:
$ zzdate ;sqlplus -s -l scott/btbtms @m2.txt 1e6 cursor1=true 10 >/dev/null ;zzdate
trunc(sysdate)+08/24+36/1440+35/86400 == 2020/11/16 08:36:35
trunc(sysdate)+08/24+36/1440+46/86400 == 2020/11/16 08:36:46

--//同时在另外的会话先执行：
SYS@test> @ tpt/snapper ash 60 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 60 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
    15% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU
     2% |    1 |                 |           | ON CPU                              | ON CPU
     2% |    1 | 0dgcvpaqt1gp7   | 0         | ON CPU                              | ON CPU
--  End of ASH snap 1, end=2020-11-16 08:37:33, seconds=60, samples_taken=99
PL/SQL procedure successfully completed.

--//测试cursor_space_for_time＝false的情况。
SYS@test> alter system reset cursor_space_for_time scope=spfile sid='*';
System altered.
--//重启略。
SYS@test> show parameter cursor_space_for_time
NAME                  TYPE    VALUE
--------------------- ------- -----
cursor_space_for_time boolean FALSE

$ zzdate ;sqlplus -s -l scott/btbtms @m2.txt 1e6 cursor1=false 10 >/dev/null ;zzdate
trunc(sysdate)+08/24+32/1440+50/86400 == 2020/11/16 08:32:50
trunc(sysdate)+08/24+33/1440+06/86400 == 2020/11/16 08:33:06

--//同时在另外的会话先执行：
SYS@test> @ tpt/snapper ash 60 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 60 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
    17% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU
     5% |    1 |                 |           | ON CPU                              | ON CPU
     4% |    1 | 0dgcvpaqt1gp7   | 0         | ON CPU                              | ON CPU
     1% |    1 |                 | 0         | ON CPU                              | ON CPU
--  End of ASH snap 1, end=2020-11-16 08:33:49, seconds=60, samples_taken=100

SCOTT@test> 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)
-------------------- ---------- ---------------------- -------------
cursor1=true                  1                   1144          1144
cursor1=false                 1                   1579          1579
cursor=true                  50                   3552        177595
cursor=false                 50                   4801        240068

--//可以发现在没有阻塞的情况下更快,设置cursor_space_for_time=true，减少了申请和释放latch(也许是mutex的次数)。
--//因为前面看到的等待事件是cursor: pin S。
--//许多开发很不重视这些细节，实际上大量重复相同的sql语句一样导致运行"缓慢".

--//补充测试cursor_space_for_time=false ,ash_wait_chains10g收集到的情况。
$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/btbtms @m2.txt 1e6 cursor50=false 10 >/dev/null ;zzdate
trunc(sysdate)+09/24+05/1440+03/86400 == 2020/11/16 09:05:03
trunc(sysdate)+09/24+05/1440+56/86400 == 2020/11/16 09:05:56

SYS@test> @ tpt/snapper ash 60 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 60 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
  2260% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU
   594% |    1 |                 |           | ON CPU                              | ON CPU
   473% |    1 | 0dgcvpaqt1gp7   | 0         | ON CPU                              | ON CPU
   275% |    1 |                 |           | cursor: pin S                       | Other
   213% |    1 | 56r5sd49t3jrv   | 0         | cursor: pin S                       | Other
    10% |    1 | 56r5sd49t3jrv   |           | ON CPU                              | ON CPU
     6% |    1 |                 | 0         | ON CPU                              | ON CPU
     3% |    1 | 0dgcvpaqt1gp7   |           | ON CPU                              | ON CPU
     1% |    1 | 00000009t3jrv   | 0         | ON CPU                              | ON CPU
     1% |    1 |                 |           | log file sync                       | Commit
--  End of ASH snap 1, end=2020-11-16 09:06:02, seconds=60, samples_taken=99
PL/SQL procedure successfully completed.

SYS@test> @ tpt/ash/ash_wait_chains10g username||':'||program2||event2 1=1 trunc(sysdate)+09/24+05/1440+03/86400 trunc(sysdate)+09/24+05/1440+56/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- ---------------------------------
  88%        2026       38.2 -> SCOTT:(sqlplus) ON CPU
  12%         278        5.2 -> SCOTT:(sqlplus) cursor: pin S
   0%           1          0 -> SYS:(sqlplus) ON CPU

--//补充测试cursor_space_for_time=true ,ash_wait_chains10g收集到的情况。

$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/btbtms @m2.txt 1e6 cursor50=true 10 >/dev/null ;zzdate
trunc(sysdate)+09/24+11/1440+35/86400 == 2020/11/16 09:11:35
trunc(sysdate)+09/24+12/1440+16/86400 == 2020/11/16 09:12:16

SYS@test> @ tpt/snapper ash 60 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 60 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
  2174% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU
   451% |    1 | 0dgcvpaqt1gp7   | 0         | ON CPU                              | ON CPU
   329% |    1 |                 |           | ON CPU                              | ON CPU
     6% |    1 | 56r5sd49t3jrv   |           | ON CPU                              | ON CPU
     6% |    1 | 56r5sd49t3jrv   | 0         | latch: cache buffers chains         | Concurrency
     6% |    1 |                 | 0         | ON CPU                              | ON CPU
     5% |    1 | gfcyhuuv233x5   | 0         | enq: US - contention                | Other
     2% |    1 | 0dgcvpaqt1gp7   |           | cursor: pin S wait on X             | Concurrency
     1% |    1 | 0000000000000   |           | ON CPU                              | ON CPU
--  End of ASH snap 1, end=2020-11-16 09:12:35, seconds=60, samples_taken=98
PL/SQL procedure successfully completed.

SYS@test> @ tpt/ash/ash_wait_chains10g username||':'||program2||event2 1=1  trunc(sysdate)+09/24+11/1440+35/86400 trunc(sysdate)+09/24+12/1440+16/86400

-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------
 100%        1706       41.6 -> SCOTT:(sqlplus) ON CPU
   0%           1          0 -> SCOTT:(sqlplus) log file sync


SCOTT@test> 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)
-------------------- ---------- ---------------------- -------------
cursor1=true                  1                   1144          1144
cursor1=false                 1                   1579          1579
cursor50=true                50                   3550        177500
cursor=true                  50                   3552        177595
cursor50=false               50                   4637        231856
cursor=false                 50                   4801        240068

6 rows selected.

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

0
0
上一篇： [20201113]测试CURSOR_SPACE_FOR_TIME(10g).txt
下一篇： [20201116]测试CURSOR_SPACE_FOR_TIME=false(11g).txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969289

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593