ADMIN 점검 사항

--인스턴스 상태
select instance_name, status from v$instance;

--초기 파라메터 확인
show parameter spfile
값이 없으면 pfile로 DB 기동한것이고 있으면 spfile 경로가 보인다.

--리스너 상태
lsnrctl status

--오라클 S/W, 아카이브 공간 확인
df -k(dbf)

--리소스 체크
select * from v$resource_limit;

--오라클 서버 메모리
select sum(bytes)/1024/1024 shared_pool_size from v$sgastat
where pool='shared pool'

--백업확인
select * from v$backup; --(HotBackup)

--Recover file 조회
select * from v$recover_file;

--RedoLog 발생량 확인
select thread#, to_char(first_time, 'yyyy.mm.dd hh24') "Time", count(*) "Count"
from v$loghist
where first_time > sysdate -10
group by thread#, to_char(first_time, 'yyyy.mm.dd hh24');

--테이블스페이스 Flagmentation 조회
SELECT * FROM (
      SELECT tablespace_name,count(*) AS fragments,
             sum(bytes) AS total,
             max(bytes) AS largest
       FROM dba_free_space
       group by tablespace_name      )
WHERE fragments > 200;

--사용량이 80%이상인 테이블스페이스
 SELECT a.tablespace_name, a.bytes/1024/1024 "AMOUNT(MB)", b.bytes/1024/1024 "USED(MB)", c.bytes/1024/1024 "FREE(MB)",
(b.bytes*100)/a.bytes "% USED", (c.bytes*100)/a.bytes "% FREE"
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name=b.tablespace_name
AND a.tablespace_name=c.tablespace_name
AND (c.bytes*100)/a.bytes<20;

--테이블스페이스 사용량확인
SELECT U.TABLESPACE_NAME  ,
       SUM(U.BYTES)/1024/1024 TOT,
       round(SUM(F.BYTES)/1024/1024,2)  FREE,
       round(SUM(U.BYTES-F.BYTES)/1024/1024,2) USED,
       round(SUM(F.BYTES/1024/1024)/SUM(U.BYTES/1024/1024)*100,2) FREE_RATIO,u.autoextensible auto
FROM DBA_DATA_FILES U,
    (SELECT FILE_ID,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) F
WHERE U.FILE_ID = F.FILE_ID
GROUP BY U.TABLESPACE_NAME,u.autoextensible;

--DB Buffer Cache Hit Ratio
select to_char(sysdate,'yyyy.mm.dd HH24:MI:SS') "Time",
a.value+b.value "Logical Reads",
c.value "Physical Reads",
round((1 - (c.value/(a.value+b.value)))*100, 3) "Hit Ratio"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'db block gets'
and b.name = 'consistent gets'
and c.name = 'physical reads';

--Library Cache Hit Ratio
select 100-(sum(reloads)/sum(pins))*100 
from v$librarycache;

--Dictionary Cache Hit Ratio
select 100-(sum(getmisses)/sum(gets))*100
from v$rowcache;

--Memory Sort Ratio
select a.value "Sort(memory)",
b.value "Sort(disk)",
round(a.value/(a.value+b.value) * 100 ,2) "Memory Sort Ratio"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)';

--PGA Cache Hit Ratio
select round(((a.value * 100 ) / (a.value + b.value)),2)||'%' "PGACacheHitRatio%"
from v$pgastat a, v$pgastat b
where a.name = 'bytes processed'
and b.name = 'extra bytes read/written';

--Rollback Segment's wait ratio
select name, waits/gets*100 mis_ratio
from v$rollstat ,v$rollname;

--1Month 주기로 데이터 증가량
select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');

select rn.name, (rs.waits/rs.gets) rbs_header_wait_ratio from v$rollstat rs, v$rollname rn
where rs.usn = rn.usn order by 1;  
-->  조회된 rbs_header_wait_ratio 가 0.01 보다 크면, rollback segment contention 방지를 위해 개수를 추가합니다.

select class, count
from v$WAITSTAT
where class in ('undo header','undo block', 'system undo header', 'system undo block');
-->만약 1% 이상을 초과하는 경우 rollback segment 크기를 현재보다 크게 하거나 새롭게 생성/추가하시기 바랍니다.

 select initial_extent + next_extent * (extents -1) "RBS SIZE(byte)", extents  from dba_segments
where segment_type ='ROLLBACK';
--> query의 평균 값(byte)을 rollback segment들의 optimal size로 사용할 수 있습니다.