V$SESSION 테이블 활용

/*오라클서버의 메모리에 관한 테이블*/
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

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'
**************************************************/