by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Dynamic View




목차

V$SESSION
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    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.