티스토리 뷰

Oracle

[Oracle] 오라클 점검 스크립트

정짱 정뚱띵 2018.02.06 10:05
set linesize 350;
PROMPT
PROMPT=======================================================
PROMPT= Oracle Cheak
PROMPT=======================================================
PROMPT
PROMPT
PROMPT=======================================================
PROMPT= 1. Library Cache Miss Ratio
PROMPT=======================================================
PROMPT
select sum(reloads) MISSES, sum(pins), to _char(sum(reloads)/sum(pins)*100,990.99) "Library Cache Miss Ratio (%)" 
from v$librarycache;
PROMPT PROMPT======================================================= PROMPT= 2. Data Dictionary Miss Ratio PROMPT======================================================= PROMPT
select sum(getmisses) MISSES, sum(gets) GETS, to_char(sum(getmisses)/sum(gets)*100,'990.99') "Dict. Cache Miss Ratio (%)"
from v$rowcache;
PROMPT PROMPT======================================================= PROMPT= 3. Buffer Cache Hit Ratio PROMPT======================================================= PROMPT
select sum(decode(NAME, 'consistent gets' VALUE, 0)) "Consistent Gets", sum(decode(NAME, 'db block gets', VALUE, 0)) "DB Block Gets", sum(decode(NAME, 'physical reads', VALUE, 0)) "Physical Reads", round((sum(decode(name, 'consistent gets', value, 0)) + sum(decode(name, 'db block gets', value, 0)) - sum(decode(name, 'physical reads', value, 0))) / (sum(decode(name, 'consistent gets', value, 0)) + sum(decode(name, 'db block gets', value, 0))) * 100,2) "Hit Ratio"
from v$sysstat;
PROMPT PROMPT======================================================= PROMPT= 4. Sort Hit Ratio PROMPT======================================================= PROMPT
select 100*(a.value-b.value)/(a.value) AS "Sort Ratio" from v$sysstat a, v$sysstat a.name = 'sorts (memory)' and b.name = 'sorts (disk)';
PROMPT PROMPT======================================================= PROMPT= 5. Rollback Segments Wait Ratio PROMPT======================================================= PROMPT
select name "Rollback Segment", rssize "Rsize", writes "Writes", xacts "Xacts", status "Status", waits "Waits", gets "Gets", decode(gets, 0, -1, trunc((1-(waits/gets))*100, 5)) "Hit Ratio"
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
order by waits/gets desc;
PROMPT PROMPT======================================================= PROMPT= 6. Table Space Free Space PROMPT======================================================= PROMPT
PROMPT ##Autoextensible##
col file_name for a50;
select tablespace_name, file_name, autoextensible
from DBA_DATA_FILES
order by tablespace_name;
PROMPT ##Table Space##
col USING for a10;
col FREE_SPACE for a10;
select tablespace_name "TABLE NAME", (extend_bytes/1024)/1024 as "EXTEND_BYTES(MB)", (used_bytes/1024)/1024 as "USED_BYTES(MB)", round(((used_bytes)/extend_bytes)*100,2) || '%' "USING", (free_bytes/1024)/1024 as "FREE_BYTES(MB)", round((1-((used_bytes)/extend_bytes))*100,2) || '%' "FREE_SPACE"
from (select t1.tablespace_name, t1.extend_bytes, t1.extend_bytes - t2.free_bytes used_bytes, t2.free_bytes from(select tablespace_name, sum(bytes) as EXTEND_BYTES from dba_data_files
group by tablespace_name) t1, (select tablespace_name, sum(bytes) free_bytes from dba_free_space group by tablespace_name) t2 whwere t2.tablespace_name = t1.tablespace_name)
order by tablespace_name;
PROMPT PROMPT======================================================= PROMPT= 7. UpTime PROMPT======================================================= PROMPT
col host_name for a20;
col instance_name for a20;
col uptime for a70;
select host_name, instance_name, 'Started At :' || to_char(startup_time, 'DD-MON-YYYY HH24:MI:SS') stime, 'Uptime :' || floor(sysdate - startup_time) || 'days(s)' || trunc(24*((sysdate-startup_time) - trunc(sysdate-startup_time))) || 'hour(s)' || mod(trunc(1440*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) || 'minute(s)' || mod(trunc(86400*((sysdate-startup_time) - trunc(sysdate_startup_time))), 60) || 'seconds' uptime
from v$instance;
내부망 :(


댓글
공지사항
«   2018/10   »
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
Total
119,955
Today
45