[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2786553/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
