±à¼ÍƼö: |
±¾ÎÄÀ´×ÔÓÚcsdn£¬±¾ÎÄÖ÷Òª½éÉÜÁËÔõÑù¿´OracleµÄÖ´Ðмƻ®ÒÔ¼°Ö´Ðз½Ê½£¬oracle·ÃÎÊÊý¾ÝµÄ´æÈ¡·½·¨µÈÏà¹ØÖªÊ¶¡£ |
|
OracleµÄÖ´Ðмƻ®
µÃµ½Ö´Ðмƻ®µÄ·½Ê½
AutotraceÀý×Ó
ʹÓÃExplain
explain plan
set STATEMENT_ID='testplan'
for select * from dual; |
select lpad('
',5*(level-1))||operation operation, options,
object_name, cost,position
from plan_table
start with id=0 and STATEMENT_ID='testplan'
connect by prior id=parent_id ; |
ÔõÑù¿´Ö´Ðмƻ® 
¿´¶®Ö´Ðмƻ®Ç°ÏÈÐèÒªÁ˽âµÄһЩ֪ʶ
αÁÐ-ROWID
rowidÊÇÒ»¸öαÁУ¬¼ÈÈ»ÊÇαÁУ¬ÄÇôÕâ¸öÁоͲ»ÊÇÓû§¶¨Ò壬¶øÊÇϵͳ×Ô¼º¸ø¼ÓÉϵġ£¶Ôÿ¸ö±í¶¼ÓÐÒ»¸örowidµÄαÁУ¬µ«ÊDZíÖв¢²»ÎïÀí´æ´¢ROWIDÁеÄÖµ¡£²»¹ýÄã¿ÉÒÔÏñʹÓÃÆäËüÁÐÄÇÑùʹÓÃËü£¬µ«ÊDz»ÄÜɾ³ý¸ÄÁУ¬Ò²²»ÄܶԸÃÁеÄÖµ½øÐÐÐ޸ġ¢²åÈë¡£Ò»µ©Ò»ÐÐÊý¾Ý²åÈëÊý¾Ý¿â£¬ÔòrowidÔÚ¸ÃÐеÄÉúÃüÖÜÆÚÄÚÊÇΨһµÄ£¬¼´¼´Ê¹¸ÃÐвúÉúÐÐÇ¨ÒÆ£¬ÐеÄrowidÒ²²»»á¸Ä±ä¡£
Recursive SQL
ÓÐʱΪÁËÖ´ÐÐÓû§·¢³öµÄÒ»¸ösqlÓï¾ä£¬Oracle±ØÐëÖ´ÐÐһЩ¶îÍâµÄÓï¾ä£¬ÎÒÃǽ«ÕâЩ¶îÍâµÄÓï¾ä³ÆÖ®Îª¡®recursive
calls¡¯»ò¡®recursive SQL statements¡¯¡£Èçµ±Ò»¸öDDLÓï¾ä·¢³öºó£¬ORACLE×ÜÊÇÒþº¬µÄ·¢³öһЩrecursive
SQLÓï¾ä£¬À´ÐÞ¸ÄÊý¾Ý×ÖµäÐÅÏ¢£¬ÒÔ±ãÓû§¿ÉÒԳɹ¦µÄÖ´ÐиÃDDLÓï¾ä¡£µ±ÐèÒªµÄÊý¾Ý×ÖµäÐÅϢûÓÐÔÚ¹²ÏíÄÚ´æÖÐʱ£¬¾³£»á·¢ÉúRecursive
calls£¬ÕâЩRecursive calls»á½«Êý¾Ý×ÖµäÐÅÏ¢´ÓÓ²Å̶ÁÈëÄÚ´æÖС£Óû§²»±È¹ØÐÄÕâЩrecursive
SQLÓï¾äµÄÖ´ÐÐÇé¿ö£¬ÔÚÐèÒªµÄʱºò£¬ORACLE»á×Ô¶¯µÄÔÚÄÚ²¿Ö´ÐÐÕâЩÓï¾ä¡£µ±È»DMLÓï¾äÒ²¶¼¿ÉÄÜÒýÆðrecursive
SQL¡£¼òµ¥µÄ˵£¬ÎÒÃÇ¿ÉÒÔ½«´¥·¢Æ÷ÊÓΪrecursive SQL¡£
Row Source and Predicate
Row Source(ÐÐÔ´)£ºÓÃÔÚ²éѯÖУ¬ÓÉÉÏÒ»²Ù×÷·µ»ØµÄ·ûºÏÌõ¼þµÄÐеļ¯ºÏ£¬¼´¿ÉÒÔÊDZíµÄÈ«²¿ÐÐÊý¾ÝµÄ¼¯ºÏ£»Ò²¿ÉÒÔÊDZíµÄ²¿·ÖÐÐÊý¾ÝµÄ¼¯ºÏ£»Ò²¿ÉÒÔΪ¶ÔÉÏ2¸örow
source½øÐÐÁ¬½Ó²Ù×÷(ÈçjoinÁ¬½Ó)ºóµÃµ½µÄÐÐÊý¾Ý¼¯ºÏ¡£
Predicate(ν´Ê)£ºÒ»¸ö²éѯÖеÄWHEREÏÞÖÆÌõ¼þ
Driving Table
Driving Table(Çý¶¯±í)£º¸Ã±íÓÖ³ÆÎªÍâ²ã±í(OUTER TABLE)¡£Õâ¸ö¸ÅÄîÓÃÓÚǶÌ×ÓëHASHÁ¬½ÓÖС£Èç¹û¸Ãrow
source·µ»Ø½Ï¶àµÄÐÐÊý¾Ý£¬Ôò¶ÔËùÓеĺóÐø²Ù×÷ÓиºÃæÓ°Ïì¡£×¢Òâ´Ë´¦ËäÈ»·ÒëΪÇý¶¯±í£¬µ«Êµ¼ÊÉÏ·ÒëΪÇý¶¯ÐÐÔ´(driving
row source)¸üΪȷÇС£Ò»°ã˵À´a£¬ÊÇÓ¦ÓòéѯµÄÏÞÖÆÌõ¼þºó£¬·µ»Ø½ÏÉÙÐÐÔ´µÄ±í×÷ΪÇý¶¯±í£¬ËùÒÔÈç¹ûÒ»¸ö´ó±íÔÚWHEREÌõ¼þÓÐÓÐÏÞÖÆÌõ¼þ(ÈçµÈÖµÏÞÖÆ)£¬Ôò¸Ã´ó±í×÷ΪÇý¶¯±íÒ²ÊǺÏÊʵģ¬ËùÒÔ²¢²»ÊÇÖ»ÓнÏСµÄ±í¿ÉÒÔ×÷ΪÇý¶¯±í£¬Õýȷ˵·¨Ó¦¸ÃΪӦÓòéѯµÄÏÞÖÆÌõ¼þºó£¬·µ»Ø½ÏÉÙÐÐÔ´µÄ±í×÷ΪÇý¶¯±í¡£ÔÚÖ´Ðмƻ®ÖУ¬Ó¦¸ÃΪ¿¿ÉϵÄÄǸörow
source£¬ºóÃæ»á¸ø³ö¾ßÌå˵Ã÷¡£
Probed Table
Probed Table(±»Ì½²é±í)£º¸Ã±íÓÖ³ÆÎªÄÚ²ã±í(INNER TABLE)¡£ÔÚÎÒÃÇ´ÓÇý¶¯±íÖеõ½¾ßÌåÒ»ÐеÄÊý¾Ýºó£¬ÔڸñíÖÐѰÕÒ·ûºÏÁ¬½ÓÌõ¼þµÄÐС£ËùÒԸñíÓ¦µ±Îª´ó±í(ʵ¼ÊÉÏÓ¦¸ÃΪ·µ»Ø½Ï´órow
sourceµÄ±í)ÇÒÏàÓ¦µÄÁÐÉÏÓ¦¸ÃÓÐË÷Òý¡£
×éºÏË÷Òý(concatenated index)
Óɶà¸öÁй¹³ÉµÄË÷Òý£¬Èçcreate index idx_emp on emp(col1, col2,
col3, ¡¡)£¬ÔòÎÒÃdzÆidx_empË÷ÒýΪ×éºÏË÷Òý¡£ÔÚ×éºÏË÷ÒýÖÐÓÐÒ»¸öÖØÒªµÄ¸ÅÄÒýµ¼ÁÐ(leading
column)£¬ÔÚÉÏÃæµÄÀý×ÓÖУ¬col1ÁÐΪÒýµ¼ÁС£µ±ÎÒÃǽøÐвéѯʱ¿ÉÒÔʹÓá±where col1
= ? ¡±£¬Ò²¿ÉÒÔʹÓá±where col1 = ? and col2 = ?¡±£¬ÕâÑùµÄÏÞÖÆÌõ¼þ¶¼»áʹÓÃË÷Òý£¬µ«ÊÇ¡±where
col2 = ? ¡±²éѯ¾Í²»»áʹÓøÃË÷Òý¡£ËùÒÔÏÞÖÆÌõ¼þÖаüº¬Ïȵ¼ÁÐʱ£¬¸ÃÏÞÖÆÌõ¼þ²Å»áʹÓøÃ×éºÏË÷Òý¡£
¿ÉÑ¡ÔñÐÔ(selectivity)
±È½ÏÒ»ÏÂÁÐÖÐΨһ¼üµÄÊýÁ¿ºÍ±íÖеÄÐÐÊý£¬¾Í¿ÉÒÔÅжϸÃÁеĿÉÑ¡ÔñÐÔ¡£Èç¹û¸ÃÁеġ±Î¨Ò»¼üµÄÊýÁ¿/±íÖеÄÐÐÊý¡±µÄ±ÈÖµÔ½½Ó½ü1£¬Ôò¸ÃÁеĿÉÑ¡ÔñÐÔÔ½¸ß£¬¸ÃÁоÍÔ½Êʺϴ´½¨Ë÷Òý£¬Í¬ÑùË÷ÒýµÄ¿ÉÑ¡ÔñÐÔÒ²Ô½¸ß¡£ÔÚ¿ÉÑ¡ÔñÐԸߵÄÁÐÉϽøÐвéѯʱ£¬·µ»ØµÄÊý¾Ý¾Í½ÏÉÙ£¬±È½ÏÊʺÏʹÓÃË÷Òý²éѯ¡£
oracle·ÃÎÊÊý¾ÝµÄ´æÈ¡·½·¨£¨¸ßʵս£©

Ë÷ÒýɨÃèµÄϸ·Ö£¨Index Scan£©


±íÁ¬½Ó£¨¸ßʵս1£© 
±íÁ¬½Ó£¨¸ßʵս2£© 
±íÁ¬½Ó£¨¸ßʵս3£© 
±íÁ¬½Ó£¨¸ßʵս4£© 
²»Í¬±íÁ¬½ÓµÄÏà¶ÔËÙ¶È 
Ò»¸ö¼òµ¥µÄÖ´Ðмƻ®£¨ÔÚPLSQL-DEVELOPERÀï°´F5,²»ÊÇF4¹þ£©

ÔÙÀ´¿´2¸öÖ´Ðмƻ®£¨1£©
ÔÙÀ´¿´2¸öÖ´Ðмƻ®£¨2£© 
OracleÖеÄHints£¨Ìáʾ£© 
ÓÅ»¯Æ÷Ìáʾ 
±íÁ¬½ÓÌáʾ 
Ë÷ÒýÌáʾ 


²¢ÐÐÌáʾ 
±í·ÃÎÊÌáʾ 
Ë÷ÒýºÍSQLÓï¾äµÄÕýȷʹÓÃ
ʹÓÃORACLE×Ô´øµÄSQLPLUS 
ÈçºÎÈÃSQLPLUS¾ÝÓÐAUTOTRACE¹¦ÄÜ
ÒÔsysÓû§Á¬½Ó£»
ÔËÐÐ$ORACLE_HOME/sqlplusĿ¼ÏµÄplustrace.sql½Å±¾£»
grant plustrace to public£¬¶ÔËùÓÐÓû§ÓÐЧ£»
ÔÚsql*plus ÖÐÔËÐÐset autot onÃüÁ½«×Ô¶¯¸ú×ÙsqlµÄÖ´Ðмƻ®²¢Ìṩsqlͳ¼Æ×ÊÁÏ£»
Consistent Gets 
µÚ1¸ö²»¼Óorder byµÄSQL¿Ï¶¨±ÈµÚ2¸öSQLЧÂʸßÊÇÎãÓ¹ÖÃÒɵġ£
µ«ÊÇΪʲôµÚ2¸öSQLµÄconsistent getsÈç´ËÖ®ÉÙ?
ÔÒòÓÐÈçÏÂÁ½µã£º
ͨ³£Çé¿öÏ£¬²»ÔÚlogical RAM bufferÖеÄÊý¾ÝҪͨ¹ýphysical readsÀ´¶ÁÈ¡£¬¶øphysical
readsºóͨ³£»á½ô¸ú×ÅÒ»¸öconsistent gets¡£Òò´ËÒ»°ãÇé¿öÏÂconsistent getsÊÇÒª±Èphysical
reads´óµÄ¡£µ«ÊÇÓÐÒ»¸öÌØÀý£¬Èç¹ûphysical readsµÃµ½µÄÊý¾ÝÖ±½ÓÓÃÓÚHASH»òÕßSORT£¬ÔòÖ»¼ÇΪphysical
reads²»¼ÇΪconsistent gets¡£ËùÒÔ¼ÓÉÏorder byºóÓпÉÄÜphysical reads¶àµ«consistent
getsÉÙ¡£²»¹ýÕâ¸öÔÒò²»ÊÇÎÒÕâÀïÏÖÏó²úÉúµÄÔÒò£¬ÒòΪÎÒÕâ¸öʵÑéÀï¸ù±¾Ã»ÓÐphysical reads¡£
arraysizeµÄÓ°Ïì¡£arraysizeÊÇÖ¸¶ÁÈ¡Êý¾Ýʱһ´Î¶ÁÈ¡µÃµ½µÄÐÐÊý¡£Õâ¸öֵĬÈÏΪ15£¬Ê¹ÓÃshow
arraysizeÃüÁî¿ÉÒԲ鿴¡£Ò»¸öÊý¾Ý¿éÀýÈçÓÐ100Ìõ¼Ç¼£¬ÄÇô²¢²»ÊǶÁÈ¡Õâ¸ö¿éÒ»´Î¾ÍÄÜÈ¡µ½ËùÓÐÊý¾Ý£¬ÒÔarraysize=15ΪÀý£¬¾ÍÒªÓÐ100/15=7´Îconsistent
gets¡£°ÑarraysizeÉèÖõôóÒ»µã¿ÉÒÔ½µµÍconsistent gets£¬²»¹ýÓÐʱºò¿ÉÄÜ»áÏûºÄ¸ü¶àµÄ×ÊÔ´¡£Èç¹ûÎÒÃÇ×öselect
count(0) from test;²Ù×÷£¬ÄÇôOracle»á°ÑarraysizeÔÝʱÉèΪtestµÄÐÐÊý£¬Òò´Ëconsistent
gets»áºÜÉÙ¡£ºÜÉÙ£º 
AUTOTRACEµÄ¼¸¸ö³£ÓÃÑ¡Ïî
set autotrace off £º ²»Éú³Éautotrace ±¨¸æ£¬ÕâÊÇȱʡģʽ
set autotrace on explain£º autotraceÖ»ÏÔʾÓÅ»¯Æ÷Ö´Ðз¾¶±¨¸æ
set autotrace on statistics£º Ö»ÏÔʾִÐÐͳ¼ÆÐÅÏ¢
set autotrace on£º °üº¬Ö´Ðмƻ®ºÍͳ¼ÆÐÅÏ¢
set autotrace traceonly£º ͬset autotrace on£¬µ«ÊDz»ÏÔʾ²éѯÊä
set autotrace on explain 
set autotrace on statistics 
set autotrace traceonly 
set autotrace traceonly explain 
ÁíÒ»Öֲ鿴SQL¼Æ»®µÄ·½Ê½-Explain plan
˵Ã÷£ºÓÃÒԲ鿴SQLÓï¾äµÄÖ´Ðмƻ®
×¼±¸£º
ÔËÐÐ$ORACLE_HOME/rdbms/adminĿ¼ÏµÄutlxplan.sql½Å±¾
½¨Á¢plan_table±í
Ö´Ðз½°¸£ºexplain plan for SQL
Explain Plan-²é¿´Ö´Ðз½°¸ 
|