ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

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

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

原创 Oracle 作者：lfree 时间：2021-08-27 09:08:57 0 删除 编辑

[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2789058/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20210819]给文件内容编行号.txt
下一篇： [20210818]测试sql语句子光标的性能4.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7970383

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593