[20210812]測試sql語句子游標的效能.txt
[20210812]測試sql語句子游標的效能.txt
--//如果一條sql語句產生的子游標很多,除了消耗共享池記憶體外,也會導致執行時一些效能問題.測試看看.
--//children number 0 與children number=N執行時是否存在差異.
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> @ hide _cursor_obsolete_threshold
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
--------------------------- ----------------------------------------------- ------------- ------------- ------------ ----- ---------
_cursor_obsolete_threshold Number of cursors per parent before obsoletion. TRUE 1024 1024 TRUE FALSE
SYS@book> alter system set session_cached_cursors=0 scope=spfile;
System altered.
--//重啟略.
--//設定session_cache_cursor=0;避免透過軟軟解析，主要想看看這種方式對效能的影響，我想了解尋找合適的執行計劃是否透過探察。
--//如果設定session_cache_cursor>0,透過軟軟解析，2種情況執行效率基本一樣，沒有差別大家可以自行測試。
2.建立測試環境:
create table job_times (sid number, time_ela number,method varchar2(20));
create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ;
alter table t modify ( id not null );
create unique index i_t_id on t(id);
--//分析表略。
--//產生128個子游標.
$ cat m13.txt
set verify off
--//host sleep $(echo &&3/50 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
--//insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
alter session set optimizer_index_cost_adj = &&3;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
--//execute immediate 'select count(name) from t where id=1' into l_count;
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
end ;
/
--//update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
--//commit;
quit
--//說明：引數1表示迴圈次數，引數2表示method，引數3表示設定optimizer_index_cost_adj。
$ seq 128 | xargs -IQ sqlplus -s -l scott/book @m13.txt 1 xx Q
--//執行多次,保證其在共享池中.sql_id='5zfc9hksnyp90',注意sql語句全部大寫，因為PL/SQL內部做了轉化為大寫。
SCOTT@book> select child_number,executions from v$sql where sql_id='5zfc9hksnyp90' and child_number in (0,127);
CHILD_NUMBER EXECUTIONS
------------ ----------
0 6
127 6
$ cat m12.txt
set verify off
host sleep $(echo &&3/50 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
alter session set optimizer_index_cost_adj = &&4;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
--//execute immediate 'select count(name) from t where id=1' into l_count;
SELECT COUNT(NAME) INTO v_id FROM T WHERE ID=1;
end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
commit;
quit
--//說明：引數1表示迴圈次數，引數2表示method，引數3表示設定延遲，避免開始同時執行的一些爭用,引數4設定optimizer_index_cost_adj.
3.測試:
$ zzdate;seq 1 | xargs -I{} -P 1 sqlplus -s -l scott/book @m12.txt 2e5 c1=1 {} 1 >/dev/null;zzdate
trunc(sysdate)+09/24+59/1440+04/86400 == 2021/08/12 09:59:04
trunc(sysdate)+09/24+59/1440+19/86400 == 2021/08/12 09:59:19
$ zzdate;seq 1 | xargs -I{} -P 1 sqlplus -s -l scott/book @m12.txt 2e5 c128=1 {} 128 >/dev/null;zzdate
trunc(sysdate)+09/24+59/1440+33/86400 == 2021/08/12 09:59:33
trunc(sysdate)+09/24+59/1440+42/86400 == 2021/08/12 09:59:42
--//注意看:反而是帶入引數optimizer_index_cost_adj=128的執行計劃快許多.
SCOTT@book> @ ashtop username,sql_id,event 1=1 trunc(sysdate)+09/24+59/1440+04/86400 trunc(sysdate)+09/24+59/1440+19/86400
Total
Seconds AAS %This USERNAME SQL_ID EVENT FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------- ------------- ---------------------------------------- ------------------- -------------------
11 .7 79% | SCOTT 5zfc9hksnyp90 2021-08-12 09:59:05 2021-08-12 09:59:17
3 .2 21% | SCOTT 2021-08-12 09:59:14 2021-08-12 09:59:18
SCOTT@book> @ ashtop username,sql_id,event 1=1 trunc(sysdate)+09/24+59/1440+33/86400 trunc(sysdate)+09/24+59/1440+42/86400
Total
Seconds AAS %This USERNAME SQL_ID EVENT FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------- ------------- ---------------------------------------- ------------------- -------------------
3 .3 38% | SCOTT 5zfc9hksnyp90 2021-08-12 09:59:34 2021-08-12 09:59:36
3 .3 38% | SCOTT f7qnzku2m13xa 2021-08-12 09:59:33 2021-08-12 09:59:41
2 .2 25% | SCOTT 2021-08-12 09:59:37 2021-08-12 09:59:39
--//因為僅僅1個會話在執行不存在任何阻塞的情況.
SCOTT@book> select child_number,executions from v$sql where sql_id='5zfc9hksnyp90' and child_number in (0,127);
CHILD_NUMBER EXECUTIONS
------------ ----------
0 200006
127 200006
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
c128=1 1 866 866
c1=1 1 1433 1433
--//差距明顯，奇怪的是c128=1反而快.
--//測試150個程式同時執行的情況.測試2e5太慢了,簡直無法忍受!!.換成2e3看看.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 c1=150 {} 1 >/dev/null;zzdate
trunc(sysdate)+10/24+04/1440+19/86400 == 2021/08/12 10:04:19
trunc(sysdate)+10/24+05/1440+04/86400 == 2021/08/12 10:05:04
--//完成執行時間僅僅45秒.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 c1=150 {} 128 >/dev/null;zzdate
trunc(sysdate)+10/24+05/1440+31/86400 == 2021/08/12 10:05:31
trunc(sysdate)+10/24+05/1440+35/86400 == 2021/08/12 10:05:35
--//完成執行時間僅僅4秒.快了整整10倍!!
SCOTT@book> @ ashtop username,sql_id,event 1=1 trunc(sysdate)+10/24+04/1440+19/86400 trunc(sysdate)+10/24+05/1440+04/86400
Total
Seconds AAS %This USERNAME SQL_ID EVENT FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------- ------------- ---------------------------------------- ------------------- -------------------
4809 106.9 80% | SCOTT 5zfc9hksnyp90 cursor: mutex S 2021-08-12 10:04:20 2021-08-12 10:05:03
1141 25.4 19% | SCOTT 5zfc9hksnyp90 2021-08-12 10:04:20 2021-08-12 10:05:03
62 1.4 1% | SCOTT 5zfc9hksnyp90 library cache: mutex X 2021-08-12 10:04:38 2021-08-12 10:04:47
6 .1 0% | SCOTT 3hvsjqq60ng1u 2021-08-12 10:04:32 2021-08-12 10:05:02
4 .1 0% | SCOTT 2021-08-12 10:04:24 2021-08-12 10:04:50
1 .0 0% | SCOTT 8uc08r76472t2 2021-08-12 10:04:22 2021-08-12 10:04:22
6 rows selected.
--//出現的等待時間主要是cursor: mutex S,主要原因是設定session_cache_cursor=0的情況.
SCOTT@book> @ ashtop username,sql_id,event 1=1 trunc(sysdate)+10/24+05/1440+31/86400 trunc(sysdate)+10/24+05/1440+35/86400
Total
Seconds AAS %This USERNAME SQL_ID EVENT FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------- ------------- ---------------------------------------- ------------------- -------------------
12 3.0 34% | SCOTT 5zfc9hksnyp90 2021-08-12 10:05:32 2021-08-12 10:05:34
7 1.8 20% | SCOTT 3hvsjqq60ng1u 2021-08-12 10:05:32 2021-08-12 10:05:34
5 1.3 14% | SCOTT library cache: mutex X 2021-08-12 10:05:32 2021-08-12 10:05:32
3 .8 9% | SCOTT 2021-08-12 10:05:32 2021-08-12 10:05:34
2 .5 6% | SYS library cache: mutex X 2021-08-12 10:05:32 2021-08-12 10:05:32
1 .3 3% | SCOTT 0k8522rmdzg4k 2021-08-12 10:05:32 2021-08-12 10:05:32
1 .3 3% | SCOTT 459f3z9u4fb3u 2021-08-12 10:05:32 2021-08-12 10:05:32
1 .3 3% | SCOTT cm5vu20fhtnq1 2021-08-12 10:05:32 2021-08-12 10:05:32
1 .3 3% | SCOTT fj2820gfajfgf 2021-08-12 10:05:32 2021-08-12 10:05:32
1 .3 3% | SYS null event 2021-08-12 10:05:32 2021-08-12 10:05:32
1 .3 3% | SYS 2021-08-12 10:05:32 2021-08-12 10:05:32
11 rows selected.
--//對比ashtop,可以發現差異非常顯著.
SCOTT@book> select child_number,executions from v$sql where sql_id='5zfc9hksnyp90' and child_number in (0,1,126,127);
CHILD_NUMBER EXECUTIONS
------------ ----------
0 499892
1 6
126 6
127 497315
SCOTT@book> select count(*) from v$sql where sql_id='5zfc9hksnyp90' ;
COUNT(*)
----------
128
--//還是存在128個子游標的情況下.
--//我自己重複做了幾次,結果都是一樣CHILD_NUMBER=0很慢.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 p128=150 {} 128 >/dev/null;zzdate
trunc(sysdate)+10/24+17/1440+51/86400 == 2021/08/12 10:17:51
trunc(sysdate)+10/24+17/1440+55/86400 == 2021/08/12 10:17:55
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 p1=150 {} 1 >/dev/null;zzdate
trunc(sysdate)+10/24+18/1440+03/86400 == 2021/08/12 10:18:03
trunc(sysdate)+10/24+18/1440+50/86400 == 2021/08/12 10:18:50
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
c128=150 150 15 2270
p128=150 150 15 2236
c128=1 1 866 866
c1=1 1 1433 1433
c1=150 150 4008 601226
p1=150 150 4283 642383
6 rows selected.
--//可以看出,在session_cached_cursors=0的情況,沒有軟軟解析的情況下.大量執行相同sql語句.訪問CHILD_NUMBER=0比
--//CHILD_NUMBER=128慢許多.
--//實際上在2e5迴圈下CHILD_NUMBER=0根本無法忍受,太慢了.
--//從測試可以看出CHILD_NUMBER=128要快許多,我估計生成新執行計劃時插入在前面,這樣探查時最先獲得執行計劃,反而CHILD_NUMBER=0的很慢.
--//等上一段時間.
SCOTT@book> select child_number,executions from v$sql where sql_id='5zfc9hksnyp90' ;
CHILD_NUMBER EXECUTIONS
------------ ----------
0 1099646
1 297685
101 6
102 6
103 6
104 6
105 6
106 6
107 6
108 6
109 6
110 6
111 6
112 6
113 6
114 6
115 6
116 6
117 6
118 6
119 6
120 6
121 6
122 6
123 6
124 6
125 6
126 6
127 1389744
29 rows selected.
--//一些子游標已經退出共享池,再次測試.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z1=150 {} 1 >/dev/null;zzdate
trunc(sysdate)+10/24+26/1440+10/86400 == 2021/08/12 10:26:10
trunc(sysdate)+10/24+26/1440+56/86400 == 2021/08/12 10:26:56
--//依舊很慢.46秒.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 p126=150 {} 126 >/dev/null;zzdate
trunc(sysdate)+10/24+27/1440+24/86400 == 2021/08/12 10:27:24
trunc(sysdate)+10/24+27/1440+27/86400 == 2021/08/12 10:27:27
--//3秒.再次驗證我的判斷!!
SCOTT@book> select child_number,executions from v$sql where sql_id='5zfc9hksnyp90' ;
CHILD_NUMBER EXECUTIONS
------------ ----------
0 1399535
1 297685
101 6
102 6
103 6
104 6
105 6
106 6
107 6
108 6
109 6
110 6
111 6
112 6
113 6
114 6
115 6
116 6
117 6
118 6
119 6
120 6
121 6
122 6
123 6
124 6
125 297428
126 6
127 1389744
29 rows selected.
3.總結:
--//可以看出在生成許多子游標的情況下下,選擇CHILD_NUMBER更大的反而執行快一些,當然執行計劃沒有變化的情況下對比.
4.補充測試:
--//以下是我設定session_cached_cursors=50的情況,你可以發現在設定session_cached_cursors大於0的情況下快許多.
--//而且兩者差異很小.注意我迴圈次數是2e5次.而不是2e3.
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
c128=1 1 564 564
c1=1 1 585 585
c1=150 150 4914 737033
c128=150 150 4951 742632
--//兩者差別並不大。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2786553/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210816]測試sql語句子游標的效能3.txtSQL
- [20210818]測試sql語句子游標的效能4.txtSQL
- [20210813]關於測試sql語句子游標的效能的一些補充說明.txtSQL
- Python的SQL效能測試PythonSQL
- Python 的 SQL 效能測試PythonSQL
- [20210812]windows xcopy問題.txtWindows
- Prepared SQL 效能測試SQL
- 效能測試之測試指標指標
- 效能測試指標指標
- [20180724]Flashback query和子游標共享.txt
- 效能測試中唯一標識的 JMH 測試
- 效能測試——效能測試-常見效能指標-總體概況指標
- 效能測試有哪些指標需要測試？指標
- [20210812]完善vim bccalc_linux外掛.txtLinux
- 效能測試之常見效能指標指標
- PL/SQL第三章--游標SQL
- 使用profiler測試Oracle PL/SQL效能OracleSQL
- 大話效能測試系列（3）- 常用的效能指標指標
- [20180813]重新整理共享池與父子游標.txt
- [20200129]子游標不共享BIND_EQUIV_FAILURE.txtUIAI
- 軟體效能測試有哪些效能指標?可做效能測試的軟體檢測機構安利指標
- 軟體效能測試常見指標。在哪裡測試測試？指標
- [20220104]in list 幾種寫法效能測試.txt
- 大話效能測試系列（1）- 效能測試概念與主要指標指標
- 軟體效能測試有哪些測試指標?效能測試報告怎麼編寫?指標測試報告
- 效能測試指標演算法指標演算法
- [20221101]如何減少BIND_EQUIV_FAILURE引起的子游標.txtUIAI
- [20210419]測試18c SQL Translation Framework.txtSQLFramework
- [20180819]關於父子游標問題(11g).txt
- [20240607]PL/SQL中sql語句的註解.txtSQL
- 效能測試學習(1)-效能測試分類與常見術語
- 【網路】效能指標與測試工具指標
- 軟體效能測試常見指標指標
- [20180822]session_cached_cursors與子游標堆0.txtSession
- [20220328]查詢游標為什麼不共享指令碼.txt指令碼
- [20240320]空格與sqlpus的sql語句.txtSQL
- [20200320]SQL語句優化的困惑.txtSQL優化
- 游標美化