"ORACLE 모니터링"의 두 판 사이의 차이

7. 모니터링

1. 38 연결되어 있는 OS 사용자 및 프로그램 조회

SELECT SID
     , SERIAL#
     , OSUSER
     , SUBSTRB(USERNAME, 1, 10) AS USER_NAME
     , SUBSTRB(PROGRAM, 1, 30) AS PROGRAM_NAME
     , STATUS
     , TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME

  FROM V$SESSION WHERE TYPE!= ‘BACKGROUND’ AND STATUS = ‘ACTIVE’;

1. 39 1시간 이상 유휴 상태인 세션

SELECT SID

     , SERIAL#
     , USERNAME
     , TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET

  FROM V$SESSION WHERE LAST_CALL_ET > 3600 AND USERNAME IS NOT NULL;

1. 40 Active Session 중 Idle Time이 긴 작업

SELECT VS.SID || ',' || VS.SERIAL# " SID"

     , VP.SPID
     , VS.MACHINE
     , VS.PROGRAM
     , VS.MODULE
     , VS.STATUS
     , TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME
     , ROUND(VS.LAST_CALL_ET / 60) "IDLE"

  FROM V$SESSION VS

     , V$PROCESS VP

 WHERE VS.STATUS = 'ACTIVE' AND VS.SID NOT IN (1, 2, 3, 4, 5, 6, 7) AND VS.PADDR = VP.ADDR ORDER BY 8;

1. 41 DBUser 별로 Session 정보를 조회

SELECT S.USERNAME

     , S.SID
     , S.SERIAL#
     , P.SPID
     , S.OSUSER
     , S.MACHINE
     , S.PROGRAM
     , TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME"
     , ROUND(S.LAST_CALL_ET / 60) "IDLE"

  FROM V$SESSION S

     , V$PROCESS P

 WHERE S.PADDR = P.ADDR AND S.USERNAME LIKE UPPER('&DBUSER%') ORDER BY 9;

1. 42 Session별 사용 명령어

SELECT SESS.SID

     , SESS.SERIAL#
     , SUBSTR(SESS.USERNAME, 1, 10) "USER NAME"
     , SUBSTR(OSUSER, 1, 11) "OS USER"
     , SUBSTR(SESS.MACHINE, 1, 15) "MACHINE NAME"
     , STATUS
     , UPPER(
            DECODE(NVL(COMMAND, 0)
              , 0, '---'
              , 1, 'CREATE TABLE'
              , 2, 'INSERT -'
              , 3, 'SELECT -'
              , 4, 'CREATE CLUST'
              , 5, 'ALTER CLUST'
              , 6, 'UPDATE -'
              , 7, 'DELETE -'
              , 8, 'DROP -'
              , 9, 'CREATE INDEX'
              , 10, 'DROP INDEX'
              , 11, 'ALTER INDEX'
              , 12, 'DROP TABLE'
              , 13, 'CREATE SEQ'
              , 14, 'ALTER SEQ'
              , 15, 'ALTER TABLE'
              , 16, 'DROP SEQ'
              , 17, 'GRANT'
              , 18, 'REVOKE'
              , 19, 'CREATE SYN'
              , 20, 'DROP SYN'
              , 21, 'CREATE VIEW'
              , 22, 'DROP VIEW'
              , 23, 'VALIDATE IX'
              , 24, 'CREATE PROC'
              , 25, 'ALTER PROC'
              , 26, 'LOCK TABLE'
              , 27, 'NO OPERATION'
              , 28, 'RENAME'
              , 29, 'COMMENT'
              , 30, 'AUDIT'
              , 31, 'NOAUDIT'
              , 32, 'CREATE DBLINK'
              , 33, 'DROP DB LINK'
              , 34, 'CREATE DATABASE'
              , 35, 'ALTER DATABASE'
              , 36, 'CREATE RBS'
              , 37, 'ALTER RBS'
              , 38, 'DROP RBS'
              , 39, 'CREATE TABLESPACE'
              , 40, 'ALTER TABLESPACE'
              , 41, 'DROP TABLESPACE'
              , 42, 'ALTER SESSION'
              , 43, 'ALTER USER'
              , 44, 'COMMIT'
              , 45, 'ROLLBACK'
              , 47, 'PL/SQL EXEC'
              , 48, 'SET TRANSACTION'
              , 49, 'SWITCH LOG'
              , 50, 'EXPLAIN'
              , 51, 'CREATE USER'
              , 52, 'CREATE ROLE'
              , 53, 'DROP USER'
              , 54, 'DROP ROLE'
              , 55, 'SET ROLE'
              , 56, 'CREATE SCHEMA'
              , 58, 'ALTER TRACING'
              , 59, 'CREATE TRIGGER'
              , 61, 'DROP TRIGGER'
              , 62, 'ANALYZE TABLE'
              , 63, 'ANALYZE INDEX'
              , 69, 'DROP PROCEDURE'
              , 71, 'CREATE SNAP LOG'
              , 72, 'ALTER SNAP LOG'
              , 73, 'DROP SNAP LOG'
              , 74, 'CREATE SNAPSHOT'
              , 75, 'ALTER SNAPSHOT'
              , 76, 'DROP SNAPSHOT'
              , 85, 'TRUNCATE TABLE'
              , 88, 'ALTER VIEW'
              , 91, 'CREATE FUNCTION'
              , 92, 'ALTER FUNCTION'
              , 93, 'DROP FUNCTION'
              , 94, 'CREATE PACKAGE'
              , 95, 'ALTER PACKAGE'
              , 96, 'DROP PACKAGE'
              , 46, 'SAVEPOINT'
                  )
          )
            COMMAND
     , SESS.PROCESS "C.PROC"
     , PROC.SPID "S.PROC"
     , TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI')

  FROM V$SESSION SESS

     , V$SESSTAT STAT
     , V$STATNAME NAME
     , V$PROCESS PROC

 WHERE SESS.SID = STAT.SID AND STAT.STATISTIC# = NAME.STATISTIC# AND SESS.USERNAME IS NOT NULL AND NAME.NAME = 'RECURSIVE CALLS' AND SESS.PADDR = PROC.ADDR ORDER BY 3, 1, 2;

1. 43 사용자 session 중에서 2시간 이상 idle 상태가 지속되는 session을 kill

SET PAGESIZE 0 SPOOL KILLIDLE3.SQL

SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';' FROM V$SESSION A

     , V$PROCESS B

 WHERE A.PADDR IN (SELECT S.PADDR

                      FROM      V$SESSION S
                      WHERE     STATUS = 'INACTIVE'
                      GROUP BY  S.PADDR
                      HAVING    MIN(ROUND(LAST_CALL_ET / 60)) > 120)

AND A.PADDR = B.ADDR AND A.STATUS = 'INACTIVE';

SPOOL OFF

1. 44 Oracle Process의 정보

SELECT S.STATUS "STATUS"

     , S.SERIAL# "SERIAL#"
     , S.TYPE "TYPE"
     , S.USERNAME "DB USER"
     , S.OSUSER "CLIENT USER"
     , S.SERVER "SERVER"
     , S.MACHINE "MACHINE"
     , S.MODULE "MODULE"
     , S.TERMINAL "TERMINAL"
     , S.PROGRAM "PROGRAM"
     , P.PROGRAM "O.S. PROGRAM"
     , S.LOGON_TIME "CONNECT TIME"
     , LOCKWAIT "LOCK WAIT"
     , SI.PHYSICAL_READS "PHYSICAL READS"
     , SI.BLOCK_GETS "BLOCK GETS"
     , SI.CONSISTENT_GETS "CONSISTENT GETS"
     , SI.BLOCK_CHANGES "BLOCK CHANGES"
     , SI.CONSISTENT_CHANGES "CONSISTENT CHANGES"
     , S.PROCESS "PROCESS"
     , P.SPID
     , P.PID
     , S.SERIAL#
     , SI.SID
     , S.SQL_ADDRESS "ADDRESS"
     , S.SQL_HASH_VALUE "SQL HASH"
     , S.ACTION

  FROM V$SESSION S

     , V$PROCESS P
     , SYS.V_$SESS_IO SI

 WHERE S.PADDR = P.ADDR(+) AND SI.SID(+) = S.SID AND S.USERNAME IS NOT NULL AND NVL(S.OSUSER, 'X') <> 'SYSTEM' AND S.TYPE <> 'BACKGROUND' ORDER BY 3;

1. 45 오브젝트에 접속되어 있는 프로그램 조회

SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT FROM V$SESSION A

     , V$ACCESS B

 WHERE A.SID = B.SID AND B.OWNER NOT IN ('SYS') AND A.TYPE!= 'BACKGROUND' AND B.OBJECT LIKE UPPER('&OBJECT_NAME') || '%' GROUP BY B.OBJECT, SUBSTR(A.PROGRAM, 1, 15);

1. 46 롤백 세그먼트 경합 조회

SELECT NAME T0

     , GETS T1
     , WAITS T2
     , TO_CHAR(TRUNC(WAITS / GETS * 100, 2), 099.99) || '%' T3
     , TO_CHAR(ROUND(RSSIZE / 1024)) T4
     , SHRINKS T5
     , EXTENDS T6

  FROM V$ROLLSTAT

     , V$ROLLNAME

 WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN;

</source>

1. 50 System 테이블스페이스에 비시스템 세그먼트 조회

SELECT OWNER

     , SEGMENT_NAME
     , SEGMENT_TYPE
     , TABLESPACE_NAME

  FROM DBA_SEGMENTS WHERE OWNER NOT IN ('SYS', 'SYSTEM') AND TABLESPACE_NAME = 'SYSTEM';

</source>

1. 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;

1. Library Cache Hit Ratio

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

1. Data Dictionary Cache Hit Ratio

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

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;

1. 오라클서버의 메모리

select * from v$sgastat

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

1. 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

1. cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

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

1. 프로세스 아이디를 이용하여 쿼리문 알아내기

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

1. 세션 죽이기(SID,SERAIL#)

ALTER SYSTEM KILL SESSION '8,4093'

## 오라클 세션과 관련된 테이블*/
<source lang="sql">
--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

1. 현재 커서 수 확인

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

1. 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

1. 락이 걸린 세션 자세히 알아보기

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'

1. 락이 걸린 세션 간단히 알아보기

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'

1. 위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다

ALTER SYSTEM KILL SESSION '11,39061'

alter session으로 죽지않는 프로세스 죽이기

1.oracle이 설치된 서버에 텔넷으로 root로 접속한다 2.su -오라클계정 3.sqlplus '/as sysdba 4.connect system/sys 5.ALTER SYSTEM KILL SESSION '137,1723'

"ORACLE 모니터링"의 두 판 사이의 차이

DB CAFE

2019년 3월 29일 (금) 19:10 판