불량SQL을 찾을때와 락문제를 해결할때 도움이 될 것입니다.
유용하게 사용되길 바라며...
-
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'
SELECT * FROM dba_External_Locations
SELECT * FROM nls_database_parameters
--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;
--2. Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
--3. 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를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
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'
alter session으로 죽지않는 프로세스 죽이기
1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys
5.ALTER SYSTEM KILL SESSION '137,1723'
-- 특정 테이블에 사용된 쿼리
SELECT * FROM V$SQLAREA
WHERE parsing_schema_name = 'ING'
AND lower(sql_text) LIKE '%order_result_report_tbl%'
ORDER BY last_active_time DESC
-- 현재 세션에서 사용되는 쿼리
SELECT S.SID
, S.SERIAL#
, S.STATUS
, S.PROCESS
, S.OSUSER
, A.SQL_TEXT
, P.PROGRAM
FROM V$SESSION S
, V$SQLAREA A
, V$PROCESS P
WHERE S.SQL_HASH_VALUE = A.HASH_VALUE
AND S.SQL_ADDRESS = A.ADDRESS
AND S.PADDR = P.ADDR
AND S.SCHEMANAME = 'ING'
AND S.STATUS = 'ACTIVE'
--
select
sql_text,
sharable_mem + persistent_mem + runtime_mem memory,
sorts,
executions,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
round(rows_processed/greatest(executions,1)) row_ratio,
round(disk_reads/greatest(executions,1)) disk_ratio,
round(buffer_gets/greatest(executions,1)) buffer_ratio
from v$sqlarea
where
executions > 100
or disk_reads > 1000
or buffer_gets > 1000
or rows_processed > 1000
order by
executions * 250 + disk_reads * 25 + buffer_gets desc
--
SELECT
PARSING_SCHEMA_NAME,
executions,
SQL_TEXT,
disk_reads,
buffer_gets,
elapsed_time / executions AS elapsed_time,
buffer_gets / decode(executions,0,1,executions) / 500 AS runtime,
bind_data,
MODULE,
sharable_mem,
persistent_mem,
runtime_mem,
rows_processed,
round(rows_processed/greatest(executions,1)) row_ratio,
round(disk_reads/greatest(executions,1)) disk_ratio,
round(buffer_gets/greatest(executions,1)) buffer_ratio,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
FROM
V$SQLAREA
WHERE
LAST_LOAD_TIME BETWEEN SYSDATE - 1 AND SYSDATE
AND PARSING_SCHEMA_NAME IN('WORLDGATE')
AND SQL_TEXT LIKE 'SELECT%'
ORDER BY
buffer_gets / decode(executions,0,1,executions) / 500 DESC
-- 10초 이상
select translate(sql_text,'&',':') ,
' Expected Run Time = '||
buffer_gets / decode(executions,0,1,executions) / 500 runt
from v$sqlarea
where buffer_gets / decode(executions,0,1,executions) / 500 > 10
and upper(sql_text) not like '%BEGIN%'
and upper(sql_text) not like '%SQLAREA%'
and upper(sql_text) not like '%DBA_%'
and upper(sql_text) not like '%USER_%'
and upper(sql_text) not like '%ALL_%'
order by executions desc
평균 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,
EXECUTIONS,BUFFER_PER_EXEC,SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,
BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
SQL_TEXT
FROM V$SQLAREA
WHERE LAST_ACTIVE_TIME > trunc(SYSDATE)-8/24
AND ROWNUM <= 5
ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
총 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM V$SQLAREA
ORDER BY BUFFER_GETS DESC )
WHERE ROWNUM <= 5
'ORACLE > ADMIN' 카테고리의 다른 글
Oracle Hang 상황에서 액티브 세션 리스트 얻기 (1) | 2012.01.02 |
---|---|
오라클 버전간의 파라미터 변경사항 비교 (Oracle 9i, 10g, 11g) (0) | 2011.12.28 |
oracle inactive session 정리 (0) | 2011.12.15 |
oracle Shared Server 와 Dedicate Server 의 접속 방식 (0) | 2011.12.13 |
Shared Server (MTS) Diagnostics [ID 1005259.6] (0) | 2011.12.08 |