반응형

쿼리를 조회하다 보면

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

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


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


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


V$SESSION

  • 세션에 대한 전반적인 내용을 보여줌
    ColumnValueDescription
    SADDR070000050F9798E0오라클 메모리의 세션 주소
    SID9542세션 식별자 ( 세션 관련 뷰는 SID컬럼과 조인이 가능 )
    SERIAL#5865세션 시리얼 번호
    AUDSID86855884감사 세션 아이디( AUD$의 SESSIONID 컬럼과 조인 가능)
    PADDR07000004FD0771A8이 세션을 소유한 프로세스의 메모리 주소( V$PROCESS의 ADDR 컬럼과 조인 가능)
    USER#361오라클 사용자 식별자( DBA_USERS의 USER_ID 컬럼과 조인 가능)
    USERNAMERAXSOFT오라클 사용자 이름
    COMMAND3현재 수행되고 있는 명령문의 타입이 기록된다( AUDIT_ACTIONS의 ACTION 컬럼과 조인 가능)
    OWNERID2147483644Migratable session을 소유한 사용자의 식별자를 포함. 병렬 실행의 경우, 상위 2바이트는 Query Coordinator의 인스턴스의 ID, 하위 2바이트는 세션 번호를 포함. 그러나, 이 값이 2147483644의 값을 가진다면 의미 없다.
    TADDR07000004B519BE58트랜잭션 상태 객체의 주소( V$TRANSACTION의 ADDR컬럼과 조인 가능)
    LOCKWAIT Lock의 주소( V$LOCK의 ADDR컬럼과 조인 가능)
    STATUSACTIVE세션의 상태
    SERVERDEDICATED서버의 타입
    SCHEMA#361스키마 사용자 아이디
    SCHEMANAMERAXSOFT스키마 사용자 이름
    OSUSERhopefullyOS 클라이언트 사용자 이름
    PROCESS1436:648OS 클라이언트 프로세스 ID
    MACHINEHQ10-NE2256OS Machine 이름
    TERMINALHQ10-NE2256OS 터미널 이름
    PROGRAMSQL*PLUSOS 프로그램 이름
    TYPEUSER세션 타입
    SQL_ADDRESS0700000502F77800현재 수행되고 있는 SQL문의 구별을 위한 주소 ( V$SQLAREA의 ADDRESS컬럼과 조인 가능)
    SQL_HASH_VALUE156003993현재 수행되고 있는 SQL문 구별을 위한 Hash 값( V$SQLAREA의 HASH_VALUE컬럼과 조인 가능)
    SQL_IDgdwxvqw4nsvnt 
    SQL_CHILD_NUMBER0 
    PREV_SQL_ADDR070000043907DF38가장 최근에 수행되었던 SQL문의 구별을 위한 주소( V$SQLAREA의 ADDRESS컬럼과 조인 가능)
    PREV_HASH_VALUE3851972948가장 최근에 수행되었던 SQL문 구별을 위한 Hash 값( V$SQLAREA의 HASH_VALUE컬럼과 조인 가능)
    PREV_SQL_IDcg9wzx3kthwan 
    PREV_CHILD_NUMBER0 
    PLSQL_ENTRY_OBJECT_ID  
    PLSQL_ENTRY_SUBPROGRAM_ID  
    PLSQL_OBJECT_ID  
    PLSQL_SUBPROGRAM_ID  
    MODULESQL*PLUS현재 실행되고 있는 모듈의 이름
    MODULE_HASH2468076772모듈의 Hash 값
    ACTION3.1.5 (Build:5)현재 실행되고 있는 Action의 이름
    ACTION_HASH775161987Action의 Hash 값
    CLIENT_INFO 클라이언트의 정보 값
    FIXED_TABLE_SEQUENCE429762060데이터베이스에 콜을 하고, 다이나믹 퍼포먼스 테이블(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_TIME2008-03-06 오후 2:39:34세션이 로그온한 시간을 나타낸다.
    LAST_CALL_ET409세션의 가장 최근의 DB Call이후 지난 시간을 보여준다.
    PDML_ENABLEDNO이 컬럼은 PDML_STATUS로 바뀌었음.
    FAILOVER_TYPENONE 
    FAILOVER_METHODNONE 
    FAILED_OVERNO 
    RESOURCE_CONSUMER_GROUP 세션의 현재 Resource Consumer Group을 보여준다.( DBA_RSRC_CONSUMER_GROUPS뷰의 CONSUMER_GROUP컬럼과 조인 가능 )
    PDML_STATUSDISABLED세션의 현재 Parallel DML의 상태를 보여준다.
    PDDL_STATUSENABLED세션의 현재 Parallel DDL의 상태를 보여준다.
    PQ_STATUSENABLED세션의 현재 Parallel Query의 상태를 보여준다.
    CURRENT_QUEUE_DURATION0 
    CLIENT_IDENTIFIER Global Application Context에서 이용한다.
    BLOCKING_SESSION_STATUSUNKNOWN 
    BLOCKING_INSTANCE  
    BLOCKING_SESSION  
    SEQ#19373 
    EVENT#116 
    EVENTdb file sequential read 
    P1TEXTfile# 
    P1329 
    P1RAW149 
    P2TEXTblock# 
    P2335700 
    P2RAW0000000000051F54 
    P3TEXTblocks 
    P31 
    P3RAW1 
    WAIT_CLASS_ID1740759767 
    WAIT_CLASS#8 
    WAIT_CLASSUser I/O 
    WAIT_TIME0 
    SECONDS_IN_WAIT0 
    STATEWAITING 
    SERVICE_NAMESYS$USERS 
    SQL_TRACEDISABLED 
    SQL_TRACE_WAITSFALSE 
    SQL_TRACE_BINDSFALSE 

V$PROCESS

  • 기술 예정
    ColumnValueDescription
    ADDR07000001D6C79EB8프로세스의 메모리 주소
    PID2프로세스 ID
    SPID3064180OS 프로세스 ID
    USERNAMERAXSOFTOS 유저 이름
    SERIAL#1세션 시리얼 번호
    TERMINALUNKNOWNOS 터미널 이름
    PROGRAMoracle@ (PMON)OS 프로그램 이름
    TRACEID Trace ID
    BACKGROUND1Background일 때 1
    LATCHWAIT Latch Wait 일때 메모리 주소
    LATCHSPIN Latch Spinning 일때 메모리 주소
    PGA_USED_MEM329394 
    PGA_ALLOC_MEM580290 
    PGA_FREEABLE_MEM0 
    PGA_MAX_MEM580290 

V$SQL 11g

  • V$SQL에 대해 10g와 11g 비교함
CNT10g V$SQL11g V$SQLDATA TYPEDESCRIPTION
1SQL_TEXTSQL_TEXTVARCHAR2(1000)First thousand characters of the SQL text for the current cursor
2SQL_FULLTEXTSQL_FULLTEXTCLOBFull 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.
3SQL_IDSQL_IDVARCHAR2(13)SQL identifier of the parent cursor in the library cache
4SHARABLE_MEMSHARABLE_MEMNUMBERAmount of shared memory used by the child cursor (in bytes)
5PERSISTENT_MEMPERSISTENT_MEMNUMBERFixed amount of memory used for the lifetime of the child cursor (in bytes)
6RUNTIME_MEMRUNTIME_MEMNUMBERFixed amount of memory required during the execution of the child cursor
7SORTSSORTSNUMBERNumber of sorts that were done for the child cursor
8LOADED_VERSIONSLOADED_VERSIONSNUMBERIndicates whether the context heap is loaded (1) or not (0)
9OPEN_VERSIONSOPEN_VERSIONSNUMBERIndicates whether the child cursor is locked (1) or not (0)
10USERS_OPENINGUSERS_OPENINGNUMBERNumber of users executing the statement
11FETCHESFETCHESNUMBERNumber of fetches associated with the SQL statement
12EXECUTIONSEXECUTIONSNUMBERNumber of executions that took place on this object since it was brought into the library cache
13PX_SERVERS_EXECUTIONSPX_SERVERS_EXECUTIONSNUMBERTotal number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
14END_OF_FETCH_COUNTEND_OF_FETCH_COUNTNUMBERNumber 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.
15USERS_EXECUTINGUSERS_EXECUTINGNUMBERNumber of users executing the statement
16LOADSLOADSNUMBERNumber of times the object was either loaded or reloaded
17FIRST_LOAD_TIMEFIRST_LOAD_TIMEVARCHAR2(76)Timestamp of the parent creation time
18INVALIDATIONSINVALIDATIONSNUMBERNumber of times this child cursor has been invalidated
19PARSE_CALLSPARSE_CALLSNUMBERNumber of parse calls for this child cursor
20DISK_READSDISK_READSNUMBERNumber of disk reads for this child cursor
21DIRECT_WRITESDIRECT_WRITESNUMBERNumber of direct writes for this child cursor
22BUFFER_GETSBUFFER_GETSNUMBERNumber of buffer gets for this child cursor
23APPLICATION_WAIT_TIMEAPPLICATION_WAIT_TIMENUMBERApplication wait time (in microseconds)
24CONCURRENCY_WAIT_TIMECONCURRENCY_WAIT_TIMENUMBERConcurrency wait time (in microseconds)
25CLUSTER_WAIT_TIMECLUSTER_WAIT_TIMENUMBERCluster wait time (in microseconds)
26USER_IO_WAIT_TIMEUSER_IO_WAIT_TIMENUMBERUser I/O Wait Time (in microseconds)
27PLSQL_EXEC_TIMEPLSQL_EXEC_TIMENUMBERPL/SQL execution time (in microseconds)
28JAVA_EXEC_TIMEJAVA_EXEC_TIMENUMBERJava execution time (in microseconds)
29ROWS_PROCESSEDROWS_PROCESSEDNUMBERTotal number of rows the parsed SQL statement returns
30COMMAND_TYPECOMMAND_TYPENUMBEROracle command type definition
31OPTIMIZER_MODEOPTIMIZER_MODEVARCHAR2(10)Mode under which the SQL statement is executed
32OPTIMIZER_COSTOPTIMIZER_COSTNUMBERCost of this query given by the optimizer
33OPTIMIZER_ENVOPTIMIZER_ENVRAW(2000)Optimizer environment
34OPTIMIZER_ENV_HASH_VALUEOPTIMIZER_ENV_HASH_VALUENUMBERHash value for the optimizer environment
35PARSING_USER_IDPARSING_USER_IDNUMBERUser ID of the user who originally built this child cursor
36PARSING_SCHEMA_IDPARSING_SCHEMA_IDNUMBERSchema ID that was used to originally build this child cursor
37PARSING_SCHEMA_NAMEPARSING_SCHEMA_NAMEVARCHAR2(30)Schema name that was used to originally build this child cursor
38KEPT_VERSIONSKEPT_VERSIONSNUMBERIndicates whether this child cursor has been marked to be kept pinned in the cache using the?DBMS_SHARED_POOL?package
39ADDRESSADDRESSRAW(8)Address of the handle to the parent for this cursor
40TYPE_CHK_HEAPTYPE_CHK_HEAPRAW(8)Descriptor of the type check heap for this child cursor
41HASH_VALUEHASH_VALUENUMBERHash value of the parent statement in the library cache
42OLD_HASH_VALUEOLD_HASH_VALUENUMBEROld SQL hash value
43PLAN_HASH_VALUEPLAN_HASH_VALUENUMBERNumerical 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).
44CHILD_NUMBERCHILD_NUMBERNUMBERNumber of this child cursor
45SERVICESERVICEVARCHAR2(64)Service name
46SERVICE_HASHSERVICE_HASHNUMBERHash value for the name listed in?SERVICE
47MODULEMODULEVARCHAR2(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
48MODULE_HASHMODULE_HASHNUMBERHash value of the module listed in the?MODULE?column
49ACTIONACTIONVARCHAR2(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
50ACTION_HASHACTION_HASHNUMBERHash value of the action listed in the?ACTION?column
51SERIALIZABLE_ABORTSSERIALIZABLE_ABORTSNUMBERNumber of times the transaction fails to serialize, producing?ORA-08177?errors, per cursor
52OUTLINE_CATEGORYOUTLINE_CATEGORYVARCHAR2(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.
53CPU_TIMECPU_TIMENUMBERCPU time (in microseconds) used by this cursor for parsing, executing, and fetching
54ELAPSED_TIMEELAPSED_TIMENUMBERElapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
55OUTLINE_SIDOUTLINE_SIDNUMBEROutline session identifier
56CHILD_ADDRESSCHILD_ADDRESSRAW(8)Address of the child cursor
57SQLTYPESQLTYPENUMBERDenotes the version of the SQL language used for this statement
58REMOTEREMOTEVARCHAR2(1)Indicates whether the cursor is remote mapped (Y) or not (N)
59OBJECT_STATUSOBJECT_STATUSVARCHAR2(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
60LITERAL_HASH_VALUELITERAL_HASH_VALUENUMBERHash 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.
61LAST_LOAD_TIMELAST_LOAD_TIMEVARCHAR2(76)Time at which the query plan (heap 6) was loaded into the library cache
62IS_OBSOLETEIS_OBSOLETEVARCHAR2(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_SENSITIVEVARCHAR2(1)  
64 IS_BIND_AWAREVARCHAR2(1)  
65 IS_SHAREABLEVARCHAR2(1)  
66CHILD_LATCHCHILD_LATCHNUMBERChild latch number that is protecting the cursor
67SQL_PROFILESQL_PROFILEVARCHAR2(64)SQL profile
68 SQL_PATCHVARCHAR2(30)  
69 SQL_PLAN_BASELINEVARCHAR2(30)  
70PROGRAM_IDPROGRAM_IDNUMBERProgram identifier
71PROGRAM_LINE#PROGRAM_LINE#NUMBERProgram line number
72EXACT_MATCHING_SIGNATUREEXACT_MATCHING_SIGNATURENUMBERSignature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
73FORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATURENUMBERThe signature used when the?CURSOR_SHARING?parameter is set to?FORCE
74LAST_ACTIVE_TIMELAST_ACTIVE_TIMEDATETIme at which the query plan was last active
75BIND_DATABIND_DATARAW(2000)Bind data
76 TYPECHECK_MEMNUMBER  
77 IO_CELL_OFFLOAD_ELIGIBLE_BYTESNUMBER  
78 IO_INTERCONNECT_BYTESNUMBER  
79 PHYSICAL_READ_REQUESTSNUMBER  
80 PHYSICAL_READ_BYTESNUMBER  
81 PHYSICAL_WRITE_REQUESTSNUMBER  
82 PHYSICAL_WRITE_BYTESNUMBER  
83 OPTIMIZED_PHY_READ_REQUESTSNUMBER  
84 LOCKED_TOTALNUMBER  
85 PINNED_TOTALNUMBER  
86 IO_CELL_UNCOMPRESSED_BYTESNUMBER  
87 IO_CELL_OFFLOAD_RETURNED_BYTESNUMBER 

[출처]

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

반응형

+ Recent posts