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

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

[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:显示顺序存在关系，仅 --//仅是我的猜测。