database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
¤ýOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB ¹®¼­µé
½ºÅ͵ð
Community
°øÁö»çÇ×
ÀÚÀ¯°Ô½ÃÆÇ
±¸ÀÎ|±¸Á÷
DSN °¶·¯¸®
µµ¿òÁֽźеé
Admin
¿î¿µ°Ô½ÃÆÇ
ÃֱٰԽù°
Oracle Q&A 27232 °Ô½Ã¹° Àбâ
No. 27232
oracleÀ» »ç¿ëÇϰí ÀÖ´Â sessionÀÌ ³Ê¹« ¸¹¾Æ¿ä~
ÀÛ¼ºÀÚ
¼ÛÀºÁ¤
ÀÛ¼ºÀÏ
2006-07-08 16:46:34
Á¶È¸¼ö
2,924

oracleÀ» »ç¿ëÇÏ´Â applicationÀÌ ³Ê¹« ¸¹¾Æ¼­

sessionÀ» ´Ù Àâ°í ÀÖÀ¸¸é

oracleÀÌ cpu »ç¿ë·®ÀÌ 70%°¡·® µÇ¿ä...

±×·¡¼­ ½Ã½ºÅÛÀÌ ´À·Á¼­ ¼­ºñ½º°¡ Èûµé¾î¿ä.

°¢ application ¿¡¼­ session closeÀ» ÇØÁà¾ß ÇÏÁö¸¸

oracle ¿¡¼­ ÀÚüÀûÀ¸·Î sessionµéÀ»

ÀûÁ¤ ½Ã°£µÇ¸é ²÷¾îÁÙ¼ö ÀÖ³ª¿ä?

timeout °ü·Ã parameter°¡ ÀÖÀ»°Í °°°í...

¾Ë·ÁÁÖ½Ã¸é °¨»çÇÕ´Ï´Ù.

ÀÌ ±Û¿¡ ´ëÇÑ ´ñ±ÛÀÌ ÃÑ 3°Ç ÀÖ½À´Ï´Ù.

Á¦°¡ ¸¸µé¾î ³õÀº ¹®¼­¸¦ ´ë½Å ¿Ã¸³´Ï´Ù. ÂüÁ¶Çϼ¼¿ä.


No. Profile_1

Oracle9i Profile_1
==================

PURPOSE
-------

ProfileÀ» ÀÌ¿ëÇÑ »ç¿ëÀÚ ÀÚ¿ø °ü¸®¿¡ ´ëÇØ¼­ ¾Ë¾Æº¸ÀÚ

Explanation
-----------

(1) ProfileÀ» »ç¿ëÇÒ ¼ö ÀÖµµ·Ï ȯ°æ ¼³Á¤

SQL> connect system
Enter password:
Connected.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources                    integer     968
resource_limit                       boolean     FALSE
resource_manager_plan                string

SQL> alter system set resource_limit = true scope=spfile;
System altered.

# resource_limit¸¦ true·Î º¯°æÇÑ ÈÄ DB Àç½ÃÀÛ.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  236000476 bytes
Fixed Size                   451804 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources                    integer     968
resource_limit                       boolean     TRUE
resource_manager_plan                string

(2) Profile¿¡´Â ¾î¶²°ÍµéÀÌ ÀÖ´ÂÁö Á¶È¸ÇØ º¸ÀÚ

SQL> desc dba_profiles;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE                                   NOT NULL VARCHAR2(30)
 RESOURCE_NAME                             NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                      VARCHAR2(8)
 LIMIT                                              VARCHAR2(40)

SQL> select resource_name, resource_type from dba_profiles;

RESOURCE_NAME                    RESOURCE
-------------------------------- --------
COMPOSITE_LIMIT                  KERNEL
FAILED_LOGIN_ATTEMPTS            PASSWORD
SESSIONS_PER_USER                KERNEL
PASSWORD_LIFE_TIME               PASSWORD
CPU_PER_SESSION                  KERNEL
PASSWORD_REUSE_TIME              PASSWORD
CPU_PER_CALL                     KERNEL
PASSWORD_REUSE_MAX               PASSWORD
LOGICAL_READS_PER_SESSION        KERNEL
PASSWORD_VERIFY_FUNCTION         PASSWORD
LOGICAL_READS_PER_CALL           KERNEL

RESOURCE_NAME                    RESOURCE
-------------------------------- --------
PASSWORD_LOCK_TIME               PASSWORD
IDLE_TIME                        KERNEL
PASSWORD_GRACE_TIME              PASSWORD
CONNECT_TIME                     KERNEL
PRIVATE_SGA                      KERNEL

(3) Profile »ý¼º

SQL> create profile test_profile limit
  2  sessions_per_user 2
  3  cpu_per_session unlimited
  4  cpu_per_call 6000
  5  idle_time 1
  6  connect_time 480
  7  /
Profile created.

# ¼¼¼Ç´ç 2¸íÀÇ À¯Àú¸¦ Çã¿ëÇϰí SessionÀÇ Á¢¼ÓÈÄ 1ºÐ µ¿¾È ¾Æ¹«·± ÀÛ¾÷ÀÌ ÀϾÁö ¾Ê´Â
  ´Ù¸é ÀÚµ¿À¸·Î SessionÀ» Á¾·áÇÑ´Ù.
 
# ProfileÀ» »ý¼ºÇÏ¿© »ç¿ëÀÚ¿¡°Ô ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é »õ·Ó°Ô »ý¼ºµÇ´Â ¸ðµç »ç¿ëÀÚ´Â Default·Î
  Á¤ÀÇµÈ ¸ðµç ProfileÀÇ ±â´ÉÀ» ¼öÇàÇÒ¼ö ÀÖ´Ù.

# connect_timeÀº 8ºÐ( 480 )µ¿¾È¸¸ SessionÀ» »ç¿ëÇÒ ¼ö ÀÖ°Ô Á¦ÇÑÇÑ´Ù.

SQL> select profile, resource_name, limit from dba_profiles
  2  where profile = 'TEST_PROFILE';

PROFILE                        RESOURCE_NAME                    LIMIT
------------------------------ -------------------------------- --------------
TEST_PROFILE                   COMPOSITE_LIMIT                  DEFAULT
TEST_PROFILE                   SESSIONS_PER_USER                2
TEST_PROFILE                   CPU_PER_SESSION                  UNLIMITED
TEST_PROFILE                   CPU_PER_CALL                     6000
TEST_PROFILE                   LOGICAL_READS_PER_SESSION        DEFAULT
TEST_PROFILE                   LOGICAL_READS_PER_CALL           DEFAULT
TEST_PROFILE                   IDLE_TIME                        1
TEST_PROFILE                   CONNECT_TIME                     480
TEST_PROFILE                   PRIVATE_SGA                      DEFAULT
TEST_PROFILE                   FAILED_LOGIN_ATTEMPTS            DEFAULT
TEST_PROFILE                   PASSWORD_LIFE_TIME               DEFAULT
TEST_PROFILE                   PASSWORD_REUSE_TIME              DEFAULT
TEST_PROFILE                   PASSWORD_REUSE_MAX               DEFAULT
TEST_PROFILE                   PASSWORD_VERIFY_FUNCTION         DEFAULT
TEST_PROFILE                   PASSWORD_LOCK_TIME               DEFAULT
TEST_PROFILE                   PASSWORD_GRACE_TIME              DEFAULT

# ¼³Á¤ÇÑ ProfileÀÌ Á¦´ë·Î Àû¿ëµÇ¾ú´ÂÁö È®ÀÎ.

(4) »ý¼ºÇÑ ProfileÀ» »ç¿ëÀÚ¿¡°Ô Àû¿ë½ÃŰ°í °á°ú¸¦ È®ÀÎÇÑ´Ù

SQL> alter user kigook profile test_profile;
User altered.

SQL> connect kigook/kigook
Connected.

SQL> select * from t;

ID
--
aa

# 1ºÐÈÄ¿¡ ´Ù½Ã °°Àº ¹®ÀåÀ» Á¶È¸ÇØ º¸ÀÚ.

SQL> /
select * from t
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

# Á¢¼ÓÈÄ 1ºÐ°£ ¾Æ¹«·± ÀÛ¾÷ÀÌ ¾øÀ¸¸é ÀÚµ¿À¸·Î SessionÀ» Á¾·á½ÃŰ´ÂÁö È®ÀÎ.

(5) ProfileÀÇ ¼öÁ¤

SQL> connect system 
Enter password:
Connected.

SQL> alter profile test_profile limit
  2  idle_time 10
  3  /
Profile altered.

SQL> select resource_name, limit from dba_profiles
  2  where resource_name like '%IDLE_TIME%';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME                        UNLIMITED
IDLE_TIME                        10

# º¯°æµÈ ProfileÀÇ ³»¿ë È®ÀÎ.

Conclusion
----------

ProfileÀ» Àû¿ëÇÏ¿© °èÁ¤¿¡ ´ëÇÑ °ü¸®¿¡ ´ëÇØ¼­ ¾Ë¾Æº¸¾Ò´Ù. ´ÙÀ½ Note¿¡¼­ ´Ù¸¥ ºÎºÐ¿¡ ´ë
ÇØ¼­ ¾Ë¾Æº¸°Ú´Ù.

Reference Documents
-------------------

No. Profile_2

Written by Kigook Choi
----------------------

Ãֱⱹ´ÔÀÌ 2006-07-09 01:19:23¿¡ ÀÛ¼ºÇÑ ´ñ±ÛÀÔ´Ï´Ù. Edit £Ø

ÀÚ¼¼ÇÑ ¼³¸í ¹«ÇÑ °¨»çµå¸³´Ï´ç~

ÇÏ´Ù°¡ Àß ¾ÈµÇ¸é ¶Ç ¹°¾îº¸°Ú½À´Ï´ç~^^

¼ÛÀºÁ¤´ÔÀÌ 2006-07-10 08:52:22¿¡ ÀÛ¼ºÇÑ ´ñ±ÛÀÔ´Ï´Ù. Edit £Ø

Ȥ½Ã ¿À¶óŬ 10g ¶ó¸é ÆÐÄ¡¸¦ Çß´ÂÁö ¸ÕÀú ¾Ë¾Æ ºÁ¾ß ÇÕ´Ï´Ù.

ÀúÈñ °æ¿ì¿¡µµ cpu 50%¸¦ Â÷ÁöÇϰí ÀÖ¾ú´Âµ¥ ÆÐÄ¡¸¦ ÇÏ°í ³ª¼­

10%¾Æ·¡·Î ¶³¾îÁ³½À´Ï´Ù.


À±º´Âù´ÔÀÌ 2006-11-07 10:16:18¿¡ ÀÛ¼ºÇÑ ´ñ±ÛÀÔ´Ï´Ù. Edit £Ø
[Top]
No.
Á¦¸ñ
ÀÛ¼ºÀÚ
ÀÛ¼ºÀÏ
Á¶È¸
27235¿À¶óŬ ¼³Ä¡½Ã ÀÚ¹Ù ¹öÀü¹®Á¦ [2]
À¯¼ºÈÆ
2006-07-09
638
27234c¾ð¾î·Î ¹è¿­À» ¹Þ¾Æ¼­ insert±¸¹® ¿äû.. [1]
±èÀ±Ãµ
2006-07-09
930
27233ÁֹιøÈ£·Î ³ªÀÌ °è»ê [2]
³²¼ö
2006-07-09
1614
27232oracleÀ» »ç¿ëÇϰí ÀÖ´Â sessionÀÌ ³Ê¹« ¸¹¾Æ¿ä~ [3]
¼ÛÀºÁ¤
2006-07-08
2924
27231view°¡ ÀÌ»óÇÕ´Ï´Ù;¤§; [1]
ÀÌÁ¾¹Î
2006-07-07
814
27230B.O.M Å×ÀÌºí ¼³°è ¹æ¹ý½Ã Á¶¾ðÀ» ±¸ÇÒ·Á°í ÇÕ´Ï´Ù.
¿À¶óŬ
2006-07-07
765
27228Á¶È¸½Ã ¹®Á¦°¡ »ý°å½À´Ï´Ù....... [1]
Á¤¸í±Ô
2006-07-07
606
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2008 DSN, All rights reserved.
ÀÛ¾÷½Ã°£: 0.437ÃÊ, À̰÷ ¼­ºñ½º´Â
	PostgreSQL v8.4.2·Î ÀڷḦ °ü¸®ÇÕ´Ï´Ù