[20210812]测试sql语句子光标的性能.txt

来源：这里教程网时间：2026-03-03 16:52:16 作者：

[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 --//两者差别并不大。

编辑推荐：

返回列表

[20210812]测试sql语句子光标的性能.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推