Techdata VINA 지식 공유

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