SYSAUX

*SYSAUX TBS 가 비정상적으로 늘어날 경우 아래와 같은 문구로 Shrink 가능 

 - 늘어나는 이유. 

   DB에서는 스냅샷 보관주기가 설정되어 있어 과거의 스냅샷은 delete 가 이루어짐.

   하지만 자동으로 shrink를 해주지 않음. 

   결국 공간만 계속 늘어나는 것.

   이 외에 비정상적인 부분

   스냅샷 보관주기 만큼 테이블(WRH$_ 테이블들) 이 가지고 있어야 하는데 그러지 못하는 경우 - 버그임

 

 

 

 

기본적으로 아래의 패키지를 이용하여 스냅샷을 삭제함. 

그러나 너무 오래 걸리고 비효율적이라 생각됨. 

 

수행 패키지 

EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(:MIN sanp, :MAX snap);

  > 삭제만 되고 줄여주진 않음 (rebuild 가 안됨)

 

 

 

해당 패키지 수행시 도는 쿼리를 추출하였더니 아래와 같이 delete 쿼리가 도는 것을 확인함

baseline 이 설정이 되어 있지 않으면 그냥 delete 문임. 

 

delete from WRH$_SYSSTAT tab 
                where dbid = 777160365
                AND tab.snap_id BETWEEN 1 and 21092
                and not exists 
                        (select 1
                        from WRM$_BASELINE b
                        where (tab.dbid = b.dbid)
                        and (tab.snap_id >= b.start_snap_id)
                        and (tab.snap_id <= b.end_snap_id));
                        
commit;

 

그래서 나는 sysaux 에서 큰 사이즈를 갖고 있는 extent 만 추출하여 shrink 하는 것이 효율적이라 생각함

아래는 해당 처리를 위해 적어 놓은 것

 

 

 -- SYSAUX 사용 확인
 select occupant_name , space_usage_kbytes/1024 "MB"
    from v$sysaux_occupants
    order by MB desc;



-- 줄이기 전 대상 확인 & 줄인 후 확인
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 "MB"
   FROM DBA_SEGMENTS 
   WHERE TABLESPACE_NAME ='SYSAUX'
   and bytes/1024/1024 > 50
   ORDER BY MB DESC;
  

  
-- 테이터 파일 마지막 extent id 확인, 마지막 extent의 스냅샷 주기만 남겨놓고 삭제 후 쉬링크 하면 됨
select owner, segment_name, segment_type, max(block_id)  from DBA_EXTENTS
where file_id ='2'
group by owner, segment_name, segment_type
ORDER BY max(block_id) DESC ;


SQL> select d.dbid,w.snap_interval,w.retention from DBA_HIST_WR_CONTROL w, v$database d where w.dbid = d.dbid;

      DBID SNAP_INTERVAL        RETENTION
---------- -------------------- --------------------
1039230559 +00000 00:20:00.0    +00008 00:00:00.0





SQL> SELECT DBID, CREATED FROM V$DATABASE;

      DBID CREATED
---------- ------------
1039230559 26-NOV-15





SQL> SELECT MIN(SAVTIME) FROM WRI$_OPTSTAT_HISTGRM_HISTORY;

MIN(SAVTIME)
---------------------------------------------------------------------------
01-MAY-16 05.42.47.017071 AM +09:00






3. ASH 테이블 SNAP_ID MIN, MAX 값
select min(snap_id),MAX(snap_id) from dba_hist_snapshot;
--MIN MAX
--70283 72486


--4. 보관중인 ASH 
select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;
--MIN     MAX
--1 72486

 

> WRH$_ACTIVE_SESSION_HISTORY 테이블의 snap_id 값이 1부터 존재 

  dba_hist_snapshot 테이블은 70283 

즉, 1 ~ 70282 까지 삭제하면 됨

 

5. PURGE 구동 확인 및 RETENTION
select SNAP_INTERVAL,RETENTION,MOST_RECENT_PURGE_TIME from sys.wrm$_wr_control
SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME
+00000 00:20:00.0 +00030 00:00:00.0 2017/01/09 22:28:17






EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(:MIN sanp, :MAX snap);

아래는 table, lob , 파티션 별 shrink 명령어

--  table
ALTER TABLE *** SHRINK SPACE CASCADE;

alter table PERFSTAT.STATS$MUTEX_SLEEP enable row movement;

-- lob
select owner, table_name, segment_name, column_name from dba_lobs
where segment_name ='SYS_LOB0000069708C00025$$'
            소유자.테이블           
alter table XDB.XDB$RESOURCE modify lob(param_value_file : 컬럼이름)(shrink space cascade);

#파티션일 경우
alter table SPT.TR_SALE_JOURNAL modify PARTITION P201710 lob(JOURNAL_DATA)(shrink space cascade);


---

@ Statspack  
-- PERFSTAT - STATSPACK 을 지원하기 위한 유저 

- AWR이 제공되지 않는 버전(8.1.6 - )의 서버
- Oracle Standard Edition을 사용하는 서버
Standard Edition에서는 tuning pack, diagnostic pack이 제공되지 않음에 따라 AWR을 사용할 수 없다.

- JOB BROKEN 도 필요 
@@ 삭제 방법

 1. statspack delete - sppurge

지정된 스냅샷 범위만큼을 삭제한다.

SQL>conn perfstat/perfstat 
 SQL> @?/rdbms/admin/sppurge.sql



2.  statspack trucate -  sptrunc

SQL>conn perfstat/perfstat 
 SQL> @?/rdbms/admin/sptrunc.sql





** 통계정보 같은 것
WRI$_OPTSTAT_HISTGRM_HISTORY = 통계정보 히스토그램 수집 관련


현재 통계정보 보관 주기
select dbms_stats.get_stats_history_retention from dual;


통계정보 보관주기 변경
exec dbms_stats.alter_stats_history_retention(14);

2017 01 01 이전 통계정보 삭제
exec dbms_stats.purge_stats(to_timestamp_tz('01-01-2017 00:00:00 Asia/Seoul','DD-MM-YYYY HH24:MI:SS TZR'));


alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;
-- ora 10636   >>>  function-based index drop 후 삭제


function-based index 구문 
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;



-- function index 구문 뽑음. 
  CREATE INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST" ON "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY" ("OBJ#", "INTCOL#", SYS_EXTRACT_UTC("SAVTIME"), "COLNAME") 
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" 





  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" 





drop INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST"; 
drop INDEX "SYS"."I_WRI$_OPTSTAT_H_ST";


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;


alter session set workarea_size_policy=manual;
alter session set sort_area_size=104857600;


-- function index 다시 생성
 CREATE INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST" ON "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY" ("OBJ#", "INTCOL#", SYS_EXTRACT_UTC("SAVTIME"), "COLNAME") 
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" 





  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"