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

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;