µ¼Ö²»Ê¹ÓÃCBO¶øʹÓÃRBOµÄÈý¸öÔÒò:51TestingÈí¼þ²âÊÔÍø%K0l ^R\6K?
1.optimizer_modeÉèÖÃΪrule»òoptimizer_goalÉèÖÃΪrule
X ?q5lLZ#~d5y02.²éѯÖаüº¬ÓÐruleÌáʾ.
9qsG+`#nRAe\03.²éѯµÄËùÓÐ±í¶¼Ã»Óб»·ÖÎö¹ý,²¢ÇÒ²éѯ²»°üº¬ÈκÎÌáʾ.
I`D:h6L
bc*V0RBOµÄ¹¤×÷·½Ê½:
-RoD iUz0 RBOÔÚ¾ö¶¨SQLµÄÖ´Ðмƻ®Ê±,ֻʹÓÃÉÙÁ¿ÐÅÏ¢:
9el(y o#yG!rb0 SQLÎı¾.
'@_;i![fp{2V0 SQLµÄFROMºóÃæµÄ»ù±¾ÐÅÏ¢,Èç±í,´Ø,ÊÓͼ,²Î¿¼µÄÆäËû×ֶεÄÊý¾ÝÀàÐÍ.
k2a4YH] R0 ÓëSQLÏà¹Ø±íÉϵÄË÷Òý.
VS;n`*R1Be0 Êý¾Ý×ÖµäÐÅÏ¢Ö»¶Ô±¾µØÊý¾Ý¿âÓÐÓÃ.Èç²Î¿¼Ô¶³ÌÊý¾Ý¿â,Êý¾Ý×ÖµäÐÅÏ¢¶ÔRBOÎÞЧ.51TestingÈí¼þ²âÊÔÍø-szm/B6gu
´ØµÄȱµã³¬¹ýËüµÄÓŵã.51TestingÈí¼þ²âÊÔÍø&u-v\,d4s\
ºÜÉÙÔÚÓ¦ÓÃÖÐʹÓôØ. 51TestingÈí¼þ²âÊÔÍøL0m,C"T|$na
½áÂÛ:51TestingÈí¼þ²âÊÔÍø^*Z"_,P1wS4SD5U
RBOÖ»¿¼ÂÇÉÙÁ¿¿ÉÄܵķÃÎÊ·½Ê½.51TestingÈí¼þ²âÊÔÍø
MW1o8]%E V
J
¶ÔRBOÀ´Ëµ,Ë÷Òý×ÜÊǺõÄ,¼´Ê¹ÓеÄʱºòË÷Òý²»Ò»¶¨ºÃ.
E,O#vf#AGr;Q0 RBO¾³£Ê¹ÓÃûÓÐÒâÒåµÄÇÏÃÅÀ´½âÊÍSQL,ΪʲôFROMºóÃæµÄ˳Ðò»áÓë´ÎÏà¹Ø?!ºÜ¶àÈ˽è½âÊÍÁËCBOµÄ²»Îȶ¨ÐÔ,µ«ÊÇÓжàÉÙ´ÎË÷ÒýÖؽ¨ºóRBOµÄÖ´Ðмƻ®Òò´Ë¸Ä±äÁËÄØ.51TestingÈí¼þ²âÊÔÍø,n2Y?~` Q D:O)Uk
100%µÄÃüÖÐÂÊÒâζ×ÅûÓÐÂß¼IOµ¼ÖÂÎïÀíIO,ÕâÇå³þµØ½âÊÍÁËÒ»µã:»º´æÃüÖÐÂʶÔÓÅ»¯Ö´Ðмƻ®Ã»ÓÐʲôÒâÒå.¼õÉÙÖ´ÐÐ,¼õÉÙÂß¼IOµÄÊýÁ¿²ÅÊǸüºÃµÄÄ¿±ê.51TestingÈí¼þ²âÊÔÍø(Ute0W8S6y
Oracle×îºó²»µÃ²»Å×ÆúIO.×Ô´Ó½éÉÜRBOµ½CBO³öÏÖ²¢ºÜºÃµØÓ¦ÓÃÓÚÉú²úϵͳ,RBOÔËÐÐÁË7ÄêÖ®¾Ã.51TestingÈí¼þ²âÊÔÍøTz1[ v)bV,b6?R
CBOµÄ¹¤×÷ÔÀí:
W4oeaZ)KLEU0 CBOÊÇÒ»¸öÊýѧ´¦ÀíÆ÷,ËüʹÓù«Ê½È¥¼ÆËãSQLµÄ³É±¾.³É±¾±¾ÖÊÉϱ»×ª»»ÎªÎïÀíIO,Âß¼IOÔÚOracleµÄSGAµÄBuffer CacheÖб»ÍêÈ«´¦Àí.
KmwXo w r`0 CBOʹÓøü¶àµÄ¿ÉÄܵķÃÎÊ·½Ê½,Ëü¼ÆËãÿ¸ö¿ÉÄܵķÃÎÊ·½Ê½µÄÂß¼IOµÄÊýÁ¿,È»ºó¼òµ¥µØÑ¡Ôñ×îµÍ³É±¾µÄÒ»¸öÖ´Ðз½Ê½.51TestingÈí¼þ²âÊÔÍø)P+c7@ |&p9Fr
RBOÊÇÏßÐÔµÄÉè¼Æ,Ò»²½²½µØ×ö¾ö¶¨,ÏÈ×î¾ö¶¨,È»ºóϸö¾ö¶¨ÒԸþö¶¨Îª»ù´¡,³õʼ¾ö¶¨¶Ô×îÖյĽá¹ûÓ°ÏìºÜ´ó.
'B&e`UxE w0 ʲôÇé¿öÏ¿ÉÄܳö´í?51TestingÈí¼þ²âÊÔÍø3^JV"YI0D7U
CBOÖ»ÊÇÑ¡ÔñÖ´Ðмƻ®Öгɱ¾×îµÍµÄÒ»ÖÖ,ÖÁÉÙÁ½ÖÖÇé¿ö¿ÉÄÜ»á³ö´í:
LN(A%S+x-s7F7ve|P0 1.¿ÉÄܽÓÊÕµ½´íÎóµÄÊý¾Ý,51TestingÈí¼þ²âÊÔÍø]3od3K3n
U;h/GsV
2.Ò»¸ö»ò¶à¸ö¹«Ê½Ã»ÓаüÀ¨ÖØÒªµÄÒò×Ó»òÊÇ´íÎóµÄ.51TestingÈí¼þ²âÊÔÍøGU|4M}z3o ]T)X
ÕâÁ½¸öÎÊÌâ¹á´©Oracle7ʼÖÕ.
y7I
_6WN6t1A
}0 Oracle7ÖÐ,analyzeÃüÁî´æÔںܶàBUG,µ½ÁËOracle8ÖÐ,ºÜ¶àBUG±»ÐÞ¸´ÁË. Oracle 8iÖÐDBMS_stats°ü±ÈanalyzeÃüÁî¸üºÃµÄͳ¼ÆÐÅÏ¢.ʣϵľÍÊǶÔCBOʹÓõļÆËã³É±¾µÄ¹«Ê½½øÐÐϸ΢µÄÐÞÕýÁË.51TestingÈí¼þ²âÊÔÍøfZ0E0gxAd
CostÊÇʲôÒâ˼?
9Q.O` oT0 CBO¼ÆËãµÄ³É±¾Ö÷ÒªÓÉÎïÀíIO×é³É.ʵ¼ÊµÄ¹«Ê½:IO+CPU/1000+NetIO*1.5
;LM1X%S'Q7f)u0 ¸Ã¹«Ê½ÖÐ,IO±íʾÎïÀíIOÇëÇó, CPUÃèÊöÂß¼IOÇëÇó, NetIOÃèÊöͨ¹ýÊý¾Ý¿âÁ¬½Óµ½Ô¶³ÌÊý¾Ý¿âµÄÂß¼IOÇëÇó.ÕâÒÔΪ×ÅÎïÀíIOÊÇ×î"°º¹ó"µÄ×é³É²¿·Ö.´ÓÖÐÒ²¿ÉÒÔ¿´³ö,·Ö²¼Ê½Êý¾Ý¿âÓ¦ÓûáÓиü¸ßµÄCOST.Âß¼IOºÍÎïÀíIOµÄÇø±ðÊÇʲô? Ç°ÕßÊǶԻº´æÔÚOracle SGAµÄBuffer CacheÖеÄÊý¾Ý¿éµÄ·ÃÎÊ. ºóÕßÊÇ´ÓOracleϵͳÖÐÈ¡µÃµÄ²»»º´æÔÚBuffer CacheµÄÊý¾Ý¿éµÄÇëÇóÊý. CBO¼ÆËãËùÓпÉÄܵÄÖ´Ðмƻ®µÄÎïÀíIOµÄÇëÇóÊý.Åųý²»ÊÇ×îµÍµÄÖ´Ðмƻ®.51TestingÈí¼þ²âÊÔÍøm$DYU?)R0?|
Oracle IO¹æÔò:
1oy0|[&D,T:Y^Oi_y0 Oracle²»·ÃÎÊÐÐ,¶øÊÇ·ÃÎÊ°üº¬ÐеÄÊý¾Ý¿é,²»ÊÇÈ¥·ÃÎÊ´ÅÅÌ,¶øÊÇÊ×ÏÈÈ¥²éOracle SGAÖеÄBuffer CacheÄÚ´æÖеĿé(Âß¼¶Á).Âß¼¶Á:consistent getsºÍdb block gets(current gets). Ò»Ö¶ÁÊÇÌáÈ¡µÄÌض¨°æ±¾»òʱ¼äµãµÄSQLÊý¾Ý¿é, db block getsÊǵ±Ç°µÄʱ¼äµãÌáÈ¡µÄÊý¾Ý¿é,Ò»°ãÊÇÐÞ¸ÄÊý¾ÝÈç²åÈë¡¢¸üС¢É¾³ý¡£
#Ymp
a!M0 v$sysstat»òv$sesstatÖв»»áÓÐ"logical read"£¬Ö»ÓÐ"consistent read"ºÍ"db block gets" ,½«Á½ÕßÏà¼Ó¾ÍÊÇÁË¡£
Fg"Lil ?0 Èç¹ûÔÚBuffer CacheÖÐÕÒµ½ÁËÊý¾Ý¿é£¬Ôòϵͳ¾ÍʹÓÃÕÒµ½µÄÊý¾Ý¿é£¬Èç¹ûûÓÐÕÒµ½£¬ÏµÍ³Ö´ÐÐÎïÀí¶Á£¬½«¿é¶Áµ½Buffer CacheÖС£
N e/W){XW
~2H0 Èç¹ûÒ»¸ö·þÎñÆ÷½ø³ÌÕýÔÚÖ´ÐÐÒ»¸öÎïÀí¶Á£¬¶øÁíÒ»¸ö½ø³ÌÖ´ÐÐÂß¼¶Áʱ£¬Ã»Óз¢ÏÖ»º´æÖÐÓÐÊý¾Ý¿é£¬ÄÇô¸Ã½ø³ÌÒ²»áÈ¥½øÐÐÒ»¸öÎïÀí¶ÁÂ𣿲»£¬µÚÒ»¸ö½ø³ÌÔÚ·¢ÏÖÊý¾Ý¿éûÓÐÔÚ»º´æÖÐʱ£¬ÔÚʵ¼ÊÖ´ÐÐÎïÀí¶Á֮ǰ»áÔ¤ÏÈ·ÖÅäÒ»¸ö»º³åÆ÷¸ø¸Ã¿é£¬È»ºóËø¶¨»º³åÆ÷²¢·¢ËÍÎïÀíIOÇëÇ󣬵ڶþ¸ö½ø³ÌÊÔͼ½øÐÐÂß¼¶Á£¬¶ø»º³åÆ÷±»Ëø¶¨£¬Ôò»áPOSTÒ»¸ö"buffer busy wait"µÄµÈ´ýʼþ²¢µÈ´ý¸Ã¿é¿ÉÓ㬼õÉÙûÓбØÒªµÄIO¡£51TestingÈí¼þ²âÊÔÍøOz6ic?7JfH7U1U
ÔÚcache buffers chainÉϹ¤×÷
}*j7e`q0 Buffer CacheÊÇÒ»¸öÌîÂú»º³å¿éµÄ¸ßËÙ»º³å´æ´¢Æ÷£¬ÓÉLRU(least recently used)ÔË51TestingÈí¼þ²âÊÔÍøg&Mt
hIG r%j [
H/w
Ëã·¨Ôò¹ÜÀí,ʹÓø÷¨Ôò£¬×î¾³£±»·ÃÎʵĻº³å¿é±»·ÅÔÚÁ´±íµÄMRU(most recently used)¶Ë,жÁµ½»º³å´æ´¢Æ÷ÖеĿ鱻·ÅÖÃÔÚMRU¶Ë£¬Âß¼¶Áʱ£¬ËûÃDZ»ÒƵ½MRUÁбíÒ»¶Ë£¬Èç¹û¿éûÓб»·ÃÎÊ,Ëü¾Í»áÖð½¥µØÏòLRU¶ËÒƶ¯¡£µ±Ëü´ïµ½LRU¶ËÄ©µÄʱºò£¬Ð¶Áµ½µÄ¿é¾Í»á¸²¸Ç¸Ã»º³å¿é£¬¸Ã¿éÒ²¾ÍµôÀëÁбíµÄLRU. Oracle8iÖÐʹÓÃÁËÖеã²åÈëËã·¨À´¼õÉÙÒò¿éÒƳöÁбíÌرðÊÇ"ÈÈ"¿éÒƶ¯µ½ÁбíµÄMRU¶ËµÄlatch´¦Àí¡£51TestingÈí¼þ²âÊÔÍø0jxGH#{$w]T#c
Á½ÀàIO£º
$m[H1gv'e4S0 Oracle¶ÁÊý¾ÝÎļþÓÐÁ½ÖÐÀàÐ͵ÄIO£º
*u;HhU)J*?d0 1.µ¥¿é£¬Ëæ»ú·ÃÎʶÁÈ¡£¬Í¨³£ÊÇÓëË÷Òý·ÃÎÊÓйصÄ(¸ÃIOÇëÇó·¢Éúʱ£¬²úÉúµÈ´ýʼþdb file sequential read).
Q6}y3e
s0 2.¶à¿é£¬Ë³Ðò·ÃÎʶÁÈ¡£¬Í¨³£ÓëÈ«±íɨÃè·ÃÎÊÓйØ(¸ÃIOÇëÇó·¢Éúʱ£¬½«²úÉúdb file scattered readµÈ´ýʼþ)£¬µ«ÊÇ¿ÉÄÜÓëÅÅÐòºÍ²¢Ðвéѯ(µÈ´ýʼþ£ºdirect path read).
#Pi X-uN-HnJOM0 ÕâÁ½ÖÖIOÀàÐ͹æÔòÊ®·ÖÏàËÆ£¬½ø³ÌÐèÒªÊý¾Ý¿éʱ£¬Ê×Ïȼì²éBuffer Cache£¬Èç¹û¿é´æÔÚ£¬¾ÍʹÓÃËü£¬²¢ÇÒ°ÑËüÒƶ¯µ½LRUÁ´µÄMRU¶Ë¡£Èç¹û¿é²»´æÔÚ£¬¾Í´Ó´ÅÅÌÉϵÄÊý¾ÝÎļþ¶ÁÈ¡·Åµ½»º³åÇøÖУ¬²¢½«Ëü·ÀÖ¹µ½LRUÁ´µÄMRU¶Ë¡£51TestingÈí¼þ²âÊÔÍø h
H
s [i-G9Wh
²»¿É±ÜÃâµØ£¬Ã¿¸öжÁµ½µÄ¿é¶¼½«±»·ÅÖõ½LRUÁ´µÄMRU¶Ë£¬µ±Ç°ÃæµÄ¿é±»LRUÁ´µÄLRU¶Ëʱ£¬×îºó±»ÍƳöLRUÁ´»ò±»¸²¸Ç¡£È«±íɨÃèʱ£¬ÐèҪɨÃèµÄ¿é´óÓÚBuffer Cacheʱ£¬½«ÍêÈ«Ë¢ÐÂBuffer CacheµÄÄÚÈÝ¡£51TestingÈí¼þ²âÊÔÍøl7i T\)\Mb
D
OracleÖеĻº´æIO£º51TestingÈí¼þ²âÊÔÍøp*z
OF0]sHfYT
Oracle7ÖÐÒ»¸ö¸Ä±äÀ´·ÀÖ¹ÕâÖÖ»º³åÇø±»Ë¢ÐµÄÇé¿ö(cache flushing effect),Ê×ÏÈ,¶à¿é˳Ðò·ÃÎʵÄÈ«±íɨÃèµÄ´¦ÀíºÍLRUµÄËã·¨ÊDz»Í¬µÄ,µ±È«±íɨÃèʱ,¶Áµ½µÄ¿é½«±»·ÀÖ¹µ½LRUÁ´µÄLRU¶Ë.Ë÷ÒýɨÃèµÄµ¥¿é¶ÁûÓиıä,¼ÌÐø±»·ÅÖÃÔÚLRUÁ´µÄMRU¶Ë.È«±íɨÃèµÄÊý¾Ý¿é¼Æ»®»áÁ¢¿Ì±»¸²¸Ç,³ý·ÇÕâЩ¿éºÜ¿ì±»ÔٴηÃÎÊ(½«±»Òƶ¯µ½MRU¶Ë).
v2gd3\xKA6L0 È«±íɨÃèµÄÂß¼¶ÁÒ»°ãºÜÔÚBuffer CacheÖÐÕÒµ½ÐèÒªµÄ¿é,Òò´Ë,È«±íɨÃè×ÜÊDZíÏÖΪ´óÁ¿µÄÎïÀí¶Á. µ«ÊDZ»È«±íɨÃèÖØзÃÎʵĿéÓжà¿éµÄƵÂÊ?´ó¶àÊýÇé¿öϺÜÉÙ.ËùÒÔOracle7µÄÉè¼ÆÕß¹«ÈϵØÔÊÐí¿éºÜ¿ì±»¸²¸Ç,ͬʱ±£ÁôÏÂBuffer CacheµÄÔÀ´ÕæʵÄÚÈÝ.51TestingÈí¼þ²âÊÔÍø
a;oT3X1~5y
µ«ÊÇС±íûÓбØҪÿ´ÎÎïÀí¶Á,ÓÚÊÇËûÃÇÉè¼ÆOracle7ʱ½«Ð¡±íµÄÈ«±íɨÃè¿é·ÀÖ¹ÔÚLRUÁ´µÄMRU¶Ë,×÷ΪÌØÀý,¼ÓËÙ¶ÔС±íÈ«±íɨÃèµÄ·ÃÎÊ. ²ÎÊýSMALL_TABLE_THESHOLD´æ´¢ÁË¿ÉÒÔʹÓõÄÊý¾Ý¿éÊý,ĬÈÏdb_block_buffersµÄ2%. Oracle7ǰûÓиòÎÊý. ÓÐÈ˽«¸Ã²ÎÊýÉèÖùý´ó,ÒÑ´æ´¢¸ü¶àµÄС±í,µ«ÊÇÕâÑù½«µ¼ÖÂbuffer cache¿ÉÓñäС,·´ÎÊÈÝÒ׳öÏÖ²»Ï£Íû³öÏÖµÄcache flush.51TestingÈí¼þ²âÊÔÍø}pz s)YS
Oracle7 +°æ±¾ÖеÄIO:51TestingÈí¼þ²âÊÔÍøm8]R3Up
vld\/^3f
small_table_thresholdÒѾ±»ÉèÖÃΪÒþº¬²ÎÊý_small_table_threshold.¿¼Âǵ½¿ª·¢Õß¿ÉÄܻὫij±íפÁôÔÚBuffer CacheÖÐ,ËùÒÔÔÚ½¨±í»òÐ޸ıíµÄÓï¾äÖÐÉèÖÃÁËcacheºÍnocache¹Ø¼ü×Ö,ÉèÖÃΪcacheµÄ±í¿é½«±»·ÀÖ¹ÔÚLRUÁ´µÄMRU¶Ë.51TestingÈí¼þ²âÊÔÍøj Ym7Z ?.[
f
¿¼Âǵ½¿ÉÄÜÓдóÁ¿µÄ±í»º´æ,ËùÒÔOracleÉèÖÃÒ»¸ö²ÎÊý:CACHE_SIZE_THRESHOLD,³¬¹ý¸Ã²ÎÊýÉèÖõÄÊýÁ¿µÄÊý¾Ý¿éÊýµÄ±í,½«²»Äܱ»cacheµ½bufferÖÐ.¸Ã²ÎÊýĬÈÏBuffer CacheµÄ´óСµÄ10%,¿ÉÒÔ¸ù¾ÝÐèÒª½øÐиü¸Ä.51TestingÈí¼þ²âÊÔÍøf P,T L? ZA%^n|
Ë÷ÒýɨÃèµÄ¿é×ÜÊÇ·ÅÔÚLRUÁ´µÄMRU¶Ë.±ê¼ÇΪcacheµÄ±í²¢ÇÒ¿éÒª±Ècache_size_threshold²ÎÊýÉèÖõÄСʱ,¸Ã±í»á´¦ÀíLRUÁ´µÄMRU¶Ë.È«±íɨÃèµÄ´ó±í¿é½«±»ÔÚLRUÁ´µÄLRU¶Ë.
;~
A2L5@
vYM6w$P7d0 È«±íɨÃèµÄ¼¸ºõÿ´ÎÂß¼¶Á¶¼½«µ¼ÖÂÎïÀí¶