自動UNDOチューニングについて - ablog のつづき。
UNDO表領域使用率の警告アラート値を実際に変更してみる。
- dba_thresholds の定義を確認する。
desc dba_thresholds Name Null? Type ----------------------------------------- -------- ---------------------------- METRICS_NAME VARCHAR2(64) WARNING_OPERATOR VARCHAR2(12) WARNING_VALUE VARCHAR2(256) CRITICAL_OPERATOR VARCHAR2(12) CRITICAL_VALUE VARCHAR2(256) OBSERVATION_PERIOD NUMBER CONSECUTIVE_OCCURRENCES NUMBER INSTANCE_NAME VARCHAR2(16) OBJECT_TYPE VARCHAR2(64) OBJECT_NAME VARCHAR2(513) STATUS VARCHAR2(7)
- dba_thresholds を見てみる。
set linesize 200 col METRICS_NAME for a22 col WARNING_OPERATOR for a2 col WARNING_VALUE for a2 col CRITICAL_OPERATOR for a2 col CRITICAL_VALUE for a2 col OBSERVATION_PERIOD for 99 col CONSECUTIVE_OCCURRENCES for 99 col INSTANCE_NAME for a13 col OBJECT_TYPE for a10 col OBJECT_NAME for a1 col STATUS for a6 select * from dba_thresholds; METRICS_NAME WA WA CR CR OBSERVATION_PERIOD CONSECUTIVE_OCCURRENCES INSTANCE_NAME OBJECT_TYP O STATUS ---------------------- -- -- -- -- ------------------ ----------------------- ------------- ---------- - ------ Tablespace Space Usage GE 85 GE 97 1 1 database_wide TABLESPACE VALID
- UNDO表領域名を確認する。
select value from v$parameter where name='undo_tablespace'; VALUE -------------------------------------------------------------------------------- UNDOTBS1
- UNDO表領域の警告アラート閾値を変更してみる。
BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD( metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT, warning_value => '50', critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT, critical_value => '85', observation_period => 1, consecutive_occurrences => 1, instance_name => NULL, object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, object_name => 'UNDOTBS1'); END; /
- 確認してみる。
set linesize 200 col METRICS_NAME for a22 col WARNING_OPERATOR for a2 col WARNING_VALUE for a2 col CRITICAL_OPERATOR for a2 col CRITICAL_VALUE for a2 col OBSERVATION_PERIOD for 99 col CONSECUTIVE_OCCURRENCES for 99 col INSTANCE_NAME for a13 col OBJECT_TYPE for a10 col OBJECT_NAME for a8 col STATUS for a6 select * from dba_thresholds; METRICS_NAME WA WA CR CR OBSERVATION_PERIOD CONSECUTIVE_OCCURRENCES INSTANCE_NAME OBJECT_TYP OBJECT_N STATUS ---------------------- -- -- -- -- ------------------ ----------------------- ------------- ---------- -------- ------ Tablespace Space Usage GE 85 GE 97 1 1 database_wide TABLESPACE VALID Tablespace Space Usage GT 50 GT 85 1 1 database_wide TABLESPACE UNDOTBS1 VALID
とりあえずできた。これでいいんだか。未検証。気が向いたらやってみる予定。
