sysaux tablespace는 10g에서 새로 추가된 system default tablespace의 하나로 기존에 system tablespace에
저장되던 각종 ORACLE OPTION들의 schema가 저장되며 10g의 new feature인 AWR(auto workload repository) 데이터들이 저장되는 tablespace 입니다.
awr정보는 default로 1시간에 한번씩 data를 gathering하고 그 정보를 7일동안 저장하게 되어 있습니다.
7일이 지나면 가장 오래된 awr정보를 자동으로 삭제하게끔 되어 있습니다.
위의 내용대로 라면 특별한 ORACLE OPTION들을 사용하지 않는 한 awr정보를 저장하는 sysaux tablespace의 크기는
일정기간 늘어나다가(retention기간) 그 일정기간이 지나면 크기에 변화가 거의 없어야 정상일 것이라 생각됩니다만…
sysaux tablespace가 지속적으로 점점 커지는 현상이 발생 했습니다.
처음 설치시 1G로 크기를 설정했는데 시간이 지날 수록 점점 늘어나더니 지금은 4G가 되었습니다.
그래서 이래저래 찾아보니 awr정보는 7일간 저장되지만 주기적으로 실행하는 table analyze 정보는
default로 31일 동안 저장이 된다고 합니다.
31일 동안 data가 저장되고 삭제되는 과정에서 index의 크기가 지속적으로 늘어나는것이
sysaux의 크기가 증가하는 원인이 되었습니다.
해결책
## SYSAUX tablespace를 어떤 owner가 얼마만큼의 크기를 차지하고 있는지 다음의 query로 확인 합니다.
SQL> select occupant_name, space_usage_kbytes/1024 “MB”
from v$sysaux_occupants
order by space_usage_kbytes
OCCUPANT_NAME MB
—————————— ———-
XDB 0
XSAMD 0
ODM 0
STATSPACK 0
ORDIM 0
ORDIM/SQLMM 0
TEXT 0
ULTRASEARCH_DEMO_USER 0
EM_MONITORING_USER 0
EXPRESSION_FILTER 0
ULTRASEARCH 0
EM 0
ORDIM/PLUGINS 0
SDO 0
TSM .25
JOB_SCHEDULER .375
STREAMS .5
AO .75
XSOQHIST .75
LOGSTDBY .875
SM/OTHER 4.9375
LOGMNR 5.875
WM 6.1875
SM/ADVISOR 110.9375
SM/AWR 501.875
SM/OPTSTAT 3198.25 => SM/OPTSTAT 가 가장 많은 공간 차지(통계정보 관련 schema)
## SYSAUX tablespace에 어떤 segment가 가장 많은 공간을 차지하는지 조사
select owner, segment_name, segment_type, bytes/1024/1024
from dba_segments
where tablespace_name = ‘SYSAUX’
order by bytes
….
….
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 152
SYS SYS_LOB0000788324C00004$$ LOBSEGMENT 200
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 219
SYS I_WRI$_OPTSTAT_H_ST INDEX 653
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 840
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 1219
조회 결과 optstat관련된 table과 index가 많은 공간을 차지 하고 있음.
## 통계정보를 보관하는 주기를 확인
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
31
## 보관주기를 줄인다. 7일로 줄일경우
SQL> exec dbms_stats.alter_stats_history_retention(7);
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
7
## SYSAUX에 저장된 기존의 old 통계정보를 purge한다.
(예제는 2006년 10월 10일 이전의 data를 삭제함)
SQL> exec DBMS_STATS.PURGE_STATS(to_timestamp_tz(’10-10-2006 00:00:00 Asia/Seoul’,’DD-MM-YYYY HH24:MI:SS TZR’));
통계정보 data를 purge하더라도 기존에 할당된 segment의 크기는 줄어들지 않기 때문에 SYSAUX의 free space를 확보하려면
table과 index를 reorg해야 된다.
여기서 10g의 새로운 기능인 segment shrink를 사용하면 된다.
alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;
–> WRI$_OPTSTAT_HISTGRM_HISTORY table에 function-based index가 걸려 있어
shrink space 명령이 정상 실행되지 않습니다.
일단 WRI$_OPTSTAT_HISTGRM_HISTORY table에 걸려있는 function-based index인
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST와 I_WRI$_OPTSTAT_H_ST를 drop하고 table shrink space명령을 실행한 후
function-based index인 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST와 I_WRI$_OPTSTAT_H_ST를 재생성 해 줍니다.
1. drop indexes
drop전에 index를 생성하는 구문을 미리 뽑아 놓은다.
인덱스 생성 구문은 dbms_metadata를 사용하여 뽑아냄.
set long 10000
select DBMS_METADATA.GET_DDL(‘INDEX’,’I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST’,’SYS’) from dual;
select DBMS_METADATA.GET_DDL(‘INDEX’,’I_WRI$_OPTSTAT_H_ST’,’SYS’) from dual;
DROP INDEX “SYS”.”I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST” ;
DROP INDEX “SYS”.”I_WRI$_OPTSTAT_H_ST” ;
2.shrink table
alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY disable row movement;
## 실제로 크기가 줄었는지 확인
SQL> select occupant_name, space_usage_kbytes/1024 “MB”
from v$sysaux_occupants
order by space_usage_kbytes
OCCUPANT_NAME MB
—————————————————————- ———-
XDB 0
XSAMD 0
ODM 0
STATSPACK 0
ORDIM 0
ORDIM/SQLMM 0
TEXT 0
ULTRASEARCH_DEMO_USER 0
EM_MONITORING_USER 0
EXPRESSION_FILTER 0
ULTRASEARCH 0
EM 0
ORDIM/PLUGINS 0
SDO 0
TSM .25
JOB_SCHEDULER .375
STREAMS .5
AO .75
XSOQHIST .75
LOGSTDBY .875
SM/OTHER 4.9375
LOGMNR 5.875
WM 6.1875
SM/ADVISOR 110.9375
SM/AWR 511.4375
SM/OPTSTAT 667.125 –> 3G에서 670M로 줄어듬.
3. recreate indexes
alter session set workarea_size_policy=manual;
alter session set sort_area_size=104857600
;
CREATE INDEX “SYS”.”I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST”
ON “SYS”.”WRI$_OPTSTAT_HISTGRM_HISTORY” (“OBJ#”, “INTCOL#”, SYS_EXTRACT_UTC(“SAVTIME”))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSAUX” nologging;
CREATE INDEX “SYS”.”I_WRI$_OPTSTAT_H_ST”
ON “SYS”.”WRI$_OPTSTAT_HISTGRM_HISTORY” (SYS_EXTRACT_UTC(“SAVTIME”))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSAUX” nologging;
SQL> select occupant_name, space_usage_kbytes/1024 “MB”
from v$sysaux_occupants
order by space_usage_kbytes
OCCUPANT_NAME MB
—————————————————————- ———-
XDB 0
XSAMD 0
ODM 0
STATSPACK 0
ORDIM 0
ORDIM/SQLMM 0
TEXT 0
ULTRASEARCH_DEMO_USER 0
EM_MONITORING_USER 0
EXPRESSION_FILTER 0
ULTRASEARCH 0
EM 0
ORDIM/PLUGINS 0
SDO 0
TSM .25
JOB_SCHEDULER .375
STREAMS .5
AO .75
XSOQHIST .75
LOGSTDBY .875
SM/OTHER 4.9375
LOGMNR 5.875
WM 6.1875
SM/ADVISOR 110.9375
SM/AWR 511.4375
SM/OPTSTAT 947.125 index 재생성 후 670M에서 950M로 변경
## segment 크기 조회
select owner, segment_name, segment_type, bytes/1024/1024
from dba_segments
where tablespace_name = ‘SYSAUX’
order by bytes
SYS I_WRI$_OPTSTAT_H_ST INDEX 120
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 155
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 160
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 177.875
SYS SYS_LOB0000788324C00004$$ LOBSEGMENT 200
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 219
## SYSAUX TABLESPACE의 Free space 조회
select tablespace_name, sum(bytes)/1024/1024
from dba_free_space
where tablespace_name = ‘SYSAUX’
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
—————————— ——————–
SYSAUX 2892.9375