오라클 uptime조회 쿼리

※ 오라클 uptime조회 쿼리
col host_name form a10 heading "Host"col instance_name form a8 heading "Instance" newline
col stime form a40 Heading "Database Started At" newline
col uptime form a60 heading "Uptime" newline
set heading off

select 'Hostname      : ' || host_name
      ,'Instance 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


※ Buffer Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;



※ Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;


※ Data Dictionary Cache Hit Ratio
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;


※ Tablespace 사용량
SELECT a.tablespace_name,
             a.total "Total(Mb)",
             a.total - b.free "Used(Mb)",
             nvl(b.free,0) "Free(Mb)",
             round((a.total - nvl(b.free,0))*100/total,0)  "Used(%)"
from    (   select     tablespace_name,
                            round((sum(bytes)/1024/1024),0) as total
               from       dba_data_files
               group by tablespace_name) a,
         (     select     tablespace_name,
                             round((sum(bytes)/1024/1024),0) as free
               from        dba_free_space
               group by  tablespace_name) b
where      a.tablespace_name = b.tablespace_name(+)
order by   a.tablespace_name;


※ 서버 메모리


select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool