¡¾×ª¡¿¼òµ¥ËµÒ»Ëµ Oracle¿âµÄHang
·Ç³£ÉÙµÄÇé¿öÏ£¬ÎÒÃÇ»á¸Ð¾õÎÒÃǵĿ⡱Hang¡±ÁË£¬¼ÓÁËÒþºÅµÄÒâ˼ÊÇ˵ÓÐʱÕæµÄÊÇHangÁË£¬ÓÐһЩÔò²»ÊÇ£¬ÊÇÓÉÓÚÐÔÄܵÄÎÊÌâÒýÆðµÄ¡£ÎÒÓöµ½¹ý¼¸´ÎHangµÄÇé¿ö£¬½áºÏ×ÅÍøÉÏһЩÎÄÕ£¬°Ñ¿ÉÄܵÄÔÒò¡¢µ±Ê±ÎÒÃÇÓ¦¸Ã×öµÄһЩ²Ù×÷½øÐÐÁËÈçϵÄ×ܽᣬ²»¶ÔµÄµØ·½´ó¼Ò¿ÉÒÔ·¢Mail¸øÎÒ:crane@storren.net ¡£
Ò»¡¢Êý¾Ý¿âHangʱ¿ÉÄܵÄÏÖÏó
1¡¢×îÖ±¹ÛµÄÊÇÄãµÄ´ó²¿·ÖµÄÒµÎñ²Ù×÷£¬±ÈÈç˵һ¸ö²éѯ¶¼Ê¹Óúó¤µÄʱ¼ä£¬»ò¸ù±¾¾Í·µ»Ø²»³ö½á¹û¡£ÕâºÍ¼òµ¥ÄÇÖÖËø±íÊÇÓÐÇø±ðµÄ¡£
2¡¢ÔÚ²Ù×÷ϵͳÉÏÓÃHp-unixÓÃglance¡¢AixÓÃnmon¼°ÓÃsar×ö¼à²â»á³öÏÖϵͳ¿ÕÏеļÙÏ󣬱íÃæ¿´ÆðÀ´ÏµÍ³ºÜÏУ¬Êµ¼ÊÉÏϵͳÒѾHangÁË¡£
3¡¢²év$session_wait»á³öÏÖ´óÁ¿µÄ¡±latch free¡±¡¢¡±enqueue¡±¡¢¡± free buffer waits¡±µÈµÈ´ýʼþ£¬ÓÐʱºǫ́»á³öÏÖ´óÁ¿µÄ.trcÎļþ£¬ÁíÍâÐèÒª¹Û×¢Ò»ÏÂ$ORACLE_HOME/rdbms/logÕâ¸öλÖã¬ÓÐһЩʱºîtraceÎļþ»áÉú³Éµ½ÕâÀï¡£
¶þ¡¢Oracle¿âHangʱһЩÓÐÓõIJÙ×÷ºÍ²éѯ
1¡¢Èç¹ûҪѰÇóOracleµÄ¼¼ÊõÖ§³Ö£¬ÎÒÃÇÐèÒªdumpÒ»ÏÂOracleµÄsystemstate£¬²Ù×÷ÈçÏÂ
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';
SQL>conn / as sysdba;
SQL>set pagesize 0
SQL> SELECT * from v$parameter;
SQL>SELECT sid, id1, id2, type, lmode, request from v$lock;
SQL>SELECT * from v$session_wait ORDER BY sid; --¸ô¼¸ÃëÖظ´Ö´ÐÐ3´Î¡£
µ±µÃµ½OracleµÄ¿âÆæÂý»òHangʱ±£³ÖÀä¾²µÄÍ·ÄÔºÜÖØÒªµÄ£¬¿ÉÒÔ°´Èçϵķ½·¨½øÐÐһЩ´¦Àí£º
1¡¢Ê×ÏÈÒªµ½²Ù×÷ϵͳÉÏÈ¥£¬ÓÃglanceµÈ¹¤¾ß½øÐÐһϹ۲죬¿´Ò»ÏÂcpu¡¢ÄÚ´æ¡¢½»»»Çø¡¢´ÅÅ̵ķ±Ã¦³Ì¶ÈÓëƽʱÊDz»ÊÇÏàËƵģ¬Í¬Ê±¿´Ò»¿´ÓÐûÓÐռϵͳ×ÊÔ´Ìرð´óµÄ½ø³Ì£¬ÓÐЩʱºîÊÇÕâЩ½ø³ÌÒýÆðµÄ£¬Í¨¹ýv$processºÍv$sessionÁ½¸öÊÓͼÕÒµ½ÕâOracle½ø³ÌµÄsid,serial#,°ÑËüÓÃAlter system kill session ¡®sid,serial#¡¯;ɱµô¾ÍÐÐÁË¡£Èç¹ûƽʱͬÑùµÄʱ¶ÎÄãµÄ´ÅÅ̵ķ±Ã¦³Ì¶ÈÊÇ95%£¬¶øЩʱËüΪ20%£¬µ±È»ÊǼÙÉèÁË£¬ËµÃ÷OracleµÄ¿âû׼ÕæµÄhangÁË¡£
2¡¢Ê±¼äÀ´µÃ¼°µÄ»°Ö´ÐÐһϡ±¶þ¡±ÖеÄÓï¾äÊǺܱØÒªµÄ£¬ÕâÑù¿ÉÒÔ°ÑÕâЩÐÅÏ¢Ìá½»¸øOracleµÄsupporter£¬ÈÃÄãÃǸøÄã·ÖÎöһϣ¬²éÒ»ÏÂÔÒò¡£
3¡¢Èç¹ûÄãµÄ¿â¿ª¹éµµÁË£¬³öÏÖHangµÄʱºîÒ»¶¨Òª¿´Ò»Ï¹鵵ÈÕÖ¾µÄÎļþϵͳÊDz»ÊÇÂúÁË£¬×öÒ»¸öÍ걸һЩµÄÍø¹ÜϵͳÊǺܱØÒªµÄ¡£
4¡¢ÔÚHPϵͳÉÏ´ò¿ªÒì²½IOʱһ¶¨²»ÒªÍü¼ÇΪdbaÕâ¸ö×é¼ÓÉÏMLOCKµÄȨÏÞ£¬ÎÒ¾
Ïà¹ØÎĵµ£º
OracleÊý¾Ý¿âÓαêʹÓ÷½·¨
2009-12-30 15:27
SQLÊÇÓÃÓÚ·ÃÎÊOracleÊý¾Ý¿âµÄÓïÑÔ£¬PL/SQLÀ©Õ¹ºÍ¼ÓÇ¿ÁËSQLµÄ¹¦ÄÜ£¬ËüͬʱÒýÈëÁ˸üÇ¿µÄ³ÌÐòÂß¼, ÏÂÃæÔÚ±¾ÎÄÖн«¶ÔÓαêµÄʹÓýøÐÐһϽ²½â,Ï£Íû¿ÉÒԺʹó¼Ò¹²Í¬Ñ§Ï°½ø²½¡£
¡¡¡¡Óαê×ÖÃæÀí½â¾ÍÊÇÓζ¯µÄ¹â±ê¡£ÓαêÊÇSQLµÄÒ»¸öÄڴ湤×÷Çø£¬ÓÉϵͳ»òÓû§ÒÔ±äÁ¿µÄÐÎ ......
oracle 9i online documentation:
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage
oracle 10g online documentation:
http://www.oracle.com/pls/db102/homepage
oracle 11g online documentation:
http://www.oracle.com/pls/db111/homepage ......
Oracle 11gÓÚ2007Äê7ÔÂ11ÈÕÃÀ¹ú¶«²¿Ê±¼ä11ʱ£¨±±¾©Ê±¼ä11ÈÕ22ʱ£©Õýʽ·¢²¼£¬11gÊǼ׹ÇÎĹ«Ë¾30ÄêÀ´·¢²¼µÄ×îÖØÒªµÄÊý¾Ý¿â°æ±¾£¬¸ù¾ÝÓû§µÄÐèÇóʵÏÖÁËÐÅÏ¢ÉúÃüÖÜÆÚ¹ÜÀí£¨Information Lifecycle Management£©µÈ¶àÏС£
Ò»£®ÐÂÌØÐÔÌá¸Ù
1.Êý¾Ý¿â¹ÜÀí²¿·Ö
¡ôÊý¾Ý¿âÖØÑÝ(Database Replay)&nb ......
ʹÓÃOracleÌØÓеIJéѯÓï·¨, ¿ÉÒԴﵽʰ빦±¶µÄЧ¹û
1. Ê÷²éѯ
create table tree (
id number(10) not null primary key,
name varchar2(100) not null,
super number(10 ......
Õâ´ÎÊÇÓ¦ÀèÃ÷ÍøÂçµÄÑûÇ룬ȥÉîÛڱ߷À¼ì²éÖ¸»ÓÖÐÐĽøÐÐORACLEµÄÅàѵ¡£Åàѵ·Ö¶þÆÚ£¬Ã¿ÆÚ4Ì죬ÅÅÔÚAIXµÄÅàѵ֮ºó¡£
ÉϿεĵصã¾ÍÔÚÂÞºþ¿Ú°¶£¬Ñ§Ô±À´×Ô¸÷¸ö·ÖÕ¾£ºÉîÛÚÍ壬»Ê¸Ú¿Ú°¶£¬ÂÞºþ¿Ú°¶£¬×ÜÖ¸»ÓÖÐÐÄ£¬Ð¡Ã·É³£¬ÉõÖÁ»¹ÓÐÉÇÍ·ºÍ»ÝÖݵÄͬÐйýÀ´Ìý¿Î£¬ËùÒÔ˵ÅàѵµÄ¹æ¸ñ»¹ÊÇͦ¸ßµÄ¡£ÆäÖÐÓÐһλÑ¹Ù£¬Ìý˵»¹ÊǸ߼¶¾¯¹Ù£¬ËäȻ˾ ......