[20210816]測試sql語句子游標的效能3.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210812]測試sql語句子游標的效能.txtSQL
- [20210818]測試sql語句子游標的效能4.txtSQL
- [20210813]關於測試sql語句子游標的效能的一些補充說明.txtSQL
- Python的SQL效能測試PythonSQL
- Python 的 SQL 效能測試PythonSQL
- Prepared SQL 效能測試SQL
- 效能測試之測試指標指標
- 效能測試指標指標
- 效能測試中唯一標識的 JMH 測試
- 效能測試——效能測試-常見效能指標-總體概況指標
- 效能測試有哪些指標需要測試？指標
- 效能測試之常見效能指標指標
- PL/SQL第三章--游標SQL
- 使用profiler測試Oracle PL/SQL效能OracleSQL
- 大話效能測試系列（3）- 常用的效能指標指標
- 軟體效能測試有哪些效能指標?可做效能測試的軟體檢測機構安利指標
- [20220119]超長sql語句補充3.txtSQL
- 軟體效能測試常見指標。在哪裡測試測試？指標
- 大話效能測試系列（1）- 效能測試概念與主要指標指標
- 軟體效能測試有哪些測試指標?效能測試報告怎麼編寫?指標測試報告
- 效能測試指標演算法指標演算法
- [20210407]分析sql語句的共享記憶體段3.txtSQL記憶體
- 效能測試學習(1)-效能測試分類與常見術語
- 【網路】效能指標與測試工具指標
- 軟體效能測試常見指標指標
- 游標美化
- 效能測試中的唯一標識問題研究
- (12)mysql 中的游標MySql
- input 獲取游標位置與設定游標位置
- 測試標題測試標題
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txtSQL
- RVS — 面向目標硬體的軟體效能測試工具
- 效能測試中伺服器關鍵效能指標淺析伺服器指標
- sql語句效能優化SQL優化
- 【CURSOR】Oracle 子游標無法共享的原因之V$SQL_SHARED_CURSOROracleSQL
- 效能測試的流程
- 軟體效能測試主要看什麼指標指標
- 效能測試各個指標之間關係指標