pat98's always & forever

awr정보는 7일간 저장되지만 주기적으로 실행하는 table analyze 정보는 default로 31일 동안 저장이 됩니다.

<해결책>

1. select dbms_stats.get_stats_history_retention from dual;
(기본 31일입니다.)

2. exec dbms_stats.alter_stats_history_retention(7);
=> 일주일 주기로 바꿈
 
3.exec dbms_stats.purge_stats(to_timestamp_tz('10-10-2008 00:00:00 Asia/Seoul','DD-MM-YYYY HH24:MI:SS TZR'));
=> AWR(auto workload repository) 데이터 저장 값입니다. 디폴드 주기 31일 이지만
2008년 10월10일 이전 데이터 삭제 => 날짜 조정 해주시면 됩니다.
oracle 사용 내부 통계 정보로 자동 삭제 주기를 줄인 다음 값을 삭제 하는겁니다.

4.alter table wri$_optstat_histgrm_history enable row movement;

5.alter table wri$_optstat_histgrm_history shrink space;
5번 실행 해서 에러 없을 경우 진행
SQL> alter table wri$_optstat_histgrm_history shrink space;
alter table wri$_optstat_histgrm_history shrink space
*
1행에 오류:
ORA-10631: SHRINK clause should not be specified for this object
=> 에러 날 경우 6번 진행

6. 5번에서 에러 날 경우[index 생성 쿼리 추출 구문 실행]
set long 1000
select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST','SYS') from dual;

=>결과
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;

select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_H_ST','SYS') from dual;

=> 결과
 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;

7. INDEX 삭제 아래 적용 후 재 생성
drop INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST";

drop INDEX "SYS"."I_WRI$_OPTSTAT_H_ST";
=============================================

8. alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;

9. alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;

10. alter table WRI$_OPTSTAT_HISTGRM_HISTORY disable row movement;

11. alter session set workarea_size_policy=manual;

12. alter session set sort_area_size=104857600;

13. 위에서 삭제한 index 생성 쿼리 실행

13-1.
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;

13-2.
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;

14.결과 조회

14-1
select occupant_name,space_usage_kbytes/1024 "MB"
from v$sysaux_occupants
order by space_usage_kbytes
/
적용 전
OCCUPANT_NAME                                         MB
--------------------------------------------- ----------

SM/OPTSTAT                                        4.8125
SM/AWR
적용 후

SM/OPTSTAT                                           3.5
SM/AWR

15. SYSAUX Tablespace 사용량 체크

select tablespace_name,sum(bytes/1024/1024) "Free(M)"
from dba_free_space
where tablespace_name = 'SYSAUX'
group by tablespace_name;