10g NEW FEATURE on TABLESPACE ADVISORY
[목적]
Oracle 10g에서는 Tablespace 에 임계치를 설정해서 Tablespace space utilization을 monitor할 수 있다.
[요구조건]
1. compatible >= 10.0
2. Locally Managed Tablespace이어야 한다.
[테이블스페이스 임계값]
임계치는 critical과 Warning을 설정하고, 설정값은 WRI$ALERT_THRESHOLD,DBA_THRESHOLDS에 저장되어 있다.
[Client Interface]
DBMS_SERVER_ALERTS 패키지를 이용해서 임계값을 설정한다.
[Default behavior]
새로운 데이타베이스를 생성시의 Default 값
warning : 85%
critical : 97%
migrated database에서는 임계값이 Null이다.
[임계값 설정]
DBMS_SERVER_ALERTS.SET_THRESHOLD 이용
METRICS_ID -> Find the value of WRI$_ALERT_THRESHOLD.T_METRICS_ID
9000 : DB전체
WARNING_OPERATOR -> Like 4 is for GE(>=)
WARNING_VALUE -> A value of 1 to 100 will be considered as percentage,
if greater than 100 we will assume that the value is expressed in bytes.
CRITICAL_OPERATOR -> Like 4 is for GE(>=)
CRITICAL_VALUE -> A value of 1 to 100 will be considered as percentage
OBSERVATION_PERIOD -> The default observation period is 10minutes.
CONSECUTIVE_OCCURRENCES -> Used to avoid fake alerts due to spikes. Ignored for tablespaces.
INSTANCE_NAME -> Like NULL is for default, for RAC you can specify different instance name.
OBJECT_TYPE -> Like 5 for TABLESPACE Type.
OBJECT_NAME -> Like NULL for database Level or you can specify the TABLESPACE name.
[사용 예제]
SQL> execute dbms_server_alert.set_threshold(9000,4,'72',4,'82',10,1,NULL,5,'UNDO1');
SQL> SELECT OBJECT_NAME,WARNING_VALUE,CRITICAL_VALUE FROM DBA_THRESHOLDS;
OBJECT_NAME WARNING_VALUE CRITICAL_VALUE
----------------------- ------------- --------------
UNDO1 72 82
85 97 -> default database level
execute dbms_server_alert.set_threshold(9000,4,'80',4,'90',10,1,NULL,5,''); -- defult 변경
execute dbms_server_alert.set_threshold(9000,null,null,null,null,null,null,null,5,'UNDO1'); -- UNDO1을 Default로 변경
[ALERT 메세지 검색]
설정한 임계값을 초과하는 경우 alert 메세지는 DBA_OUTSTANDING_ALERTS에서 검색할 수 있다.
SQL> select decode(message_level,5,'WARNING',1,'CRITICAL') alert_level,reason
from dba_outstanding_alerts;
ALERT_LEVEL REASON
----------------------- -----------------------------------------
WARNING Tablespace [DATAMAIN] is [90 percent] full
DBA_OUTSTANDING_ALERTS
DBA_ALERT_HISTORY
V$FILESPACE_USAGE
DBA_TABLESPACE_USAGE_METRICS
DBA_HIST_TBSPC_SPACE_USAGG