MY COMPASS

SELECT * FROM DBA_HIST_WR_CONTROL; SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;

-- AWR SQL Stat History 확인 with w_sqlstat as (     select /*+ inline use_nl(a,b,c) leading(a) index(a (sql_id))*/            a.*,            to_char(b.begin_interval_time,'mm/dd hh24:mi') snap_time     from   dba_hist_sqlstat a          , dba_hist_snapshot b     where  b.dbid             = a.dbid     and    b.instance_number  = a.instance_number     and    b.snap_id          = a.snap_id     and    a.sql_id = '7s4bffkwdq1r4' --    and    a.module like 'BTmapsc090%' --    and    b.instance_number  = 1 --    and    b.dbid = 3107085369 --    and    a.snap_id >= 15817     --and    b.snap_id = 55925     --and   ...

SYSAUX 사용현황 select occupant_name, space_usage_kbytes/1024 "MB" from v$sysaux_occupants order by space_usage_kbytes; SYSAUX SEGMENT 정보 select owner, segment_name, segment_type, bytes/1024/1024 "MB" from dba_segments where tablespace_name = 'SYSAUX' order by bytes; 통계정보 보관주기 확인 select dbms_stats.get_stats_history_retention from dual; 보관주기 변경 exec dbms_stats.alter_stats_history_retention(8); 오래된 통계정보 삭제 (15/10/10 이전 삭제 예시 ) exec dbms_stats.purge_stats(to_timestamp_tz('10-10-2015 00:00:00 Asia/Seoul','DD-MM-YYYY HH24:MI:SS TZR')); AWR 현황 SELECT   snap_id, begin_interval_time, end_interval_time FROM   SYS.WRM$_SNAPSHOT WHERE   snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT   snap_id, begin_interval_time, end_interval_time FROM   SYS.WRM$_SNAPSHOT WHERE   snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT) / AWR 삭제 BEGIN    ...