[20210812]測試sql語句子游標的效能.txt

lfree發表於2021-08-12

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章