ITPub博客

首页 > 数据库 > Oracle > [20210812]测试sql语句子光标的性能.txt

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

原创 Oracle 作者:lfree 时间:2021-08-12 10:37:11 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3081
  • 访问量
    6806756