[20201116]測試CURSOR_SPACE_FOR_TIME(10g)(補充).txt
[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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2734320/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201116]測試CURSOR_SPACE_FOR_TIME=false(11g).txtFalse
- [20201113]測試CURSOR_SPACE_FOR_TIME(10g).txt
- [20220603]測試quiz night(補充).txtUI
- [20211013]測試遠端監聽補充.txt
- [20180828]關於引數cursor_space_for_time(10g).txt
- [20211025]12c sequence nocache測試補充.txt
- [20181030]避免表示式在sql語句中(10g)(補充).txtSQL
- [20190211]簡單測試埠是否開啟(補充).txt
- [20220815]奇怪的隱式轉換問題(11g測試補充).txt
- [20220811]奇怪的隱式轉換問題(12c補充測試).txt
- [20201116]bash shell IO重定向.txt
- [20180928]ora-01426(補充).txt
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- [20211215]提示precompute_subquery補充.txt
- [20211116]plsql_code_type=native補充.txtSQL
- [20210813]關於測試sql語句子游標的效能的一些補充說明.txtSQL
- iOS Framework 單元測試（二）-- JDAppTests（XCTests的補充）iOSFrameworkAPP
- [20211221]提示precompute_subquery補充2.txt
- [20210803]使用那個shared pool latch(補充).txt
- [20221014]TNS-12543 TNSdestination host unreachable(補充).txt
- [20180129]簡單探究cluster table(補充)4.txt
- [20181229]簡單探究cluster table(補充)3.txt
- [20181227]簡單探究cluster table(補充)2.txt
- [20220119]超長sql語句補充3.txtSQL
- [20220120]超長sql語句補充4.txtSQL
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- 面試題抽答（補充）面試題
- [20200414]Linux下快速刪除大量檔案(補充).txtLinux
- [20190214]11g Query Result Cache RC Latches補充.txt
- [20211221]記錄使用sqlplus的小問題補充.txtSQL
- [20211021]windows新建文字檔案帶日期(補充).txtWindows
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20201208]為什麼返回2行記錄補充.txt
- [20190530]DISABLE TABLE LOCK(10g).txt
- [20190215]那個更快(10g).txt
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- [20190312]關於增量檢查點的疑問(補充).txt