ORACLE SQLÖ´Ðв½Öè
ORACLE sql µÄ´¦Àí¹ý³Ì´óÖÂÈçÏ£º
1.ÔËÓÃHASHËã·¨£¬µÃµ½Ò»¸öHASHÖµ£¬Õâ¸öÖµ¿ÉÒÔͨ¹ýV$SQLAREA.HASH_VALUE ²é¿´
2.µ½shared pool ÖÐµÄ library cache ÖвéÕÒÊÇ·ñÓÐÏàͬµÄHASHÖµ£¬Èç¹û´æÔÚ£¬ÔòÎÞÐèÓ²½âÎö£¬½øÐÐÈí½âÎö
3.Èç¹ûshared pool²»´æÔÚ´ËHASHÖµ£¬Ôò½øÐÐÓï·¨¼ì²é£¬²é¿´ÊÇ·ñÓÐÓï·¨´íÎó
4.Èç¹ûûÓÐÓï·¨´íÎ󣬾ͽøÐÐÓïÒå¼ì²é£¬¼ì²é¸ÃSQLÒýÓõĶÔÏóÊÇ·ñ´æÔÚ£¬¸ÃÓû§ÊÇ·ñ¾ßÓзÃÎʸöÔÏóµÄȨÏÞ
5.Èç¹ûûÓÐÓïÒå´íÎ󣬶ԸÃSQL½øÐнâÎö£¬Éú³É½âÎöÊ÷£¬Ö´Ðмƻ®
6.Éú³ÉORACLEÄÜÔËÐеĶþ½øÖÆ´úÂ룬ÔËÐиôúÂë²¢ÇÒ·µ»Ø½á¹û¸øÓû§
Ó²½âÎöºÍÈí½âÎö¶¼ÔÚµÚ5²½½øÐÐ
Ó²½âÎöͨ³£ÊÇ°º¹óµÄ²Ù×÷£¬´óÔ¼Õ¼Õû¸öSQLÖ´ÐеÄ70%×óÓÒµÄʱ¼ä£¬Ó²½âÎö»áÉú³ÉÖ´ÐÐÊ÷£¬Ö´Ðмƻ®£¬µÈµÈ¡£
µ±ÔÙ´ÎÖ´ÐÐͬһÌõSQLÓï¾äµÄʱºò£¬ÓÉÓÚ·¢ÏÖlibrary cacheÖÐÓÐÏàͬµÄHASHÖµ£¬Õâ¸öʱºò²»»áÓ²½âÎö£¬¶ø»áÈí½âÎö£¬
ÄÇôÈí½âÎö¾¿¾¹ÊǸÉÁËʲôÄØ?ÆäʵÈí½âÎö¾ÍÊÇÌø¹ýÁËÉú³É½âÎöÊ÷£¬Éú³ÉÖ´Ðмƻ®Õâ¸öºÄʱÓÖºÄCPUµÄ²Ù×÷£¬Ö±½ÓÀûÓÃÉú³ÉµÄÖ´Ðмƻ®ÔËÐÐ
¸ÃSQLÓï¾ä¡£
ÏÂÃæÕª³eygleÉîÈë½âÎöORACLE ÖйØÓÚSQLÖ´Ðйý³ÌµÄÃèÊö
1.Ê×ÏÈ»ñµÃlibrary cache latch,¸ù¾ÝSQLµÄHASH_VALUEÔÚlibrary cacheÖвéÕÒÊÇ·ñ´æÔÚ´ËHASH_VALUE£¬Èç¹ûÕÒµ½Õâ¸öHASH_VALUE£¬³Æ֮ΪÈí½âÎö£¬Server»ñµÃ¸ÄSQLÖ´Ðмƻ®×ªÏòµÚ4²½£¬Èç¹ûÕÒ²»µ½¹²Ïí´úÂë¾Í½øÐÐÓ²½âÎö¡£
2.ÊÍ·Ålibrary pool cache,»ñµÃshared pool latch,²éÕÒ²¢Ëø¶¨×ÔÓÉ¿Õ¼ä(ÔÚbucket ÖвéÕÒchunk)¡£Èç¹ûÕÒ²»µ½£¬±¨ORA-04031´íÎó
3.ÊÍ·Åshared pool latch,ÖØлñµÃlibrary cache latch,½«SQLÖ´Ðмƻ®·ÅÈëlibrary cacheÖС£
4.ÊÍ·Ålibrary cache latch,±£³ÖnullģʽµÄlibrary cache pin/lock.
5.¿ªÊ¼Ö´ÐС£
Library cache latch¿ÉÒÔÀí½âΪӲ/Èí½âÎöµÄʱºò·¢ÉúµÄ£¬ÒòΪ½âÎöµÄʱºò»áËÑË÷library cache£¬ËùÒÔ»á²úÉúlibrary cache latch
Library cache pin ÊÇÔÚÖ´ÐеĽ׶η¢ÉúµÄ¡£
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
³ö´¦£ºhttp://hi.baidu.com/vcy168/blog/item/877945d908c3d52611df9ba0.html
WindowsÏÂORACLE 10g°²×°Óë²Ù×÷ͼ½â
¸Õ¸Õ½Ó´¥ORACLEµÄÈËÀ´Ëµ,´ÓÄÇÀïѧ,ÈçºÎѧ,ÓÐÄÇЩ¹¤¾ß¿ÉÒÔʹÓÃ,Ó¦¸ÃÖ´ÐÐʲô²Ù×÷,Ò»¶¨»Ø¸Ðµ½ÎÞÖú¡£ËùÒÔÔÚѧϰʹÓÃORACLE֮ǰ£¬Ê×ÏÈÀ´°²×°Ò»ÏÂORACLE 10g£¬ÔÚÀ´ÕÆÎÕÆä»ù±¾¹¤¾ß¡£Ë×»°ËµµÄºÃ£º¹¤ÓûÉÆÆäÊ ......
Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from ......
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ©ÖÐÖ´Ð ......
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb)
SQL: select * into b from a where 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb)
SQL: insert into b(a, b, c) select d, e, f from b;
3. ˵Ã÷£º ......