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