[20201113]测试CURSOR_SPACE_FOR_TIME(10g).txt

[20201113]测试CURSOR_SPACE_FOR_TIME(10g).txt --//有点好奇测试看看CURSOR_SPACE_FOR_TIME是否能提高性能在10g下，11g不支持已经废除了这个参数。 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 ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/btbtms @m2.txt 1e6 cursor=true 10 >/dev/null ;zzdate trunc(sysdate)+11/24+02/1440+07/86400 == 2020/11/13 11:02:07 trunc(sysdate)+11/24+02/1440+47/86400 == 2020/11/13 11:02:47 --//同时在另外的会话执行： 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 ----------------------------------------------------------------------------------------------------   2176% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU    455% |    1 | 0dgcvpaqt1gp7   | 0         | ON CPU                              | ON CPU    327% |    1 |                 |           | ON CPU                              | ON CPU      8% |    1 | 56r5sd49t3jrv   |           | ON CPU                              | ON CPU      2% |    1 |                 | 0         | ON CPU                              | ON CPU      1% |    1 | 95mdkn5g8v9za   | 0         | ON CPU                              | ON CPU      1% |    1 |                 |           | log file sync                       | Commit --  End of ASH snap 1, end=2020-11-13 11:03:05, seconds=60, samples_taken=98 PL/SQL procedure successfully completed. 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) -------------------- ---------- ---------------------- ------------- cursor=true                  50                   3552        177595 SYS@test> alter system reset cursor_space_for_time scope=spfile sid='*'; System altered. --//重启略。奇怪10g必须加上sid='*',不然报错。 SCOTT@test> alter system reset cursor_space_for_time scope=spfile; alter system reset cursor_space_for_time scope=spfile                                                     * ERROR at line 1: ORA-00905: missing keyword SYS@test> show parameter cursor_space_for_time NAME                  TYPE    VALUE --------------------- ------- ----- cursor_space_for_time boolean FALSE $ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/btbtms @m2.txt 1e6 cursor=false 10 >/dev/null ;zzdate trunc(sysdate)+11/24+05/1440+17/86400 == 2020/11/13 11:05:17 trunc(sysdate)+11/24+06/1440+11/86400 == 2020/11/13 11:06:11 --//同时在另外的会话执行： 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 ----------------------------------------------------------------------------------------------------   2419% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU    606% |    1 |                 |           | ON CPU                              | ON CPU    461% |    1 | 0dgcvpaqt1gp7   | 0         | ON CPU                              | ON CPU    299% |    1 |                 |           | cursor: pin S                       | Other    153% |    1 | 56r5sd49t3jrv   | 0         | cursor: pin S                       | Other     19% |    1 | 56r5sd49t3jrv   |           | ON CPU                              | ON CPU     15% |    1 | 56r5sd49t3jrv   | 0         | latch: session allocation           | Other     10% |    1 | 0dgcvpaqt1gp7   |           | cursor: pin S wait on X             | Concurrency      6% |    1 |                 | 0         | ON CPU                              | ON CPU      1% |    1 | 0dgcvpaqt1gp7   |           | ON CPU                              | ON CPU --  End of ASH snap 1, end=2020-11-13 11:06:17, seconds=60, samples_taken=99 PL/SQL procedure successfully completed. --//出现cursor: pin S。另外cursor: pin S wait on X估计估计与我重启马上测试有关。 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) -------------------- ---------- ---------------------- ------------- cursor=true                  50                   3552        177595 cursor=false                 50                   4801        240068 --//你可以发现设置cursor_space_for_time=true的情况下，性能还是有一定提高。如果你共享内存充足，设置cursor_space_for_time=true --//还是一定程度提高性能的。当然现在10g估计很少人用了。如果共享内存不足，不推荐使用，特别国内大量的豆腐渣工程，大量的应 --//用不使用绑定变量在oltp系统的情况下。