1. 테이블스페이스 사용량 확인

   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 ;

-- Index *****
-- 10분이내에 수행된 쿼리중 세션에 남아있는 쿼리
-- Buffer Cache Hit Ratio
-- Library Cache Hit Ratio
-- Data Dictionary Cache Hit Ratio
-- 테이블 스페이스 사용량
-- 오라클서버의 메모리
-- cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
-- 프로세스 아이디를 이용하여 쿼리문 알아내기
-- 세션 죽이기(SID,SERAIL#)
-- 오라클 세션과 관련된 테이블*/
-- 현재 커서 수 확인
-- V$LOCK 을 사용한 잠금 경합 모니터링
-- 락이 걸린 세션 자세히 알아보기
-- 락이 걸린 세션 간단히 알아보기
-- 락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
-- alter session으로 죽지않는 프로세스 죽이기 
 
========================================================================
 
-- 10분이내에 수행된 쿼리중 세션에 남아있는 쿼리
SELECT sql_text
  FROM v$sqltext a, v$session b
 WHERE a.hash_value = b.sql_hash_value
   AND logon_time <= to_date(to_char(sysdate,'yyyymmddhh24mi')-10,'yyyymmddhh24mi');
 
 
-- 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;
 
 
 
 
 
-- 테이블 스페이스 사용량
 
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 * FROM v$sgastat
 
 
 
SELECT pool, SUM(bytes) "SIZE"
 
FROM v$sgastat
 
WHERE pool = 'shared pool'
 
GROUP BY pool
 
 
 
 
 
-- cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
 
SELECT c.sql_text
 
,b.SID
 
, b.SERIAL#
 
,b.machine
 
,b.OSUSER
 
,b.logon_time --이 쿼리를 호출한 시간
 
FROM v$process a, v$session b, v$sqltext c
 
WHERE a.addr = b.paddr
 
AND b.sql_hash_value = c.hash_value
 
--and a.spid = '675958'
 
ORDER BY c.PIECE
 
 
 
 
 
-- cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기2
 
SELECT c.sql_text
 
FROM v$process a, v$session b, v$sqltext c
 
WHERE a.addr = b.paddr
 
AND b.sql_hash_value = c.hash_value
 
AND a.spid = '171'
 
ORDER BY c.PIECE
 
 
 
 
 
-- 프로세스 아이디를 이용하여 쿼리문 알아내기
 
SELECT c.sql_text
 
,b.SID
 
, b.SERIAL#
 
,b.machine
 
,b.OSUSER
 
,b.logon_time --이 쿼리를 호출한 시간
 
FROM v$process a, v$session b, v$sqltext c
 
WHERE a.addr = b.paddr
 
AND b.sql_hash_value = c.hash_value
 
AND a.spid = '1708032' --1912870/
 
ORDER BY c.PIECE
 
 
 
 
 
-- 세션 죽이기(SID,SERAIL#)
 
--ALTER SYSTEM KILL SESSION '8,4093'
 
 
 
-- 오라클 세션과 관련된 테이블*/
 
--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'
 
 
 
 
 
-- 현재 커서 수 확인
 
SELECT sid, COUNT(sid) cursor
 
FROM V$OPEN_CURSOR
 
WHERE user_name = 'ilips'
 
GROUP BY sid
 
ORDER BY cursor DESC
 
 
 
SELECT sql_text, COUNT(sid) cnt
 
FROM v$OPEN_CURSOR
 
GROUP BY sql_text
 
ORDER BY cnt DESC
 
 
 
SELECT * FROM v$session_wait
 
 
 
SELECT sid, serial#, username, taddr, used_ublk, used_urec
 
 FROM v$transaction t, v$session s
 
 WHERE t.addr = s.taddr;
 
 
 
SELECT *  FROM sys.v_$open_cursor
 
 
 
 
 
-- V$LOCK 을 사용한 잠금 경합 모니터링
 
SELECT s.username, s.sid, s.serial#, s.logon_time,
 
    DECODE(l.type, 'TM', 'TABLE LOCK',
 
          'TX', 'ROW LOCK',
 
       NULL) "LOCK LEVEL",
 
    o.owner, o.object_name, o.object_type
 
FROM v$session s, v$lock l, dba_objects o
 
WHERE s.sid = l.sid
 
AND o.object_id = l.id1
 
AND s.username IS NOT NULL
 
 
 
 
 
-- 락이 걸린 세션 자세히 알아보기
 
SELECT a.sid, a.serial#,a.username,a.process,b.object_name,
 
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
 
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
 
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
 
FROM v$session a,dba_objects b, v$lock c
 
WHERE a.sid=c.sid AND b.object_id=c.id1
 
AND c.type='TM'
 
 
 
 
-- 락이 걸린 세션 간단히 알아보기
 
SELECT a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
 
       a.logon_time, a.process, a.osuser, a.terminal
 
FROM v$session a, v$lock b, dba_objects c
 
WHERE a.sid = b.sid
 
  AND b.id1 = c.object_id
 
  AND b.type = 'TM';
 
SELECT a.sid, a.serial#, a.username, a.process, b.object_name
 
FROM v$session a , dba_objects b, v$lock c
 
WHERE a.sid=c.sid AND b.object_id = c.id1
 
AND c.type = 'TM'
 
 
 
 
-- 락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
 
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
 
--kill -9 프로세스아이디
 
SELECT substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
 
s.sid "SESSION ID", s.serial#, osuser "OS USER",
 
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
 
FROM v$process p, v$session s, v$access a
 
WHERE a.sid=s.sid AND
 
p.addr=s.paddr AND
 
s.username != 'SYS'
 
--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
 
ALTER SYSTEM KILL SESSION '11,39061'