µ¼Ö²»Ê¹ÓÃCBO¶øʹÓÃRBOµÄÈý¸öÔÒò:
)O8F,h`_+Q01.optimizer_modeÉèÖÃΪrule»òoptimizer_goalÉèÖÃΪrule
wHsD+S0Of02.²éѯÖаüº¬ÓÐruleÌáʾ.
O/E[
gKU"L*cL5E03.²éѯµÄËùÓÐ±í¶¼Ã»Óб»·ÖÎö¹ý,²¢ÇÒ²éѯ²»°üº¬ÈκÎÌáʾ.
-gI`"F*Wh0RBOµÄ¹¤×÷·½Ê½:
W't^1P(qM)Q,j0 RBOÔÚ¾ö¶¨SQLµÄÖ´Ðмƻ®Ê±,ֻʹÓÃÉÙÁ¿ÐÅÏ¢:
t9l!M{N'ih}gV,`X0 SQLÎı¾.
"ax(^3l/w+@ilt^"o0 SQLµÄFROMºóÃæµÄ»ù±¾ÐÅÏ¢,Èç±í,´Ø,ÊÓͼ,²Î¿¼µÄÆäËû×ֶεÄÊý¾ÝÀàÐÍ.
'c4Y,z`S;M0 ÓëSQLÏà¹Ø±íÉϵÄË÷Òý.51TestingÈí¼þ²âÊÔÍø%I+b8Y.p,{1P
Êý¾Ý×ÖµäÐÅÏ¢Ö»¶Ô±¾µØÊý¾Ý¿âÓÐÓÃ.Èç²Î¿¼Ô¶³ÌÊý¾Ý¿â,Êý¾Ý×ÖµäÐÅÏ¢¶ÔRBOÎÞЧ.51TestingÈí¼þ²âÊÔÍø4V3@9il8I2U\O[%r
´ØµÄȱµã³¬¹ýËüµÄÓŵã.51TestingÈí¼þ²âÊÔÍø"FKOk9b z3B
ºÜÉÙÔÚÓ¦ÓÃÖÐʹÓôØ. 51TestingÈí¼þ²âÊÔÍø0]Kil ]2^
½áÂÛ:51TestingÈí¼þ²âÊÔÍø;E!C F#dv:~ p5l2Z~q
RBOÖ»¿¼ÂÇÉÙÁ¿¿ÉÄܵķÃÎÊ·½Ê½.
#D*nl5?pU0 ¶ÔRBOÀ´Ëµ,Ë÷Òý×ÜÊǺõÄ,¼´Ê¹ÓеÄʱºòË÷Òý²»Ò»¶¨ºÃ.
$h
[!_(kw#qn^V!O0 RBO¾³£Ê¹ÓÃûÓÐÒâÒåµÄÇÏÃÅÀ´½âÊÍSQL,ΪʲôFROMºóÃæµÄ˳Ðò»áÓë´ÎÏà¹Ø?!ºÜ¶àÈ˽è½âÊÍÁËCBOµÄ²»Îȶ¨ÐÔ,µ«ÊÇÓжàÉÙ´ÎË÷ÒýÖؽ¨ºóRBOµÄÖ´Ðмƻ®Òò´Ë¸Ä±äÁËÄØ.
r&X0^(|
Tl8@0 100%µÄÃüÖÐÂÊÒâζ×ÅûÓÐÂß¼IOµ¼ÖÂÎïÀíIO,ÕâÇå³þµØ½âÊÍÁËÒ»µã:»º´æÃüÖÐÂʶÔÓÅ»¯Ö´Ðмƻ®Ã»ÓÐʲôÒâÒå.¼õÉÙÖ´ÐÐ,¼õÉÙÂß¼IOµÄÊýÁ¿²ÅÊǸüºÃµÄÄ¿±ê.
lB-vu2]~v0 Oracle×îºó²»µÃ²»Å×ÆúIO.×Ô´Ó½éÉÜRBOµ½CBO³öÏÖ²¢ºÜºÃµØÓ¦ÓÃÓÚÉú²úϵͳ,RBOÔËÐÐÁË7ÄêÖ®¾Ã.51TestingÈí¼þ²âÊÔÍøZ:R F)wIkZ
CBOµÄ¹¤×÷ÔÀí:51TestingÈí¼þ²âÊÔÍø
mU U(cB`l
CBOÊÇÒ»¸öÊýѧ´¦ÀíÆ÷,ËüʹÓù«Ê½È¥¼ÆËãSQLµÄ³É±¾.³É±¾±¾ÖÊÉϱ»×ª»»ÎªÎïÀíIO,Âß¼IOÔÚOracleµÄSGAµÄBuffer CacheÖб»ÍêÈ«´¦Àí.
N1Azi9G1p3Ix0 CBOʹÓøü¶àµÄ¿ÉÄܵķÃÎÊ·½Ê½,Ëü¼ÆËãÿ¸ö¿ÉÄܵķÃÎÊ·½Ê½µÄÂß¼IOµÄÊýÁ¿,È»ºó¼òµ¥µØÑ¡Ôñ×îµÍ³É±¾µÄÒ»¸öÖ´Ðз½Ê½.
5h R8yN
wQ.h(D0 RBOÊÇÏßÐÔµÄÉè¼Æ,Ò»²½²½µØ×ö¾ö¶¨,ÏÈ×î¾ö¶¨,È»ºóϸö¾ö¶¨ÒԸþö¶¨Îª»ù´¡,³õʼ¾ö¶¨¶Ô×îÖյĽá¹ûÓ°ÏìºÜ´ó.
']%S'S)D2I0k6kXt0 ʲôÇé¿öÏ¿ÉÄܳö´í?
8T,`)H6d!w"~Q
nX%h0 CBOÖ»ÊÇÑ¡ÔñÖ´Ðмƻ®Öгɱ¾×îµÍµÄÒ»ÖÖ,ÖÁÉÙÁ½ÖÖÇé¿ö¿ÉÄÜ»á³ö´í:51TestingÈí¼þ²âÊÔÍøs*u,g%[A:TK])Fb
1.¿ÉÄܽÓÊÕµ½´íÎóµÄÊý¾Ý,
+n$`/N.gVB%N0 2.Ò»¸ö»ò¶à¸ö¹«Ê½Ã»ÓаüÀ¨ÖØÒªµÄÒò×Ó»òÊÇ´íÎóµÄ.51TestingÈí¼þ²âÊÔÍøD\:_"n;\%A#r
ÕâÁ½¸öÎÊÌâ¹á´©Oracle7ʼÖÕ.
N&S)g;@x9V2|*S0 Oracle7ÖÐ,analyzeÃüÁî´æÔںܶàBUG,µ½ÁËOracle8ÖÐ,ºÜ¶àBUG±»ÐÞ¸´ÁË. Oracle 8iÖÐDBMS_stats°ü±ÈanalyzeÃüÁî¸üºÃµÄͳ¼ÆÐÅÏ¢.ʣϵľÍÊǶÔCBOʹÓõļÆËã³É±¾µÄ¹«Ê½½øÐÐϸ΢µÄÐÞÕýÁË.51TestingÈí¼þ²âÊÔÍø:Dud Z[!g
CostÊÇʲôÒâ˼?
0c"Ww"Ox0 CBO¼ÆËãµÄ³É±¾Ö÷ÒªÓÉÎïÀíIO×é³É.ʵ¼ÊµÄ¹«Ê½:IO+CPU/1000+NetIO*1.551TestingÈí¼þ²âÊÔÍøP;Y!`0}T2P2W
¸Ã¹«Ê½ÖÐ,IO±íʾÎïÀíIOÇëÇó, CPUÃèÊöÂß¼IOÇëÇó, NetIOÃèÊöͨ¹ýÊý¾Ý¿âÁ¬½Óµ½Ô¶³ÌÊý¾Ý¿âµÄÂß¼IOÇëÇó.ÕâÒÔΪ×ÅÎïÀíIOÊÇ×î"°º¹ó"µÄ×é³É²¿·Ö.´ÓÖÐÒ²¿ÉÒÔ¿´³ö,·Ö²¼Ê½Êý¾Ý¿âÓ¦ÓûáÓиü¸ßµÄCOST.Âß¼IOºÍÎïÀíIOµÄÇø±ðÊÇʲô? Ç°ÕßÊǶԻº´æÔÚOracle SGAµÄBuffer CacheÖеÄÊý¾Ý¿éµÄ·ÃÎÊ. ºóÕßÊÇ´ÓOracleϵͳÖÐÈ¡µÃµÄ²»»º´æÔÚBuffer CacheµÄÊý¾Ý¿éµÄÇëÇóÊý. CBO¼ÆËãËùÓпÉÄܵÄÖ´Ðмƻ®µÄÎïÀíIOµÄÇëÇóÊý.Åųý²»ÊÇ×îµÍµÄÖ´Ðмƻ®.
4`D_3Bk-Y0 Oracle IO¹æÔò:
j't tZK*~:j0 Oracle²»·ÃÎÊÐÐ,¶øÊÇ·ÃÎÊ°üº¬ÐеÄÊý¾Ý¿é,²»ÊÇÈ¥·ÃÎÊ´ÅÅÌ,¶øÊÇÊ×ÏÈÈ¥²éOracle SGAÖеÄBuffer CacheÄÚ´æÖеĿé(Âß¼¶Á).Âß¼¶Á:consistent getsºÍdb block gets(current gets). Ò»Ö¶ÁÊÇÌáÈ¡µÄÌض¨°æ±¾»òʱ¼äµãµÄSQLÊý¾Ý¿é, db block getsÊǵ±Ç°µÄʱ¼äµãÌáÈ¡µÄÊý¾Ý¿é,Ò»°ãÊÇÐÞ¸ÄÊý¾ÝÈç²åÈë¡¢¸üС¢É¾³ý¡£
:x
H$Z3t6gS0E0 v$sysstat»òv$sesstatÖв»»áÓÐ"logical read"£¬Ö»ÓÐ"consistent read"ºÍ"db block gets" ,½«Á½ÕßÏà¼Ó¾ÍÊÇÁË¡£51TestingÈí¼þ²âÊÔÍø9j!t*~8p)zO
Èç¹ûÔÚBuffer CacheÖÐÕÒµ½ÁËÊý¾Ý¿é£¬Ôòϵͳ¾ÍʹÓÃÕÒµ½µÄÊý¾Ý¿é£¬Èç¹ûûÓÐÕÒµ½£¬ÏµÍ³Ö´ÐÐÎïÀí¶Á£¬½«¿é¶Áµ½Buffer CacheÖС£51TestingÈí¼þ²âÊÔÍøG(~Qr^X
Èç¹ûÒ»¸ö·þÎñÆ÷½ø³ÌÕýÔÚÖ´ÐÐÒ»¸öÎïÀí¶Á£¬¶øÁíÒ»¸ö½ø³ÌÖ´ÐÐÂß¼¶Áʱ£¬Ã»Óз¢ÏÖ»º´æÖÐÓÐÊý¾Ý¿é£¬ÄÇô¸Ã½ø³ÌÒ²»áÈ¥½øÐÐÒ»¸öÎïÀí¶ÁÂ𣿲»£¬µÚÒ»¸ö½ø³ÌÔÚ·¢ÏÖÊý¾Ý¿éûÓÐÔÚ»º´æÖÐʱ£¬ÔÚʵ¼ÊÖ´ÐÐÎïÀí¶Á֮ǰ»áÔ¤ÏÈ·ÖÅäÒ»¸ö»º³åÆ÷¸ø¸Ã¿é£¬È»ºóËø¶¨»º³åÆ÷²¢·¢ËÍÎïÀíIOÇëÇ󣬵ڶþ¸ö½ø³ÌÊÔͼ½øÐÐÂß¼¶Á£¬¶ø»º³åÆ÷±»Ëø¶¨£¬Ôò»áPOSTÒ»¸ö"buffer busy wait"µÄµÈ´ýʼþ²¢µÈ´ý¸Ã¿é¿ÉÓ㬼õÉÙûÓбØÒªµÄIO¡£51TestingÈí¼þ²âÊÔÍø#V8~X&zN8Q*@,M6WL
ÔÚcache buffers chainÉϹ¤×÷51TestingÈí¼þ²âÊÔÍø#EB O k"AX
Buffer CacheÊÇÒ»¸öÌîÂú»º³å¿éµÄ¸ßËÙ»º³å´æ´¢Æ÷£¬ÓÉLRU(least recently used)ÔË51TestingÈí¼þ²âÊÔÍø0kT;y5]j)D@
Ëã·¨Ôò¹ÜÀí,ʹÓø÷¨Ôò£¬×î¾³£±»·ÃÎʵĻº³å¿é±»·ÅÔÚÁ´±íµÄMRU(most recently used)¶Ë,жÁµ½»º³å´æ´¢Æ÷ÖеĿ鱻·ÅÖÃÔÚMRU¶Ë£¬Âß¼¶Áʱ£¬ËûÃDZ»ÒƵ½MRUÁбíÒ»¶Ë£¬Èç¹û¿éûÓб»·ÃÎÊ,Ëü¾Í»áÖð½¥µØÏòLRU¶ËÒƶ¯¡£µ±Ëü´ïµ½LRU¶ËÄ©µÄʱºò£¬Ð¶Áµ½µÄ¿é¾Í»á¸²¸Ç¸Ã»º³å¿é£¬¸Ã¿éÒ²¾ÍµôÀëÁбíµÄLRU. Oracle8iÖÐʹÓÃÁËÖеã²åÈëËã·¨À´¼õÉÙÒò¿éÒƳöÁбíÌرðÊÇ"ÈÈ"¿éÒƶ¯µ½ÁбíµÄMRU¶ËµÄlatch´¦Àí¡£
&?@L4g:N jX7y0Á½ÀàIO£º51TestingÈí¼þ²âÊÔÍø'{~&k1I/v}
Oracle¶ÁÊý¾ÝÎļþÓÐÁ½ÖÐÀàÐ͵ÄIO£º
{u-nAOXt*p3xY0 1.µ¥¿é£¬Ëæ»ú·ÃÎʶÁÈ¡£¬Í¨³£ÊÇÓëË÷Òý·ÃÎÊÓйصÄ(¸ÃIOÇëÇó·¢Éúʱ£¬²úÉúµÈ´ýʼþdb file sequential read).51TestingÈí¼þ²âÊÔÍøKG%w|0dv6]u1p"{
2.¶à¿é£¬Ë³Ðò·ÃÎʶÁÈ¡£¬Í¨³£ÓëÈ«±íɨÃè·ÃÎÊÓйØ(¸ÃIOÇëÇó·¢Éúʱ£¬½«²úÉúdb file scattered readµÈ´ýʼþ)£¬µ«ÊÇ¿ÉÄÜÓëÅÅÐòºÍ²¢Ðвéѯ(µÈ´ýʼþ£ºdirect path read).51TestingÈí¼þ²âÊÔÍøj1g&ZlQ
ÕâÁ½ÖÖIOÀàÐ͹æÔòÊ®·ÖÏàËÆ£¬½ø³ÌÐèÒªÊý¾Ý¿éʱ£¬Ê×Ïȼì²éBuffer Cache£¬Èç¹û¿é´æÔÚ£¬¾ÍʹÓÃËü£¬²¢ÇÒ°ÑËüÒƶ¯µ½LRUÁ´µÄMRU¶Ë¡£Èç¹û¿é²»´æÔÚ£¬¾Í´Ó´ÅÅÌÉϵÄÊý¾ÝÎļþ¶ÁÈ¡·Åµ½»º³åÇøÖУ¬²¢½«Ëü·ÀÖ¹µ½LRUÁ´µÄMRU¶Ë¡£
*i\"GBx5L0 ²»¿É±ÜÃâµØ£¬Ã¿¸öжÁµ½µÄ¿é¶¼½«±»·ÅÖõ½LRUÁ´µÄMRU¶Ë£¬µ±Ç°ÃæµÄ¿é±»LRUÁ´µÄLRU¶Ëʱ£¬×îºó±»ÍƳöLRUÁ´»ò±»¸²¸Ç¡£È«±íɨÃèʱ£¬ÐèҪɨÃèµÄ¿é´óÓÚBuffer Cacheʱ£¬½«ÍêÈ«Ë¢ÐÂBuffer CacheµÄÄÚÈÝ¡£51TestingÈí¼þ²âÊÔÍø
W0?7t!XqRkq
OracleÖеĻº´æIO£º51TestingÈí¼þ²âÊÔÍøy
bvFT
Oracle7ÖÐÒ»¸ö¸Ä±äÀ´·ÀÖ¹ÕâÖÖ»º³åÇø±»Ë¢ÐµÄÇé¿ö(cache flushing effect),Ê×ÏÈ,¶à¿é˳Ðò·ÃÎʵÄÈ«±íɨÃèµÄ´¦ÀíºÍLRUµÄËã·¨ÊDz»Í¬µÄ,µ±È«±íɨÃèʱ,¶Áµ½µÄ¿é½«±»·ÀÖ¹µ½LRUÁ´µÄLRU¶Ë.Ë÷ÒýɨÃèµÄµ¥¿é¶ÁûÓиıä,¼ÌÐø±»·ÅÖÃÔÚLRUÁ´µÄMRU¶Ë.È«±íɨÃèµÄÊý¾Ý¿é¼Æ»®»áÁ¢¿Ì±»¸²¸Ç,³ý·ÇÕâЩ¿éºÜ¿ì±»ÔٴηÃÎÊ(½«±»Òƶ¯µ½MRU¶Ë).51TestingÈí¼þ²âÊÔÍøsZB?(l1qX
È«±íɨÃèµÄÂß¼¶ÁÒ»°ãºÜÔÚBuffer CacheÖÐÕÒµ½ÐèÒªµÄ¿é,Òò´Ë,È«±íɨÃè×ÜÊDZíÏÖΪ´óÁ¿µÄÎïÀí¶Á. µ«ÊDZ»È«±íɨÃèÖØзÃÎʵĿéÓжà¿éµÄƵÂÊ?´ó¶àÊýÇé¿öϺÜÉÙ.ËùÒÔOracle7µÄÉè¼ÆÕß¹«ÈϵØÔÊÐí¿éºÜ¿ì±»¸²¸Ç,ͬʱ±£ÁôÏÂBuffer CacheµÄÔÀ´ÕæʵÄÚÈÝ.51TestingÈí¼þ²âÊÔÍø3u ~2L:C
I N
µ«ÊÇС±íûÓбØҪÿ´ÎÎïÀí¶Á,ÓÚÊÇËûÃÇÉè¼ÆOracle7ʱ½«Ð¡±íµÄÈ«±íɨÃè¿é·ÀÖ¹ÔÚLRUÁ´µÄMRU¶Ë,×÷ΪÌØÀý,¼ÓËÙ¶ÔС±íÈ«±íɨÃèµÄ·ÃÎÊ. ²ÎÊýSMALL_TABLE_THESHOLD´æ´¢ÁË¿ÉÒÔʹÓõÄÊý¾Ý¿éÊý,ĬÈÏdb_block_buffersµÄ2%. Oracle7ǰûÓиòÎÊý. ÓÐÈ˽«¸Ã²ÎÊýÉèÖùý´ó,ÒÑ´æ´¢¸ü¶àµÄС±í,µ«ÊÇÕâÑù½«µ¼ÖÂbuffer cache¿ÉÓñäС,·´ÎÊÈÝÒ׳öÏÖ²»Ï£Íû³öÏÖµÄcache flush.51TestingÈí¼þ²âÊÔÍøh J:d3tK
Oracle7 +°æ±¾ÖеÄIO:
|wS9fy0 small_table_thresholdÒѾ±»ÉèÖÃΪÒþº¬²ÎÊý_small_table_threshold.¿¼Âǵ½¿ª·¢Õß¿ÉÄܻὫij±íפÁôÔÚBuffer CacheÖÐ,ËùÒÔÔÚ½¨±í»òÐ޸ıíµÄÓï¾äÖÐÉèÖÃÁËcacheºÍnocache¹Ø¼ü×Ö,ÉèÖÃΪcacheµÄ±í¿é½«±»·ÀÖ¹ÔÚLRUÁ´µÄMRU¶Ë.
}N;D#PDn3m0 ¿¼Âǵ½¿ÉÄÜÓдóÁ¿µÄ±í»º´æ,ËùÒÔOracleÉèÖÃÒ»¸ö²ÎÊý:CACHE_SIZE_THRESHOLD,³¬¹ý¸Ã²ÎÊýÉèÖõÄÊýÁ¿µÄÊý¾Ý¿éÊýµÄ±í,½«²»Äܱ»cacheµ½bufferÖÐ.¸Ã²ÎÊýĬÈÏBuffer CacheµÄ´óСµÄ10%,¿ÉÒÔ¸ù¾ÝÐèÒª½øÐиü¸Ä.
f\`7J)zul0 Ë÷ÒýɨÃèµÄ¿é×ÜÊÇ·ÅÔÚLRUÁ´µÄMRU¶Ë.±ê¼ÇΪcacheµÄ±í²¢ÇÒ¿éÒª±Ècache_size_threshold²ÎÊýÉèÖõÄСʱ,¸Ã±í»á´¦ÀíLRUÁ´µÄMRU¶Ë.È«±íɨÃèµÄ´ó±í¿é½«±»ÔÚLRUÁ´µÄLRU¶Ë.51TestingÈí¼þ²âÊÔÍø]G.P*u#n$r
È«±íɨÃèµÄ¼¸ºõÿ´ÎÂß¼¶Á¶¼½«µ¼ÖÂÎïÀí¶