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

lfree發表於2021-08-27

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

--//如果一條sql語句產生的子游標很多,除了消耗共享池記憶體外,也會導致執行時一些效能問題.測試看看.
--//children number=0 與children number=127執行時是否存在差異.
--//前幾天的測試連結:http://blog.itpub.net/267265/viewspace-2786553/=>[20210812]測試sql語句子游標的效能.txt,
--//可以發現children number=127的在session_cached_cursors=0的情況下比children number=0的快許多.
--//今天補充children number=N(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 6 | xargs -IZ bash -c "seq 128 | xargs -IQ sqlplus -s -l scott/book @m13.txt 1 xx Q > /dev/null"
--//執行6次,保證其在共享池中.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

$ seq 10000 | xargs -IZ bash -c "seq 128 | xargs -IQ sqlplus -s -l scott/book @m13.txt 1 xx Q > /dev/null"
--//這樣執行的目的是避免產生的子游標失效.

$ cat m12.txt
set verify off
host sleep $(echo &&3/150 | 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.測試:
$ cat ee1.sh
#!! /bin/bash
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z001=150 {} 1 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z016=150 {} 16 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z032=150 {} 32 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z048=150 {} 48 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z064=150 {} 64 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z080=150 {} 80 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z096=150 {} 96 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z112=150 {} 112 >/dev/null;zzdate
sleep 5
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m12.txt 2e3 z128=150 {} 128 >/dev/null;zzdate
sleep 5

$ . ee1.sh
--//等....
trunc(sysdate)+10/24+07/1440+09/86400 == 2021/08/16 10:07:09
trunc(sysdate)+10/24+07/1440+54/86400 == 2021/08/16 10:07:54
trunc(sysdate)+10/24+07/1440+59/86400 == 2021/08/16 10:07:59
trunc(sysdate)+10/24+08/1440+38/86400 == 2021/08/16 10:08:38
trunc(sysdate)+10/24+08/1440+43/86400 == 2021/08/16 10:08:43
trunc(sysdate)+10/24+09/1440+18/86400 == 2021/08/16 10:09:18
trunc(sysdate)+10/24+09/1440+23/86400 == 2021/08/16 10:09:23
trunc(sysdate)+10/24+09/1440+51/86400 == 2021/08/16 10:09:51
trunc(sysdate)+10/24+09/1440+56/86400 == 2021/08/16 10:09:56
trunc(sysdate)+10/24+10/1440+19/86400 == 2021/08/16 10:10:19
trunc(sysdate)+10/24+10/1440+24/86400 == 2021/08/16 10:10:24
trunc(sysdate)+10/24+10/1440+42/86400 == 2021/08/16 10:10:42
trunc(sysdate)+10/24+10/1440+47/86400 == 2021/08/16 10:10:47
trunc(sysdate)+10/24+10/1440+59/86400 == 2021/08/16 10:10:59
trunc(sysdate)+10/24+11/1440+04/86400 == 2021/08/16 10:11:04
trunc(sysdate)+10/24+11/1440+10/86400 == 2021/08/16 10:11:10
trunc(sysdate)+10/24+11/1440+15/86400 == 2021/08/16 10:11:15
trunc(sysdate)+10/24+11/1440+18/86400 == 2021/08/16 10:11:18
--//儲存為ee2.txt

$ tac ee2.txt | awk '{print "("$1")"}'  | paste -d"-" - -  | awk '{print "set head off\nselect (",$1,")*86400 cc from dual;"}' | sqlplus -s -l scott/book | tac
        45
        39
        35
        28
        23
        18
        12
         6
         3
--//說明:我使用tac倒看,然後兩個時間相減,然後在使用tac倒回來,這樣前面顯示的對應child_number最小的執行時間在前,可以發現
--//child_number越小執行時間越長.

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)
-------------------- ---------- ---------------------- -------------
z128=150                    150                     83         12431
z112=150                    150                    458         68642
z096=150                    150                    995        149258
z080=150                    150                   1549        232309
z064=150                    150                   2056        308374
z048=150                    150                   2606        390954
z032=150                    150                   3123        468489
z016=150                    150                   3696        554327
z001=150                    150                   4198        629724
9 rows selected.

# perf top -k /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
   PerfTop:   23962 irqs/sec  kernel: 3.0%  exact:  0.0% [1000Hz cycles],  (all, 24 CPUs)
------------------------------------------------------------------------------------------------------

             samples  pcnt function               DSO
             _______ _____ ______________________ ____________________________________________________

           108104.00 52.0% kgxShared              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
            79486.00 38.2% kgxRelease             /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1524.00  0.7% kgxWait                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              989.00  0.5% kkshGetNextChild       /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              869.00  0.4% do_lookup_x            /lib64/ld-2.5.so
              605.00  0.3% _dl_relocate_object    /lib64/ld-2.5.so
              559.00  0.3% kkscsSearchChildList   /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              450.00  0.2% kksMutexWait           /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              386.00  0.2% kkscsCheckCursor       /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              328.00  0.2% kkscsPruneChild        /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              310.00  0.1% _dl_addr               /lib64/libc-2.5.so
--//主要集中在kgxShared,kgxRelease呼叫上。

--//補充說明,我在測試時也遇到這樣的情況:
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)
-------------------- ---------- ---------------------- -------------
z048=150                    150                     81         12218
z128=150                    150                    835        125234
z112=150                    150                   1394        209127
z096=150                    150                   1893        284017
z080=150                    150                   2438        365652
z064=150                    150                   2982        447316
z032=150                    150                   3459        518858
z016=150                    150                   3697        554493
z001=150                    150                   4464        669584
9 rows selected.
--//z048=150 最快的情況,你測試時間越長,出現這樣的機率越大.為什麼呢?

SCOTT@book> @ share 5zfc9hksnyp90
...
--------------------------------------------------
SQL_TEXT                       = SELECT COUNT(NAME) FROM T WHERE ID=1
SQL_ID                         = 5zfc9hksnyp90
ADDRESS                        = 000000007C5512A0
CHILD_ADDRESS                  = 000000007C248D18
CHILD_NUMBER                   = 47
REASON                         = <ChildNode><ChildNumber>47</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x228</size><optimizer_index_cost_adj> 48                   1
</optimizer_index_cost_adj></ChildNode><ChildNode><ChildNumber>47</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x228</size><optimizer_index_cost_adj> 48                   1
</optimizer_index_cost_adj></ChildNode><ChildNode><ChildNumber>47</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x228</size><optimizer_index_cost_adj> 48                   49                  </optimizer_index_cost_adj></ChildNode>
--------------------------------------------------
...
--------------------------------------------------
SQL_TEXT                       = SELECT COUNT(NAME) FROM T WHERE ID=1
SQL_ID                         = 5zfc9hksnyp90
ADDRESS                        = 000000007C5512A0
CHILD_ADDRESS                  = 000000007BCE5D48
CHILD_NUMBER                   = 127
OPTIMIZER_MISMATCH             = Y
REASON                         = <ChildNode><ChildNumber>127</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x228</size><optimizer_index_cost_adj> 128                  1                   </optimizer_index_cost_adj></ChildNode>
--------------------------------------------------

SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 10;
Statement processed.
SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_19964_0001.trc

--//出現這樣情況轉儲library_cache,可以發現CursorDiagnosticsNodes順序發生變化.出現在ChildNumber=127的前面了.
--//也就是可能我測試中遇到的例外情況.
Bucket: #=87328 Mutex=0x80493530(0, 6000, 0, 6)
.....
  LibraryHandle:  Address=0x7c5512a0 Hash=b14f5520 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=SELECT COUNT(NAME) FROM T WHERE ID=1
      FullHashValue=c11a46b81ff591945fb98984b14f5520 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=2974766368 OwnerIdn=83
    Statistics:  InvalidationCount=0 ExecutionCount=5326371 LoadCount=168 ActiveLocks=0 TotalLockCount=5422735 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=128 Version=0 BucketInUse=173 HandleInUse=173 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7c551350(0, 341, 0, 0) Mutex=0x7c5513e0(50, 22370185, 265620, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841]
    WaitersLists:
      Lock=0x7c551330[0x7c551330,0x7c551330]
      Pin=0x7c551310[0x7c551310,0x7c551310]
      LoadLock=0x7c551388[0x7c551388,0x7c551388]
    Timestamp:  Current=08-16-2021 10:04:53
    HandleReference:  Address=0x7c551470 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x7d4f0468 Handle=0x7df76560 Flags=ROD[21]
      Reference:  Address=0x7e25d350 Handle=0x7ceb6e40 Flags=ROD[21]
      Reference:  Address=0x7c51c820 Handle=0x7d070fe0 Flags=ROD[21]
      Reference:  Address=0x7dba90f0 Handle=0x7e2f9768 Flags=ROD[21]
      Reference:  Address=0x7d7791c8 Handle=0x7e130668 Flags=ROD[21]
      Reference:  Address=0x7cf45040 Handle=0x7d705e38 Flags=ROD[21]
      Reference:  Address=0x7dd6c450 Handle=0x7e2a8a70 Flags=ROD[21]
      Reference:  Address=0x7c6ed9a0 Handle=0x7daab610 Flags=ROD[21]
      Reference:  Address=0x7e17fc38 Handle=0x7c254328 Flags=ROD[21]
...

    NamespaceDump:
      Parent Cursor:  sql_id=5zfc9hksnyp90 parent=0x7c5502d0 maxchild=128 plk=n ppn=n
        CursorDiagnosticsNodes:
          ChildNode:  ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51                   1
          ChildNode:  ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50                   1
          ChildNode:  ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49                   1
          ChildNode:  ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48                   1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~          
          ChildNode:  ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47                   1
          ChildNode:  ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46                   1
          ChildNode:  ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45                   1
          ChildNode:  ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44                   1
          ChildNode:  ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43                   1
          ChildNode:  ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42                   1
          ChildNode:  ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40                   1
          ChildNode:  ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39                   1
          ChildNode:  ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 41                   1
          ChildNode:  ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 38                   1
          ChildNode:  ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 37                   1
          ChildNode:  ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 36                   1
          ChildNode:  ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 35                   1
          ChildNode:  ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 29                   1
          ChildNode:  ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 28                   1
          ChildNode:  ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 27                   1
          ChildNode:  ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26                   1
          ChildNode:  ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 25                   1
          ChildNode:  ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 24                   1
          ChildNode:  ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 23                   1
          ChildNode:  ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 22                   1
          ChildNode:  ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 21                   1
          ChildNode:  ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 20                   1
          ChildNode:  ChildNumber=127 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 128                  1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          ChildNode:  ChildNumber=126 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 127                  128
          ChildNode:  ChildNumber=125 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 126                  127
          ChildNode:  ChildNumber=124 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 125                  126
          ChildNode:  ChildNumber=123 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 124                  125
          ChildNode:  ChildNumber=122 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 123                  124
          ChildNode:  ChildNumber=121 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 122                  123
          ChildNode:  ChildNumber=120 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 121                  122
          ChildNode:  ChildNumber=119 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 120                  121
          ChildNode:  ChildNumber=118 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 119                  120
          ChildNode:  ChildNumber=117 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 118                  119
          ChildNode:  ChildNumber=116 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 117                  118
          ChildNode:  ChildNumber=115 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 116                  117
          ChildNode:  ChildNumber=114 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 115                  116
          ChildNode:  ChildNumber=113 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 114                  115
          ChildNode:  ChildNumber=112 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 113                  114
          ChildNode:  ChildNumber=111 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 112                  113
          ChildNode:  ChildNumber=110 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 111                  112
          ChildNode:  ChildNumber=109 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 110                  111
          ChildNode:  ChildNumber=108 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 109                  110
          ChildNode:  ChildNumber=107 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 108                  109
          ChildNode:  ChildNumber=106 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 107                  108
          ChildNode:  ChildNumber=105 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 106                  107
          ChildNode:  ChildNumber=104 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 105                  106
          ChildNode:  ChildNumber=103 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 104                  105
          ChildNode:  ChildNumber=102 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 103                  104
          ChildNode:  ChildNumber=101 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 102                  103
          ChildNode:  ChildNumber=100 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 101                  102
....
          ChildNode:  ChildNumber=52 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 53                   54
          ChildNode:  ChildNumber=51 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 52                   53
          ChildNode:  ChildNumber=33 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 34                   35
          ChildNode:  ChildNumber=32 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 33                   34
          ChildNode:  ChildNumber=31 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 32                   33
.....
          ChildNode:  ChildNumber=8 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 9                    10
          ChildNode:  ChildNumber=7 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 8                    9
          ChildNode:  ChildNumber=6 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 7                    8
          ChildNode:  ChildNumber=5 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 6                    7
          ChildNode:  ChildNumber=4 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 5                    6
          ChildNode:  ChildNumber=3 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 4                    5
          ChildNode:  ChildNumber=2 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 3                    4
          ChildNode:  ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 2                    3
          ChildNode:  ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 1                    2
        AgedOutCursorDiagnosticNodes:
          ChildNode:  ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51                   1
          ChildNode:  ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50                   1
          ChildNode:  ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49                   1
          ChildNode:  ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48                   1
          ChildNode:  ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47                   1
          ChildNode:  ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46                   1
          ChildNode:  ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45                   1
          ChildNode:  ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44                   1
          ChildNode:  ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43                   1
          ChildNode:  ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42                   1
          ChildNode:  ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40                   1
          ChildNode:  ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39                   1
          ChildNode:  ChildNumber=50 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51                   52
          ChildNode:  ChildNumber=49 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 50                   51
          ChildNode:  ChildNumber=48 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 49                   50
          ChildNode:  ChildNumber=47 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 48                   49
          ChildNode:  ChildNumber=46 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 47                   48
          ChildNode:  ChildNumber=45 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 46                   47
          ChildNode:  ChildNumber=44 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 45                   46
          ChildNode:  ChildNumber=43 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 44                   45
          ChildNode:  ChildNumber=42 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 43                   44
          ChildNode:  ChildNumber=41 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 42                   43
          ChildNode:  ChildNumber=40 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 41                   42
          ChildNode:  ChildNumber=39 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 40                   41
          ChildNode:  ChildNumber=38 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 39                   40
          ChildNode:  ChildNumber=37 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 38                   39
          ChildNode:  ChildNumber=36 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 37                   38
          ChildNode:  ChildNumber=35 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 36                   37
          ChildNode:  ChildNumber=34 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 35                   36
          ChildNode:  ChildNumber=28 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 29                   30
          ChildNode:  ChildNumber=27 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 28                   29
          ChildNode:  ChildNumber=26 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 27                   28
          ChildNode:  ChildNumber=25 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 26                   27
          ChildNode:  ChildNumber=24 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 25                   26
          ChildNode:  ChildNumber=23 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 24                   25
          ChildNode:  ChildNumber=22 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 23                   24
          ChildNode:  ChildNumber=21 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 22                   23
          ChildNode:  ChildNumber=20 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 21                   22
          ChildNode:  ChildNumber=19 ID=3 reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 20                   21

--//有點長...我對這方面不是很瞭解,我大致猜測,開始執行時:
optimizer_index_cost_adj= 1, ChildNumber=0
optimizer_index_cost_adj= 2, ChildNumber=1 , reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 1  2
--//以下如此類推。
optimizer_index_cost_adj= 126,ChildNumber=126,reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 127 128
optimizer_index_cost_adj= 127,ChildNumber=127,reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 128 1

--//可能是我測試時不停執行如下的影響。只有這裡可能出現執行時optimizer_index_cost_adj= 51的情況。
$ seq 10000 | xargs -IZ bash -c "seq 128 | xargs -IQ sqlplus -s -l scott/book @m13.txt 1 xx Q > /dev/null"

--//這樣出現如下情況。
optimizer_index_cost_adj= 51,ChildNumber=50,reason=Optimizer mismatch(12) size=2x228 optimizer_index_cost_adj= 51 1

--//從另外的方面說明並非ChildNumber越大,執行時間越短。可能跟library_cache轉儲看到的 CursorDiagnosticsNodes:顯示順序有關。
--//我估計測試時不執行如下,遇到的測試問題應該不存在。
$ seq 10000 | xargs -IZ bash -c "seq 128 | xargs -IQ sqlplus -s -l scott/book @m13.txt 1 xx Q > /dev/null"

--//但是我測試遇到的情況是一些子游標會消失。

4.總結:
--//不小心又寫的太長,不過可以發現子游標很多的情況下對效能的影響。
--//主要集中在kgxShared ,kgxRelease的呼叫上。kkshGetNextChild,kkscsSearchChildList也存在少量呼叫。
--//並非ChildNumber越大,執行時間越短,執行的快慢也許library_cache轉儲看到的CursorDiagnosticsNodes:顯示順序存在關係,僅
--//僅是我的猜測。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2789058/,如需轉載,請註明出處,否則將追究法律責任。

相關文章