[Oracle] v$sesion, v$process, v$sql 컬럼 확인

쿼리를 조회하다 보면

자주 사용하는 테이블 및 컬럼이 있다.

이 부분은 자주 사용하다 보니 자연스럽게 해당 컬럼들이 어떠한 값을 가지는지 확인이 가능하다

하지만 그 외의 컬럼에 대해서는 잘 알지 못하는 경우가 있다.

아래는 그루비에서 퍼온 참고할만한 내용이다.

V$SESSION

• 세션에 대한 전반적인 내용을 보여줌

  Column	Value	Description
  SADDR	070000050F9798E0	오라클 메모리의 세션 주소
  SID	9542	세션 식별자 ( 세션 관련 뷰는 SID컬럼과 조인이 가능 )
  SERIAL#	5865	세션 시리얼 번호
  AUDSID	86855884	감사 세션 아이디( AUD$의 SESSIONID 컬럼과 조인 가능)
  PADDR	07000004FD0771A8	이 세션을 소유한 프로세스의 메모리 주소( V$PROCESS의 ADDR 컬럼과 조인 가능)
  USER#	361	오라클 사용자 식별자( DBA_USERS의 USER_ID 컬럼과 조인 가능)
  USERNAME	RAXSOFT	오라클 사용자 이름
  COMMAND	3	현재 수행되고 있는 명령문의 타입이 기록된다( AUDIT_ACTIONS의 ACTION 컬럼과 조인 가능)
  OWNERID	2147483644	Migratable session을 소유한 사용자의 식별자를 포함. 병렬 실행의 경우, 상위 2바이트는 Query Coordinator의 인스턴스의 ID, 하위 2바이트는 세션 번호를 포함. 그러나, 이 값이 2147483644의 값을 가진다면 의미 없다.
  TADDR	07000004B519BE58	트랜잭션 상태 객체의 주소( V$TRANSACTION의 ADDR컬럼과 조인 가능)
  LOCKWAIT		Lock의 주소( V$LOCK의 ADDR컬럼과 조인 가능)
  STATUS	ACTIVE	세션의 상태
  SERVER	DEDICATED	서버의 타입
  SCHEMA#	361	스키마 사용자 아이디
  SCHEMANAME	RAXSOFT	스키마 사용자 이름
  OSUSER	hopefully	OS 클라이언트 사용자 이름
  PROCESS	1436:648	OS 클라이언트 프로세스 ID
  MACHINE	HQ10-NE2256	OS Machine 이름
  TERMINAL	HQ10-NE2256	OS 터미널 이름
  PROGRAM	SQL*PLUS	OS 프로그램 이름
  TYPE	USER	세션 타입
  SQL_ADDRESS	0700000502F77800	현재 수행되고 있는 SQL문의 구별을 위한 주소 ( V$SQLAREA의 ADDRESS컬럼과 조인 가능)
  SQL_HASH_VALUE	156003993	현재 수행되고 있는 SQL문 구별을 위한 Hash 값( V$SQLAREA의 HASH_VALUE컬럼과 조인 가능)
  SQL_ID	gdwxvqw4nsvnt
  SQL_CHILD_NUMBER	0
  PREV_SQL_ADDR	070000043907DF38	가장 최근에 수행되었던 SQL문의 구별을 위한 주소( V$SQLAREA의 ADDRESS컬럼과 조인 가능)
  PREV_HASH_VALUE	3851972948	가장 최근에 수행되었던 SQL문 구별을 위한 Hash 값( V$SQLAREA의 HASH_VALUE컬럼과 조인 가능)
  PREV_SQL_ID	cg9wzx3kthwan
  PREV_CHILD_NUMBER	0
  PLSQL_ENTRY_OBJECT_ID
  PLSQL_ENTRY_SUBPROGRAM_ID
  PLSQL_OBJECT_ID
  PLSQL_SUBPROGRAM_ID
  MODULE	SQL*PLUS	현재 실행되고 있는 모듈의 이름
  MODULE_HASH	2468076772	모듈의 Hash 값
  ACTION	3.1.5 (Build:5)	현재 실행되고 있는 Action의 이름
  ACTION_HASH	775161987	Action의 Hash 값
  CLIENT_INFO		클라이언트의 정보 값
  FIXED_TABLE_SEQUENCE	429762060	데이터베이스에 콜을 하고, 다이나믹 퍼포먼스 테이블(Dynamic Perfomance Table)을 조회할 때마다 값이 증가된다.
  ROW_WAIT_OBJ#	341579	락으로 인해 기다리고 있는 로우가 속한 오브젝트 번호를 보여준다.( DBA_OBJECTS뷰의 OBJECT_ID 컬럼과 조인 가능)
  ROW_WAIT_FILE#	329	락으로 인해 기다리고 있는 로우가 속한 파일의 번호를 보여준다.( DBA_DATA_FILES의 FILE_ID, V$DATAFILE의 FILE# 컬럼들과 조인 가능)
  ROW_WAIT_BLOCK#	335700	락으로 인해 기다리고 있는 로우가 속한 블락 번호를 보여준다.
  ROW_WAIT_ROW#	0	락으로 인해 기다리고 있는 블락내의 로우 번호를 보여준다.
  LOGON_TIME	2008-03-06 오후 2:39:34	세션이 로그온한 시간을 나타낸다.
  LAST_CALL_ET	409	세션의 가장 최근의 DB Call이후 지난 시간을 보여준다.
  PDML_ENABLED	NO	이 컬럼은 PDML_STATUS로 바뀌었음.
  FAILOVER_TYPE	NONE
  FAILOVER_METHOD	NONE
  FAILED_OVER	NO
  RESOURCE_CONSUMER_GROUP		세션의 현재 Resource Consumer Group을 보여준다.( DBA_RSRC_CONSUMER_GROUPS뷰의 CONSUMER_GROUP컬럼과 조인 가능 )
  PDML_STATUS	DISABLED	세션의 현재 Parallel DML의 상태를 보여준다.
  PDDL_STATUS	ENABLED	세션의 현재 Parallel DDL의 상태를 보여준다.
  PQ_STATUS	ENABLED	세션의 현재 Parallel Query의 상태를 보여준다.
  CURRENT_QUEUE_DURATION	0
  CLIENT_IDENTIFIER		Global Application Context에서 이용한다.
  BLOCKING_SESSION_STATUS	UNKNOWN
  BLOCKING_INSTANCE
  BLOCKING_SESSION
  SEQ#	19373
  EVENT#	116
  EVENT	db file sequential read
  P1TEXT	file#
  P1	329
  P1RAW	149
  P2TEXT	block#
  P2	335700
  P2RAW	0000000000051F54
  P3TEXT	blocks
  P3	1
  P3RAW	1
  WAIT_CLASS_ID	1740759767
  WAIT_CLASS#	8
  WAIT_CLASS	User I/O
  WAIT_TIME	0
  SECONDS_IN_WAIT	0
  STATE	WAITING
  SERVICE_NAME	SYS$USERS
  SQL_TRACE	DISABLED
  SQL_TRACE_WAITS	FALSE
  SQL_TRACE_BINDS	FALSE

V$PROCESS

• 기술 예정

  Column	Value	Description
  ADDR	07000001D6C79EB8	프로세스의 메모리 주소
  PID	2	프로세스 ID
  SPID	3064180	OS 프로세스 ID
  USERNAME	RAXSOFT	OS 유저 이름
  SERIAL#	1	세션 시리얼 번호
  TERMINAL	UNKNOWN	OS 터미널 이름
  PROGRAM	oracle@ (PMON)	OS 프로그램 이름
  TRACEID		Trace ID
  BACKGROUND	1	Background일 때 1
  LATCHWAIT		Latch Wait 일때 메모리 주소
  LATCHSPIN		Latch Spinning 일때 메모리 주소
  PGA_USED_MEM	329394
  PGA_ALLOC_MEM	580290
  PGA_FREEABLE_MEM	0
  PGA_MAX_MEM	580290

V$SQL 11g

• V$SQL에 대해 10g와 11g 비교함

CNT	10g V$SQL	11g V$SQL	DATA TYPE	DESCRIPTION
1	SQL_TEXT	SQL_TEXT	VARCHAR2(1000)	First thousand characters of the SQL text for the current cursor
2	SQL_FULLTEXT	SQL_FULLTEXT	CLOB	Full text for the SQL statement exposed as a?CLOB?column. The full text of a SQL statement can be retrieved using this column instead of joining with the?V$SQL_TEXT?dynamic performance view.
3	SQL_ID	SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
4	SHARABLE_MEM	SHARABLE_MEM	NUMBER	Amount of shared memory used by the child cursor (in bytes)
5	PERSISTENT_MEM	PERSISTENT_MEM	NUMBER	Fixed amount of memory used for the lifetime of the child cursor (in bytes)
6	RUNTIME_MEM	RUNTIME_MEM	NUMBER	Fixed amount of memory required during the execution of the child cursor
7	SORTS	SORTS	NUMBER	Number of sorts that were done for the child cursor
8	LOADED_VERSIONS	LOADED_VERSIONS	NUMBER	Indicates whether the context heap is loaded (1) or not (0)
9	OPEN_VERSIONS	OPEN_VERSIONS	NUMBER	Indicates whether the child cursor is locked (1) or not (0)
10	USERS_OPENING	USERS_OPENING	NUMBER	Number of users executing the statement
11	FETCHES	FETCHES	NUMBER	Number of fetches associated with the SQL statement
12	EXECUTIONS	EXECUTIONS	NUMBER	Number of executions that took place on this object since it was brought into the library cache
13	PX_SERVERS_EXECUTIONS	PX_SERVERS_EXECUTIONS	NUMBER	Total number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
14	END_OF_FETCH_COUNT	END_OF_FETCH_COUNT	NUMBER	Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the?END_OF_FETCH_COUNT?column should be less or equal to the value of the?EXECUTIONS?column.
15	USERS_EXECUTING	USERS_EXECUTING	NUMBER	Number of users executing the statement
16	LOADS	LOADS	NUMBER	Number of times the object was either loaded or reloaded
17	FIRST_LOAD_TIME	FIRST_LOAD_TIME	VARCHAR2(76)	Timestamp of the parent creation time
18	INVALIDATIONS	INVALIDATIONS	NUMBER	Number of times this child cursor has been invalidated
19	PARSE_CALLS	PARSE_CALLS	NUMBER	Number of parse calls for this child cursor
20	DISK_READS	DISK_READS	NUMBER	Number of disk reads for this child cursor
21	DIRECT_WRITES	DIRECT_WRITES	NUMBER	Number of direct writes for this child cursor
22	BUFFER_GETS	BUFFER_GETS	NUMBER	Number of buffer gets for this child cursor
23	APPLICATION_WAIT_TIME	APPLICATION_WAIT_TIME	NUMBER	Application wait time (in microseconds)
24	CONCURRENCY_WAIT_TIME	CONCURRENCY_WAIT_TIME	NUMBER	Concurrency wait time (in microseconds)
25	CLUSTER_WAIT_TIME	CLUSTER_WAIT_TIME	NUMBER	Cluster wait time (in microseconds)
26	USER_IO_WAIT_TIME	USER_IO_WAIT_TIME	NUMBER	User I/O Wait Time (in microseconds)
27	PLSQL_EXEC_TIME	PLSQL_EXEC_TIME	NUMBER	PL/SQL execution time (in microseconds)
28	JAVA_EXEC_TIME	JAVA_EXEC_TIME	NUMBER	Java execution time (in microseconds)
29	ROWS_PROCESSED	ROWS_PROCESSED	NUMBER	Total number of rows the parsed SQL statement returns
30	COMMAND_TYPE	COMMAND_TYPE	NUMBER	Oracle command type definition
31	OPTIMIZER_MODE	OPTIMIZER_MODE	VARCHAR2(10)	Mode under which the SQL statement is executed
32	OPTIMIZER_COST	OPTIMIZER_COST	NUMBER	Cost of this query given by the optimizer
33	OPTIMIZER_ENV	OPTIMIZER_ENV	RAW(2000)	Optimizer environment
34	OPTIMIZER_ENV_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	NUMBER	Hash value for the optimizer environment
35	PARSING_USER_ID	PARSING_USER_ID	NUMBER	User ID of the user who originally built this child cursor
36	PARSING_SCHEMA_ID	PARSING_SCHEMA_ID	NUMBER	Schema ID that was used to originally build this child cursor
37	PARSING_SCHEMA_NAME	PARSING_SCHEMA_NAME	VARCHAR2(30)	Schema name that was used to originally build this child cursor
38	KEPT_VERSIONS	KEPT_VERSIONS	NUMBER	Indicates whether this child cursor has been marked to be kept pinned in the cache using the?DBMS_SHARED_POOL?package
39	ADDRESS	ADDRESS	RAW(8)	Address of the handle to the parent for this cursor
40	TYPE_CHK_HEAP	TYPE_CHK_HEAP	RAW(8)	Descriptor of the type check heap for this child cursor
41	HASH_VALUE	HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
42	OLD_HASH_VALUE	OLD_HASH_VALUE	NUMBER	Old SQL hash value
43	PLAN_HASH_VALUE	PLAN_HASH_VALUE	NUMBER	Numerical representation of the SQL plan for this cursor. Comparing one?PLAN_HASH_VALUE?to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
44	CHILD_NUMBER	CHILD_NUMBER	NUMBER	Number of this child cursor
45	SERVICE	SERVICE	VARCHAR2(64)	Service name
46	SERVICE_HASH	SERVICE_HASH	NUMBER	Hash value for the name listed in?SERVICE
47	MODULE	MODULE	VARCHAR2(64)	Contains the name of the module that was executing at the time that the SQL statement was first parsed, which is set by calling?DBMS_APPLICATION_INFO.SET_MODULE
48	MODULE_HASH	MODULE_HASH	NUMBER	Hash value of the module listed in the?MODULE?column
49	ACTION	ACTION	VARCHAR2(64)	Contains the name of the action that was executing at the time that the SQL statement was first parsed, which is set by calling?DBMS_APPLICATION_INFO.SET_ACTION
50	ACTION_HASH	ACTION_HASH	NUMBER	Hash value of the action listed in the?ACTION?column
51	SERIALIZABLE_ABORTS	SERIALIZABLE_ABORTS	NUMBER	Number of times the transaction fails to serialize, producing?ORA-08177?errors, per cursor
52	OUTLINE_CATEGORY	OUTLINE_CATEGORY	VARCHAR2(64)	If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank.
53	CPU_TIME	CPU_TIME	NUMBER	CPU time (in microseconds) used by this cursor for parsing, executing, and fetching
54	ELAPSED_TIME	ELAPSED_TIME	NUMBER	Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
55	OUTLINE_SID	OUTLINE_SID	NUMBER	Outline session identifier
56	CHILD_ADDRESS	CHILD_ADDRESS	RAW(8)	Address of the child cursor
57	SQLTYPE	SQLTYPE	NUMBER	Denotes the version of the SQL language used for this statement
58	REMOTE	REMOTE	VARCHAR2(1)	Indicates whether the cursor is remote mapped (Y) or not (N)
59	OBJECT_STATUS	OBJECT_STATUS	VARCHAR2(19)	Status of the cursor:  VALID - Valid, authorized without errors VALID_AUTH_ERROR - Valid, authorized with authorization errors VALID_COMPILE_ERROR - Valid, authorized with compilation errors VALID_UNAUTH - Valid, unauthorized INVALID_UNAUTH - Invalid, unauthorized INVALID - Invalid, unauthorized but keep the timestamp
60	LITERAL_HASH_VALUE	LITERAL_HASH_VALUE	NUMBER	Hash value of the literals which are replaced with system-generated bind variables and are to be matched, whenCURSOR_SHARING?is used. This is not the hash value for the SQL statement. If?CURSOR_SHARING?is not used, then the value is 0.
61	LAST_LOAD_TIME	LAST_LOAD_TIME	VARCHAR2(76)	Time at which the query plan (heap 6) was loaded into the library cache
62	IS_OBSOLETE	IS_OBSOLETE	VARCHAR2(1)	Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large.
63		IS_BIND_SENSITIVE	VARCHAR2(1)
64		IS_BIND_AWARE	VARCHAR2(1)
65		IS_SHAREABLE	VARCHAR2(1)
66	CHILD_LATCH	CHILD_LATCH	NUMBER	Child latch number that is protecting the cursor
67	SQL_PROFILE	SQL_PROFILE	VARCHAR2(64)	SQL profile
68		SQL_PATCH	VARCHAR2(30)
69		SQL_PLAN_BASELINE	VARCHAR2(30)
70	PROGRAM_ID	PROGRAM_ID	NUMBER	Program identifier
71	PROGRAM_LINE#	PROGRAM_LINE#	NUMBER	Program line number
72	EXACT_MATCHING_SIGNATURE	EXACT_MATCHING_SIGNATURE	NUMBER	Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
73	FORCE_MATCHING_SIGNATURE	FORCE_MATCHING_SIGNATURE	NUMBER	The signature used when the?CURSOR_SHARING?parameter is set to?FORCE
74	LAST_ACTIVE_TIME	LAST_ACTIVE_TIME	DATE	TIme at which the query plan was last active
75	BIND_DATA	BIND_DATA	RAW(2000)	Bind data
76		TYPECHECK_MEM	NUMBER
77		IO_CELL_OFFLOAD_ELIGIBLE_BYTES	NUMBER
78		IO_INTERCONNECT_BYTES	NUMBER
79		PHYSICAL_READ_REQUESTS	NUMBER
80		PHYSICAL_READ_BYTES	NUMBER
81		PHYSICAL_WRITE_REQUESTS	NUMBER
82		PHYSICAL_WRITE_BYTES	NUMBER
83		OPTIMIZED_PHY_READ_REQUESTS	NUMBER
84		LOCKED_TOTAL	NUMBER
85		PINNED_TOTAL	NUMBER
86		IO_CELL_UNCOMPRESSED_BYTES	NUMBER
87		IO_CELL_OFFLOAD_RETURNED_BYTES	NUMBER

[출처]

http://wiki.gurubee.net/display/CORE/Dynamic+View?

추가로 유휴 상태인 세션 확인 쿼리다.

유휴 상태 세션 찾기

select sid, serial#, username, trumc(last_call_et/3600,2)||'hr'last_call_et

from v$session

where last_call_et>7200 and username is not null

- last_call_et 는 "초" 단위이므로 7200 이면 2시간이된다.

유휴 상태 세션 죽이기

alter system kill '<sid>,<seril#>'

[출처]

http://oraclezombie.tistory.com/entry/%EC%9C%A0%ED%9C%B4idle-%EC%83%81%ED%83%9C%EC%9D%B8-%EC%84%B8%EC%85%98-%EB%B3%B4%EA%B8%B0