ÈκÎÊÂÇ鶼ÓÐËüµÄÔ´Í·£¬Òª½â¾öÎÊÌ⣬ҲµÃ´ÓÔ´Í·¿ªÊ¼£¬Ó°ÏìORACLEÐÔÄܵÄÔ´Í··Ç³£¶à£¬Ö÷Òª°üÀ¨ÈçÏ·½Ãæ:
Êý¾Ý¿âµÄÓ²¼þÅäÖÃ:CPU¡¢ÄÚ´æ¡¢ÍøÂçÌõ¼þ
1. CPU:ÔÚÈκλúÆ÷ÖÐCPUµÄÊý¾Ý´¦ÀíÄÜÁ¦ÍùÍùÊǺâÁ¿¼ÆËã»úÐÔÄܵÄÒ»¸ö±êÖ¾£¬²¢ÇÒORACLEÊÇÒ»¸öÌṩ²¢ÐÐÄÜÁ¦µÄÊý¾Ý¿âϵͳ£¬ÔÚCPU·½ÃæµÄÒªÇó¾Í¸ü¸ßÁË£¬Èç¹ûÔËÐжÓÁÐÊýÄ¿³¬¹ýÁËCPU´¦ÀíµÄÊýÄ¿£¬ÐÔÄܾͻáϽµ£¬ÎÒÃÇÒª½â¾öµÄÎÊÌâ¾ÍÊÇÒªÊʵ±Ôö¼ÓCPUµÄÊýÁ¿ÁË£¬µ±È»ÎÒÃÇ»¹¿ÉÒÔ½«ÐèÒªÐí¶à×ÊÔ´µÄ½ø³Ì KILLµô;
2. ÄÚ´æ:ºâÁ¿»úÆ÷ÐÔÄܵÄÁíÍâÒ»¸öÖ¸±ê¾ÍÊÇÄÚ´æµÄ¶àÉÙÁË£¬ÔÚORACLEÖÐÄÚ´æºÍÎÒÃÇÔÚ½¨Êý¾Ý¿âÖеĽ»»»Çø½øÐÐÊý¾ÝµÄ½»»»£¬¶ÁÊý¾Ýʱ£¬´ÅÅÌI/O±ØÐëµÈ´ýÎïÀí I/O²Ù×÷Íê³É£¬ÔÚ³öÏÖORACLEµÄÄÚ´æÆ¿¾±Ê±£¬ÎÒÃǵÚÒ»¸öÒª¿¼ÂǵÄÊÇÔö¼ÓÄڴ棬ÓÉÓÚI/OµÄÏìӦʱ¼äÊÇÓ°ÏìORACLEÐÔÄܵÄÖ÷Òª²ÎÊý£¬ÎÒ½«ÔÚÕâ·½Ãæ½øÐÐÏêϸµÄ½²½â
3. ÍøÂçÌõ¼þ:NET*SQL¸ºÔðÊý¾ÝÔÚÍøÂçÉϵÄÀ´Íù£¬´óÁ¿µÄSQL»áÁîÍøÂçËٶȱäÂý¡£±ÈÈç10MµÄÍø¿¨ºÍ100µÄÍø¿¨¾Í¶ÔNET*SQLÓзdz£Ã÷ÏÔµÄÓ°Ï죬»¹Óн»»»»ú¡¢¼¯ÏßÆ÷µÈµÈÍøÂçÉ豸µÄÐÔÄܶÔÍøÂçµÄÓ°ÏìºÜÃ÷ÏÔ£¬½¨ÒéÔÚÈκÎÍøÂçÖв»ÒªÊÔͼÓÃ3¸ö¼¯ÏßÆ÷À´½«Íø¶Î»¥Áª¡£
OS²ÎÊýµÄÉèÖÃ
ϱí¸ø³öÁËOSµÄ²ÎÊýÉèÖü°ËµÃ÷£¬DBA¿ÉÒÔ¸ù¾Ýʵ¼ÊÐèÒª¶ÔÕâЩ²ÎÊý½øÐÐÉèÖÃ
Äں˲ÎÊýÃû
˵Ã÷
bufpages
¶Ôbuffer¿Õ¼ä²»°´¾²Ì¬·ÖÅ䣬²ÉÓö¯Ì¬·ÖÅ䣬ʹbufpagesÖµËænbufÒ»Æð¶Ôbuffer¿Õ¼ä½øÐж¯Ì¬·ÖÅä¡£
Create_fastlinks
¶ÔHFSÎļþϵͳÔÊÐí¿ìËÙ·ûºÅÁ´½Ó
dbc_max_pct
¼Ó´ó×î´ó¶¯Ì¬buffer¿Õ¼äËùÕ¼ÎïÀíÄÚ´æµÄ°Ù·Ö±È£¬ÒÔÂú×ãÓ¦ÓÃϵͳµÄ¶ÁдÃüÖÐÂʵÄÐèÒª¡£
Dbc_min_pct
ÉèÖÃ×îС¶¯Ì¬buffer¿Õ¼äËùÕ¼ÎïÀíÄÚ´æµÄ°Ù·Ö±È
desfree
Ìá¸ß¿ªÊ¼½»»»²Ù×÷µÄ×îµÍ¿ÕÏÐÄÚ´æÏÂÏÞ£¬±£ÕÏϵͳµÄÎȶ¨ÐÔ£¬·ÀÖ¹³öÏÖ²»¿ÉÔ¤¼ûµÄϵͳ±ÀÀ£(Crash)¡£
Fs_async
ÔÊÐí½øÐдÅÅÌÒì²½²Ù×÷£¬Ìá¸ßCPUºÍ´ÅÅ̵ÄÀûÓÃÂÊ
lotsfree
Ìá¸ßϵͳ½â³ý»»Ò³²Ù×÷µÄ¿ÕÏÐÄÚ´æµÄÉÏÏÞÖµ£¬±£Ö¤Ó¦ÓóÌÐòÓÐ×ã¹»µÄ¿ÉÓÃÄÚ´æ¿Õ¼ä¡£
Maxdsiz
Õë¶ÔϵͳÊý¾ÝÁ¿´óµÄÌص㣬¼Ó´ó×î´óÊý¾Ý¶ÎµÄ´óС£¬±£Ö¤Ó¦ÓõÄÐèÒª¡£(32λ)
maxdsiz_64bit
maximum process data segment size for 64_bit
Maxssiz
¼Ó´ó×î´ó¶ÑÕ»¶ÎµÄ´óС¡£(32_bit)
maxssiz_64bit
¼Ó´ó×î´ó¶ÑÕ»¶ÎµÄ´óС¡£(64_bit)
Maxtsiz
Ìá¸ß×î´ó´úÂë¶Î´óС£¬Âú×ãÓ¦ÓÃÒªÇó
maxtsiz_64bit
ÔÖµ¹ý´ó£¬Ó¦µ÷С
Minfree
Ìá¸ßÍ£Ö¹½»»»²Ù×÷µÄ×ÔÓÉÄÚ´æµÄÉÏÏÞ
Shmem
ÔÊÐí½øÐÐÄÚ´æ¹²Ïí£¬ÒÔÌá¸ßÄÚ´æµÄÀûÓÃÂÊ
Shmmax
ÉèÖÃ×î´ó¹²ÏíÄÚ´æ¶ÎµÄ´óС£¬ÍêÈ«Âú×ãÄ¿Ç°µÄÐèÒª
Timeslice
ÓÉÓÚϵͳµÄÆ¿¾±Ö÷Òª·´Ó³ÔÚ´ÅÅÌI/OÉÏ£¬Òò´Ë¡¡½µµÍʱ¼äƬµÄ´óС£¬Ò»·½Ãæ¿É±ÜÃâÒò´ÅÅÌI/O²»³©Ôì³ÉCPUµÄµÈ´ý£¬´Ó¶øÌá¸ßÁËCPUµÄ×ÛºÏÀûÓÃÂÊ¡£ÁíÒ»·½Ãæ¼õÉÙÁ˽ø³ÌµÄ×èÈûÁ¿¡£
Unlockable_mem
Ìá¸ßÁ˲»¿ÉËøÄÚ´æµÄ´óС£¬Ê¹¿ÉÓÃÓÚ»»Ò³ºÍ½»»»µÄÄÚ´æ¿Õ¼äÀ©´ó,ÓÃÒÔÂú×ãϵͳ¶ÔÄÚ´æ¹ÜÀíµÄÒªÇó¡£
Óû§SQLÖÊÁ¿
ÒÔÉϽ²µÄ¶¼ÊÇÓ²¼þ·½ÃæµÄ¶«Î÷£¬ÔÚÌõ¼þÓÐÏÞµÄÌõ¼þÏ£¬ÎÒÃÇ¿ÉÒÔµ÷ÕûÓ¦ÓóÌÐòµÄSQLÖÊÁ¿:
1. ²»Òª½øÐÐÈ«±íɨÃè(Full Table Scan):È«±íɨÃèµ¼Ö´óÁ¿µÄI/O
2. ¾¡Á¿½¨ºÃºÍʹÓúÃË÷Òý:½¨Ë÷ÒýÒ²ÊÇÓн²¾¿µÄ£¬ÔÚ½¨Ë÷Òýʱ£¬Ò²²»ÊÇË÷ÒýÔ½¶àÔ½ºÃ£¬µ±Ò»¸ö±íµÄË÷Òý´ïµ½4¸öÒÔÉÏʱ£¬ORACLEµÄÐÔÄÜ¿ÉÄÜ»¹ÊǸÄÉƲ»ÁË£¬ÒòΪ OLTPϵͳÿ±í³¬¹ý5¸öË÷Òý¼´»á½µµÍÐÔÄÜ£¬¶øÇÒÔÚÒ»¸ösql ÖУ¬ oracle ´Ó²»ÄÜʹÓó¬¹ý 5¸öË÷Òý;µ±ÎÒÃÇÓõ½GROUP BYºÍORDER BYʱ,ORACLE¾Í»á×Ô¶¯¶ÔÊý¾Ý½øÐÐÅÅÐò,¶øORACLEÔÚINIT.ORAÖоö¶¨ÁËsort_area_sizeÇøµÄ´óС,µ±ÅÅÐò²»ÄÜÔÚÎÒÃǸø¶¨µÄÅÅÐòÇøÍê³Éʱ,ORACLE¾Í»áÔÚ´ÅÅÌÖнøÐÐÅÅÐò,Ò²¾ÍÊÇÎÒÃǽ²µÄÁÙʱ±í¿Õ¼äÖÐÅÅÐò, ¹ý¶àµÄ´ÅÅÌÅÅÐò½«»áÁî free buffer waits µÄÖµ±ä¸ß,¶øÕâ¸öÇø¼ä²¢²»Ö»ÊÇÓÃÓÚÅÅÐòµÄ,¶ÔÓÚ¿ª·¢ÈËÔ±ÎÒÌá³öÈçÏÂÖÒ¸æ:
1)¡¢select,update,delete Óï¾äÖеÄ×Ó²éѯӦµ±ÓйæÂɵزéÕÒÉÙÓÚ20%µÄ±íÐÐ.Èç¹ûÒ»¸öÓï¾ä²éÕÒµÄÐÐÊý³¬¹ý×ÜÐÐÊýµÄ20%,Ëü½«²»ÄÜͨ¹ýʹÓÃË÷Òý»ñµÃÐÔÄÜÉϵÄÌá¸ß.
2)¡¢Ë÷Òý¿ÉÄܲúÉúËéƬ,ÒòΪ¼Ç¼´Ó±íÖÐɾ³ýʱ,ÏàÓ¦Ò²´Ó±íµÄË÷ÒýÖÐɾ³ý.±íÊͷŵĿռä¿ÉÒÔÔÙÓÃ,¶øË÷ÒýÊͷŵĿռäÈ´²»ÄÜÔÙÓÃ.Ƶ·±½øÐÐɾ³ý²Ù×÷µÄ±»Ë÷ÒýµÄ±í,Ó¦µ±½×¶ÎÐÔµØÖؽ¨Ë÷Òý,ÒÔ±ÜÃâÔÚË÷ÒýÖÐÔì³É¿Õ¼äËéƬ,Ó°ÏìÐÔÄÜ.ÔÚÐí¿ÉµÄÌõ¼þÏÂ,Ò²¿ÉÒԽ׶ÎÐÔµØtruncate±í,truncateÃüÁîɾ³ý±íÖÐËùÓмǼ,Ҳɾ³ýË÷ÒýËéƬ.
3)¡¢ÔÚʹÓÃË÷Òýʱһ¶¨Òª°´Ë÷Òý¶ÔÓ¦×ֶεÄ˳Ðò½øÐÐÒýÓá£
4)¡¢ÓÃ(+)±ÈÓÃNOT IN¸üÓÐЧÂÊ¡£
½µµÍORACLEµÄ¾ºÕù:
ÏȽ²¼¸¸öORACLEµÄ¼¸¸ö²ÎÊý£¬Õ⼸¸ö²ÎÊý¹Øϵµ½ORACLEµÄ¾ºÕù:
1)¡¢freelists ºÍ freelist ×é:ËûÃǸºÔðORACLEµÄ´¦Àí±íºÍË÷ÒýµÄ¿Õ¼ä¹ÜÀí;
2)¡¢pctfree ¼° pctused:¸Ã²ÎÊý¾ö¶¨ÁËfreelists ºÍ freelist ×éµÄÐÐΪ£¬pctfree ºÍpctused ²ÎÊýµÄΨһĿµÄ¾ÍÊÇΪÁË¿ØÖÆ¿éÈçºÎÔÚ freelists Öнø³ö
ÉèÖúÃpctfree ¼° pctused¶Ô¿éÔÚfreelistsµÄÒÆ×ߺͶÁÈ¡ºÜÖØÒª¡£
ÆäËû²ÎÊýµÄÉèÖÃ
1)¡¢°üÀ¨SGAÇø(ϵͳȫ¾ÖÇø):ϵͳȫ¾ÖÇø(SGA)ÊÇÒ»¸ö·ÖÅä¸øOracle µÄ°üº¬Ò»¸ö oracle ʵÀýµÄÊý¾Ý¿âµÄ¿ØÖÆÐÅÏ¢ÄÚ´æ¶Î¡£
Ö÷Òª°üÀ¨Êý¾Ý¿â¸ßËÙ»º´æ(the database buffer cache)£¬
ÖØÑÝÈÕÖ¾»º´æ(the redo log buffer)£¬
¹²Ïí³Ø(the shared pool)£¬
Êý¾Ý×ֵ仺´æ(the data dictionary cache)ÒÔ¼°ÆäËü¸÷·½ÃæµÄÐÅÏ¢
2)¡¢db_block_buffers(Êý¾Ý¸ßËÙ»º³åÇø)·ÃÎʹýµÄÊý¾Ý¶¼·ÅÔÚÕâһƬÄÚ´æÇøÓò£¬¸Ã²ÎÊýÔ½´ó£¬OracleÔÚÄÚ´æÖÐÕÒµ½ÏàͬÊý¾ÝµÄ¿ÉÄÜÐÔ¾ÍÔ½´ó£¬Ò²¼´¼Ó¿ìÁ˲éѯËٶȡ£
3)¡¢share_pool_size (SQL¹²Ïí»º³å³Ø):¸Ã²ÎÊýÊÇ¿â¸ßËÙ»º´æºÍÊý¾Ý×ÖµäµÄ¸ßËÙ»º´æ¡£
4)¡¢Log_buffer (ÖØÑÝÈÕÖ¾»º³åÇø)
5)¡¢sort_area_size(ÅÅÐòÇø)
6)¡¢processes (ͬʱÁ¬½ÓµÄ½ø³ÌÊý)
7)¡¢db_block_size (Êý¾Ý¿â¿é´óС):OracleĬÈÏ¿éΪ2KB£¬Ì«Ð¡ÁË£¬ÒòΪÈç¹ûÎÒÃÇÓÐÒ»¸ö8KBµÄÊý¾Ý£¬Ôò2KB¿éµÄÊý¾Ý¿âÒª¶Á4´ÎÅÌ£¬²ÅÄܶÁÍ꣬¶ø8KB¿éµÄÊý¾Ý¿âÖ»Òª1´Î¾Í¶ÁÍêÁË£¬´ó´ó¼õÉÙÁËI/O²Ù×÷¡£Êý¾Ý¿â°²×°Íê³Éºó£¬¾Í²»ÄÜÔٸıädb_block_sizeµÄÖµÁË£¬Ö»ÄÜÖØн¨Á¢Êý¾Ý¿â²¢ÇÒ½¨¿âʱ£¬ÒªÑ¡ÔñÊÖ¹¤°²×°Êý¾Ý¿â¡£
8)¡¢open_links (ͬʱ´ò¿ªµÄÁ´½ÓÊý)
9)¡¢dml_locks
10)¡¢open_cursors (´ò¿ª¹â±êÊý)
11)¡¢dbwr_io_slaves (ºǫ́д½ø³ÌÊý)