1. Oracle DB ¿¡ Á¢¼ÓÀÌ µÇ¾ú´Ù°¡, ¿¬°áÀÌ Á¾·áµÇ´Â °æ¿ì
A. ƯÁ¤ »ç¿ëÀÚ Profile ¿¡ ¼³Á¤µÈ limit ¶§¹®¿¡ ¿¬°áÀÌ Á¾·á µÇ´Â °æ¿ì Àϼöµµ ÀÖÀ½
-- È®ÀÎ
> Profile À» »ç¿ëÇÏ·Á¸é, resource_limit ÀÌ true ·Î ¼³Á¤µÇ¾î ÀÖ¾î¾ß ÇÔ -- 11gR2 Default ´Â false
SQL> select name, value from v$parameter where name like '%resource%' ;
SQL> alter system set resource_limit = true scope=spfile ; -- ¼³Á¤¹æ¹ý
> »ý¼ºµÇ¾î ÀÖ´Â, Profile µéÀ» È®ÀÎ : PROFILE ¸íÀÌ Dafault ÀÎ °ÍÀÌ ±âº»¼³Á¤ Profile ÀÔ´Ï´Ù.
SQL> select profile, resource_name, limit from dba_profiles where profile = '%NEW';
-- Á¶Ä¡¹æ¹ý
> ÇØ´ç Limit À» Á¦°ÅÇÑ Profile À» »ý¼ºÇÏ¿©, ÇØ´ç User ¿¡ ºÎ¿© (¾Æ·¡´Â Sample)
> ƯÁ¤ÇÏÁö ¾ÊÀº limit Àº Default profile À» µû¸§
SQL> ALTER PROFILE Profile_NEW limit
PASSWORD_GRACE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
idle_time UNLIMITED ;
SQL> alter user user_new profile Profile_new ;
-- ÂüÁ¶°ª
> sessions_per_user 2 => À¯Àú´ç 2°³ÀÇ ¼¼¼ÇÀ» Çã¿ë
> idle_time 1 => Á¢¼ÓÈÄ 1ºÐ µ¿¾È ¾Æ¹«·± ÀÛ¾÷ÀÌ ÀϾÁö ¾Ê´Â´Ù¸é ÀÚµ¿À¸·Î SessionÀ» Á¾·á
> connect_time 480 => Open µÈ ¼¼¼ÇÀ» 8ºÐ(480ÃÊ) µ¿¾È¸¸ »ç¿ë Çã¶ô, ºÐ´ÜÀ§ÀÇ ¿¬°á½Ã°£ Á¦ÇÑ
> LOGICAL_READS_PER_SESSION 1028 => SESSIONÀÌ ¿·ÁÀÖ´Â µ¿¾È disk³ª memory¿¡¼ ÀÐÀ» ¼ö ÀÖ´Â data block ¼ö°¡ 1024 Block
B. DB Instance Lelvel ·Î ¼³Á¤µÈ limit ¶§¹®¿¡ ¿¬°áÀÌ Á¾·á µÇ´Â °æ¿ìÀÏ ¼öµµ ÀÖÀ½
-- È®ÀÎ
SQL> select * from v$resource_limit ; |
RESOURCE_NAME | CURRENT UTILIZATION | MAX UTILIZATION | INITIAL ALLOCATION | LIMIT VALUE |
processes | 31 | 1800 | 2000 | 2000 |
sessions | 39 | 3024 | 3024 | 3024 |
enqueue_locks | 25 | 61 | 34780 | 34780 |
enqueue_resources | 22 | 59 | 13524 | UNLIMITED |
ges_procs | 0 | 0 | 0 | 0 |
ges_ress | 0 | 0 | 0 | UNLIMITED |
À§ÀÇ ¼ÀÇ® °á°ú¿¡¼ processes °¡ ÇÁ·Î¼¼½º °¹¼ö(¹é±×¶ó¿îµå+Dedicate Process), ±×¸®°í session ÀÌ µ¿½ÃÁ¢¼Ó ¼¼¼Ç ¼ö ÀÔ´Ï´Ù.
current_utilization ÀÌ ÇöÀç Á¢¼Ó Ä«¿îÆ®ÀÌ°í... (1¸í Á¢¼ÓÇÒ ¶§ ¸¶´Ù 1¾¿ Áõ°¡)
max_utilization ÀÌ ¿À¶óŬÀ» ½ÃÀÛÇÑ ÀÌ·¡·Î ÃÖ´ë·Î Á¢¼ÓÇßÀ» ¶§ ÇÇÅ© ¼öÄ¡ÀÔ´Ï´Ù
-- Á¶Ä¡
session (µ¿½ÃÁ¢¼Ó ¼¼¼Ç ¼ö) °¡ 3024 ·Î ¼³Á¤µÇ¾î Àִµ¥ ( Defailt °ªµµ 3024 , Çö ¼³Á¤°ªµµ 3024 ÀÓ),
ÇöÀç´Â session (µ¿½ÃÁ¢¼Ó ¼¼¼Ç ¼ö) °¡ 39 ÀÔ´Ï´Ù¸¸, DB ±¸µ¿ ÈÄ µ¿½ÃÁ¢¼Ó ¼¼¼Ç ¼ö°¡ 3024 ¿¡ µµ´ÞÇÒ ÀûÀÌ ÀÖÀ¸¹Ç·Î,
ÀÌ ¶§, ´õ ÀÌ»óÀÇ session µéÀÌ DB ¿¡ ¿¬°áµÇÁö ¸øÇÏ´Â »óȲÀ̾ùÀ» °ÍÀÔ´Ï´Ù. ==> µû¶ó¼ session À» 5000 °³ Á¤µµ·Î ´Ã¸± ÇÊ¿ä°¡ ÀÖ½À´Ï´Ù.
sessions °ú processes ´Â ÇÔ²¾ Áõ°¡ µÇ¾î¾ß Çϸç, sessions Àº processes * 1.1 + 5°³ ÀÌ»óÀÌ µÇ¾î¾ß ÇÕ´Ï´Ù. (DB ¹öÀü¿¡ µû¶ó »óÀÌ)
SQL> alter system set sessions = 5550 scope = spfile ; SQL> alter system set processes = 5000 scope = spfile ;
SQL> shutdown immediate ; SQL> startup ; |