[oracle] 프로세스,세션 추적 쿼리모음 (모니터링)

오라클 db서버가 문제생겨서 구글링하면서 모은 오라클 모니터링 쿼리입니다.  이걸로 추적?까진 모르겠지만.

유용하게 사용하세요

1. --현재 잡고있는 프로세스 리스트를 확인한다.
2. SELECT   /*+ rule */
3.          s.STATUS "Status", s.serial# "Serial#", s.TYPE "Type",
4.          s.username "DB User", s.osuser "Client User", s.server "Server",
5.          s.machine "Machine", s.module "Module", s.terminal "Terminal",
6.          s.program "Program", p.program "O.S. Program",
7.          s.logon_time "Connect Time", lockwait "Lock Wait",
8.          si.physical_reads "Physical Reads", si.block_gets "Block Gets",
9.          si.consistent_gets "Consistent Gets",
10.          si.block_changes "Block Changes",
11.          si.consistent_changes "Consistent Changes", s.process "Process",
12.          p.spid, p.pid, s.serial#, si.sid, s.sql_address "Address",
13.          s.sql_hash_value "Sql Hash", s.action
14. FROM v$session s, v$process p, sys.v_$sess_io si
15. WHERE s.paddr = p.addr(+)
16.      AND si.sid(+) = s.sid
17.      AND s.username IS NOT NULL
18.      AND NVL (s.osuser, 'x') <> 'SYSTEM'
19.      AND s.TYPE <> 'BACKGROUND'
20. ORDER BY 3;
21.  
22.  
23. --높은 커서별 sid를 확인한다.
24. SELECT sid, COUNT(sid) "cursor"
25. FROM v$open_cursor
26. --WHERE user_name = 'SCOTT'
27. GROUP BY sid
28. ORDER BY "cursor" DESC;
29.  
30.  
31. --가장 많은 커서를 사용한 sql을 추적한다.
32. SELECT sql_text, COUNT(sid) cnt
33. FROM v$OPEN_CURSOR
34. GROUP BY sql_text
35. ORDER BY cnt DESC;
36.  
37.  
38. --현재 inactive 된 세션과 active된 세션을 찾는다.
39. SELECT STATUS,COUNT(*) FROM v$session
40. GROUP BY STATUS;
41.  
42.  
43. --??안된다??
44. SELECT * FROM DBA_PROFILES;
45. --WHERE PROFILE = [NEW_PROFILE_NAME];
46.  
47. SELECT USERNAME,PROFILE FROM DBA_USERS;
48.  
49. --제한시간 확인?? 더 찾아봐야 할듯
50. SELECT * FROM USER_RESOURCE_LIMITS;
51.  
52.  
53. -- 현재 접속된 세션수와 전용서버 방식으로 연결한 세션수, 백그라운드 세션 수, active 세션 수를 알 수 있는 쿼리
54. SELECT COUNT(*) total_cnt, COUNT( DECODE( server, 'DEDICATED', 1, NULL)) dedicated_cnt,
55. COUNT( Decode (TYPE, 'BACKGROUND', 1, NULL)) back_cnt,
56. COUNT( DECODE( STATUS, 'ACTIVE', 1, NULL)) active_cnt
57. FROM V$SESSION;
58.  
59.  
60. -- 최대 동시 접속자 수
61. /**
62. processes : 프로세스 갯수 (백그라운드 +dedicate process)
63. session : 동시접속 세션 수
64. current_utilization : 현재 접속 카운트
65. max_uitilization : 오라클을 시작한 이애로 최대 접속했을 때 피크 수
66. initial_allocation : init.ora 파라메터에서 설정한 수치
67. */
68. SELECT * FROM V$Resource_limit;
69.  
70.  
71.  
72. --cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
73. SELECT c.sql_text
74. ,b.SID
75. , b.SERIAL#
76. ,b.machine
77. ,b.OSUSER
78. ,b.logon_time --이 쿼리를 호출한 시간
79. FROM v$process a, v$session b, v$sqltext c
80. WHERE a.addr = b.paddr
81. AND b.sql_hash_value = c.hash_value
82. --and a.spid = '675958'
83. ORDER BY c.PIECE ;
84.  
85.  
86.  
87. --프로세스 아이디를 이용하여 쿼리문 알아내기
88. SELECT c.sql_text
89. ,b.SID
90. , b.SERIAL#
91. ,b.machine
92. ,b.OSUSER
93. ,b.logon_time --이 쿼리를 호출한 시간
94. FROM v$process a, v$session b, v$sqltext c
95. WHERE a.addr = b.paddr
96. AND b.sql_hash_value = c.hash_value
97. AND a.spid = '1708032' --1912870/
98. ORDER BY c.PIECE;
99.  
100.  
101.  
102. --락이 걸린 세션 자세히 알아보기
103. SELECT a.sid, a.serial#,a.username,a.process,b.object_name,
104. decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
105. decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
106. decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
107. FROM v$session a,dba_objects b, v$lock c
108. WHERE a.sid=c.sid AND b.object_id=c.id1
109. AND c.TYPE='TM';
110.  
111. --2번째
112. SELECT a.sid, a.serial#, a.username, a.process, b.object_name
113. FROM v$session a , dba_objects b, v$lock c
114. WHERE a.sid=c.sid AND b.object_id = c.id1
115. AND c.TYPE = 'TM';
116.  
117.  
118.  
119. --active된녀석들 중 현재 프로세스별로 sql_text 확인하기
120. SELECT
121.   a.sid,       -- SID
122.   a.serial#,   -- 시리얼번호
123.   a.STATUS,    -- 상태정보
124.   a.process,   -- 프로세스정보
125.   a.username,  -- 유저
126.   a.osuser,    -- 접속자의 OS 사용자 정보
127.   b.sql_text,  -- sql
128.   c.program    -- 접속 프로그램
129. FROM
130.   v$session a,
131.   v$sqlarea b,
132.   v$process c
133.   WHERE
134.   a.sql_hash_value=b.hash_value
135.   AND a.sql_address=b.address
136.   AND a.paddr=c.addr
137.   AND a.STATUS='ACTIVE';
138.  
139.  
140.  
141. --현재 db서버에 올려진 오라클 프로세스 별 확인
142.    SELECT  
143.          s.STATUS "Status", s.serial# "Serial#", s.TYPE "Type",
144.          s.username "DB User", s.osuser "Client User", s.server "Server",
145.          s.machine "Machine", s.module "Module", s.client_info "Client Info",
146.          s.terminal "Terminal", s.program "Program", p.program "O.S. Program",
147.          s.logon_time "Connect Time", lockwait "Lock Wait",
148.          si.physical_reads "Physical Reads", si.block_gets "Block Gets",
149.          si.consistent_gets "Consistent Gets",
150.          si.block_changes "Block Changes",
151.          si.consistent_changes "Consistent Changes", s.process "Process",
152.          p.spid, p.pid, si.sid, s.audsid, s.sql_address "Address",
153.          s.sql_hash_value "Sql Hash", s.action
154.     FROM v$session s, v$process p, sys.v_$sess_io si
155.     WHERE s.paddr = p.addr(+)
156.      AND si.sid(+) = s.sid
157.      AND (s.username IS NOT NULL)
158.      AND (NVL (s.osuser, 'x') <> 'SYSTEM')
159.      AND (s.TYPE <> 'BACKGROUND')
160.  ORDER BY 1;
161.  
162.  
163.