[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系統的情況下。