Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
oracleÐÔÄÜÓÅ»¯£¬¿´¶®Ö´Ðмƻ®
 
  2514  次浏览      30
 2019-3-28
   
   
 
±à¼­ÍƼö:
±¾ÎÄÀ´×ÔÓÚ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-²é¿´Ö´Ðз½°¸

 
   
2514 ´Îä¯ÀÀ       30
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ