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

来源：这里教程网时间：2026-03-03 16:16:43 作者：

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

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推