DBMS/오라클] 각종 세션/커서 관련 유용한 쿼리

각종 세션/커서 관련 유용한 쿼리 | Oracle DB 2006/03/31 10:11


http://blog.naver.com/yc7497/130003056914


아래 모니터링 쿼리문은 여기 저기 기웃거리며 동냥해서 얻은 지식들을 적어 놓은 것입니다.
순서없이 뒤죽박죽이어서 보시기 불편할 지도 모르겠네요.

사용법은 오라클이 설치된 서버에 텔넷을 이용하여 root계정으로
접속후에 top 혹은 topas 명령으로 프로세스들을 감시하면서 cpu를 많이 차지하는 프로세스 아이디등을 발견하여 조치하는 것입니다.



==================================================

/*********************************************************************************************
* 각 테이블스페이스 사용용량 체크하기
*********************************************************************************************/
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'

/*hash_value 가지고 SQL 문장 찾아내는 쿼리 */
select sql_text from v$sqltext where hash_value= 317853294


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

/*인엑티브 상태의 세션들-로그온타입이 과도하게 지나도록 없어지지 않는것은 세션을 죽이도록 한다
단 machine이 머신이름 에 해당하는 것만 죽이도록 한다
prev_hash_value 로 해쉬밸류를 알아내어 쿼리문을 확인할 수 있다
*/
select sid,serial#,username,status,logon_time,prev_hash_value,machine
from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'KILLED'

SELECT COUNT(*) FROM V$SESSION WHERE MACHINE ='머신이름' AND SCHEMANAME ='스키마이름'  AND STATUS = 'ACTIVE'
   
--현재 스키마이름 스키마로 실행중인(active) 프로세스를 알아내는 쿼리문
select SID, SERIAL#, SQL_HASH_VALUE, PREV_HASH_VALUE,ROW_WAIT_OBJ#, LOGON_TIME, MACHINE
from v$session
where schemaname ='스키마이름' and status = 'ACTIVE'
 
--위에서 알아내 hash_value 값을 이용하여 해당 프로세스가 실행중인 쿼리문 알아내기
select * from v$sqltext where hash_value = 2626426688-
order by piece

select * from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'ACTIVE' and ROW_WAIT_OBJ# > 0



/******************************************************************************************/
 현재 커서 수 확인하는 Query
/******************************************************************************************/


SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = '유저이름'
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





 
 

 
   116, 15217
 
    select sql_text from v$sqltext
 where hash_value= 3252988466 --1389452958
 order by piece
 
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
 
 
  select user_name, sql_text, count(*) cnt from sys.v_$open_cursor
 where user_name = '유저이름' and sid = 114
  group by user_name, sql_text
  order by cnt desc

 alter system kill session '27,127'

--ALTER SYSTEM KILL SESSION '45, 3977'
--이런식으로 통계정보를 생성하신 후에

 analyze table emp compute statistics;

 alter table customers pctfree 5 ;

select * from dba_tables
where table_name = 'RP_PART_MST'




/*******************************************************************************
* LOCK 관련
*******************************************************************************/

--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 v$session
  where sid in (80,100)

SELECT l.SESSION_ID,
    LPAD('  ', DECODE(l.xidusn,0,3,0))
    ||l.oracle_username "User Name",
    o.owner, o.object_name, o.object_type
    FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER by o.object_id, 1 desc


--아래 table 은 존재하지 않네...

SELECT sw.username "WAITING_USER", bu.username "LOCKING_USER",
    dw.lock_type, dw.mode_held, dw.mode_requested,
    dw.waiting_session, dw.holding_session
FROM dba_waiters dw, v$session sw, v$session bu
WHERE dw.waiting_session = sw.sid
ANd dw.holding_session = bu.sid  
   

SELECT s.username, s.sid, s.serial#
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid


SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id

SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id


Select *
FROM
  all_col_comments
WHERE
  table_name = '테이블이름'
 
 
/************************************************
 alter session으로 죽지않는 프로세스 죽이기
 1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
 2.su -오라클계정
 3.sqlplus '/as sysdba''
 4.connect system/sys
 5.ALTER SYSTEM KILL SESSION '137,1723'
**************************************************/