[20201116]测试CURSOR_SPACE_FOR_TIME=false(11g).txt

[20201116]测试CURSOR_SPACE_FOR_TIME=false(11g).txt --//上个星期测试CURSOR_SPACE_FOR_TIME=true在11g的情况，11g不支持已经废除了这个参数。 --//测试在11g的情况下CURSOR_SPACE_FOR_TIME=false的情况下对比10g的情况。 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 SYS@book> show parameter cursor_space_for_time NAME                  TYPE    VALUE --------------------- ------- ------ cursor_space_for_time boolean FALSE 2.建立测试脚本： create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); $ 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/book @m2.txt 1e6 cursor=false 10 >/dev/null ;zzdate trunc(sysdate)+09/24+27/1440+54/86400 == 2020/11/16 09:27:54 trunc(sysdate)+09/24+29/1440+13/86400 == 2020/11/16 09:29:13 --//同时在另外的会话执行： SYS@book> @ 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 ----------------------------------------------------------------------------------------------------   2636% |    1 | 56r5sd49t3jrv   | 0         | ON CPU                              | ON CPU    898% |    1 | 4hx229s7rgtpw   | 0         | ON CPU                              | ON CPU    541% |    1 |                 |           | ON CPU                              | ON CPU    498% |    1 |                 |           | cursor: pin S                       | Concurrency    310% |    1 | 56r5sd49t3jrv   | 0         | cursor: pin S                       | Concurrency      7% |    1 | 56r5sd49t3jrv   |           | ON CPU                              | ON CPU      7% |    1 |                 | 0         | ON CPU                              | ON CPU      1% |    1 | 0000000000000   |           | ON CPU                              | ON CPU --  End of ASH snap 1, end=2020-11-16 09:28:53, seconds=60, samples_taken=99 PL/SQL procedure successfully completed. --//取样事件不足，实际运行需要79秒，我这里仅仅取样60秒。 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) -------------------- ---------- ---------------------- ------------- cursor=false                 50                   7785        389259 SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 1=1 trunc(sysdate)+09/24+27/1440+54/86400 trunc(sysdate)+09/24+29/1440+13/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS        AAS WAIT_CHAIN ------ ---------- ---------- ----------------------------------------------------------------------------------------------   84%        3243       41.1 -> SCOTT:(sqlplus) ON CPU   11%         436        5.5 -> SCOTT:(sqlplus) cursor: pin S    5%         191        2.4 -> SCOTT:(sqlplus) cursor: pin S  -> SCOTT:(sqlplus) ON CPU    0%           2          0 -> SCOTT:(sqlplus) cursor: pin S  -> SCOTT:(sqlplus) cursor: pin S    0%           1          0 -> SCOTT:(sqlplus) cursor: pin S  -> SCOTT:(sqlplus) cursor: pin S  -> SCOTT:(sqlplus) ON CPU    0%           1          0 -> SYS:(PSPn) ON CPU 6 rows selected. --//对比10g下的测试：http://blog.itpub.net/267265/viewspace-2734320/,你可以发现11g在唯一索引扫描下性能发生了退化。 --//我在链接：http://blog.itpub.net/267265/viewspace-2642222/ 做了一些解析。 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 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.