행위

"V$SQL MONITOR"의 두 판 사이의 차이

DB CAFE

(새 문서: V$SQL_MONITOR V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQ...)
 
1번째 줄: 1번째 줄:
 
V$SQL_MONITOR
 
V$SQL_MONITOR
−
V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
 
  
−
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.
+
V$SQL_MONITOR는 Oracle에 의해 실행이 모니터링되었거나 모니터링중인 SQL 문을 표시합니다.
 +
SQL 문 실행을 모니터링 할 때마다 V$SQL_MONITOR에 항목이 작성됩니다.
 +
SQL 모니터링은 SQL 문이 병렬로 실행되거나 CPU 또는 I / O 시간이 최소 5 초 이상 소요될 때 자동으로 시작됩니다.
 +
모니터링중인 SQL 문이 실행 중일 때 V$SQL_MONITOR의 통계는 일반적으로 1 초에 한 번씩 거의 실시간으로 새로 고쳐집니다.  
 +
실행이 끝나면 모니터링 정보는 즉시 삭제되지 않습니다.  
 +
대신 V$SQL_MONITOR에 적어도 1 분 동안 보관됩니다.  
 +
새 문장이 모니터 될 때 엔트리는 결국 공간을 되찾기 위해 삭제됩니다.
  
−
Column Datatype Description
+
{| class="wikitable"
−
KEY NUMBER Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR
+
|-
−
STATUS VARCHAR2(19) SQL execution status:
+
!Column !!Datatype !!Description
 +
|-
 +
|KEY ||NUMBER ||Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR
 +
|-
 +
|STATUS || VARCHAR2(19) || SQL execution status:
 
EXECUTING - SQL statement is still executing
 
EXECUTING - SQL statement is still executing
  
16번째 줄: 25번째 줄:
  
 
DONE - Execution terminated (parallel execution)
 
DONE - Execution terminated (parallel execution)
−
 
+
|-
−
FIRST_REFRESH_TIME DATE Time when monitoring of the SQL statement started, generally a few seconds after execution start time
+
|FIRST_REFRESH_TIME || DATE || Time when monitoring of the SQL statement started, generally a few seconds after execution start time
−
LAST_REFRESH_TIME DATE Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
+
|-
−
REFRESH_COUNT NUMBER Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)
+
|LAST_REFRESH_TIME || DATE || Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
−
SID NUMBER Session identifier executing (or having executed) the SQL statement being monitored
+
|-
−
PROCESS_NAME VARCHAR2(5) Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)
+
|REFRESH_COUNT || NUMBER || Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)
−
SQL_ID VARCHAR2(13) SQL identifier of the statement being monitored
+
|-
−
SQL_EXEC_START DATE Time when the execution started
+
|SID || NUMBER || Session identifier executing (or having executed) the SQL statement being monitored
−
SQL_EXEC_ID NUMBER Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.
+
|-
−
SQL_PLAN_HASH_VALUE NUMBER SQL Plan hash value
+
|PROCESS_NAME || VARCHAR2(5) || Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)
−
SQL_CHILD_ADDRESS RAW(4 | 8) Address of the child cursor (can be used with SQL_ID to join with V$SQL)
+
|-
−
SESSION_SERIAL# NUMBER Session serial number executing the statement being monitored
+
|SQL_ID || VARCHAR2(13) || SQL identifier of the statement being monitored
−
PX_SERVER# NUMBER Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).
+
|-
−
PX_SERVER_GROUP NUMBER Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.
+
|SQL_EXEC_START || DATE || Time when the execution started
−
PX_SERVER_SET NUMBER Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server
+
|-
−
PX_QCINST_ID NUMBER Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL
+
|SQL_EXEC_ID || NUMBER || Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.
−
PX_QCSID NUMBER Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL
+
|-
−
ELAPSED_TIME NUMBER Elapsed time (in microseconds); updated as the statement executes
+
|SQL_PLAN_HASH_VALUE || NUMBER || SQL Plan hash value
−
CPU_TIME NUMBER CPU time (in microseconds); updated as the statement executes
+
|-
−
FETCHES NUMBER Number of fetches associated with the SQL statement; updated as the statement executes
+
|SQL_CHILD_ADDRESS || RAW(4 \| 8) || Address of the child cursor (can be used with SQL_ID to join with V$SQL)
−
BUFFER_GETS NUMBER Number of buffer get operations; updated as the statement executes
+
|-
−
DISK_READS NUMBER Number of disk reads; updated as the statement executes
+
|SESSION_SERIAL# || NUMBER || Session serial number executing the statement being monitored
−
DIRECT_WRITES NUMBER Number of direct writes; updated as the statement executes
+
|-
−
APPLICATION_WAIT_TIME NUMBER Application wait time (in microseconds); updated as the statement executes
+
|PX_SERVER# || NUMBER || Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).
−
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time (in microseconds); updated as the statement executes
+
|-
−
CLUSTER_WAIT_TIME NUMBER Cluster wait time (in microseconds); updated as the statement executes
+
|PX_SERVER_GROUP || NUMBER || Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.
−
USER_IO_WAIT_TIME NUMBER User I/O Wait Time (in microseconds); updated as the statement executes
+
|-
−
PLSQL_EXEC_TIME NUMBER PL/SQL execution time (in microseconds); updated as the statement executes
+
|PX_SERVER_SET || NUMBER || Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server
−
JAVA_EXEC_TIME NUMBER Java execution time (in microseconds); updated as the statement executes
+
|-
 +
|PX_QCINST_ID || NUMBER || Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL
 +
|-
 +
|PX_QCSID || NUMBER || Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL
 +
|-
 +
|ELAPSED_TIME || NUMBER || Elapsed time (in microseconds); updated as the statement executes
 +
|-
 +
|CPU_TIME || NUMBER || CPU time (in microseconds); updated as the statement executes
 +
|-
 +
|FETCHES || NUMBER || Number of fetches associated with the SQL statement; updated as the statement executes
 +
|-
 +
|BUFFER_GETS || NUMBER || Number of buffer get operations; updated as the statement executes
 +
|-
 +
|DISK_READS || NUMBER || Number of disk reads; updated as the statement executes
 +
|-
 +
|DIRECT_WRITES || NUMBER || Number of direct writes; updated as the statement executes
 +
|-
 +
|APPLICATION_WAIT_TIME || NUMBER || Application wait time (in microseconds); updated as the statement executes
 +
|-
 +
|CONCURRENCY_WAIT_TIME || NUMBER || Concurrency wait time (in microseconds); updated as the statement executes
 +
|-
 +
|CLUSTER_WAIT_TIME || NUMBER || Cluster wait time (in microseconds); updated as the statement executes
 +
|-
 +
|USER_IO_WAIT_TIME || NUMBER || User I/O Wait Time (in microseconds); updated as the statement executes
 +
|-
 +
|PLSQL_EXEC_TIME || NUMBER || PL/SQL execution time (in microseconds); updated as the statement executes
 +
|-
 +
|JAVA_EXEC_TIME NUMBER || || Java execution time (in microseconds); updated as the statement executes
 +
|}

2018년 11월 14일 (수) 13:23 판

thumb_up 추천메뉴 바로가기

V$SQL_MONITOR

V$SQL_MONITOR는 Oracle에 의해 실행이 모니터링되었거나 모니터링중인 SQL 문을 표시합니다. SQL 문 실행을 모니터링 할 때마다 V$SQL_MONITOR에 항목이 작성됩니다. SQL 모니터링은 SQL 문이 병렬로 실행되거나 CPU 또는 I / O 시간이 최소 5 초 이상 소요될 때 자동으로 시작됩니다. 모니터링중인 SQL 문이 실행 중일 때 V$SQL_MONITOR의 통계는 일반적으로 1 초에 한 번씩 거의 실시간으로 새로 고쳐집니다. 실행이 끝나면 모니터링 정보는 즉시 삭제되지 않습니다. 대신 V$SQL_MONITOR에 적어도 1 분 동안 보관됩니다. 새 문장이 모니터 될 때 엔트리는 결국 공간을 되찾기 위해 삭제됩니다.

Column Datatype Description
KEY NUMBER Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR
STATUS VARCHAR2(19) SQL execution status:

EXECUTING - SQL statement is still executing

DONE (ERROR) - Execution terminated with an error

DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched

DONE (ALL ROWS) - Execution terminated and all rows were fetched

DONE - Execution terminated (parallel execution)

FIRST_REFRESH_TIME DATE Time when monitoring of the SQL statement started, generally a few seconds after execution start time
LAST_REFRESH_TIME DATE Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
REFRESH_COUNT NUMBER Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)
SID NUMBER Session identifier executing (or having executed) the SQL statement being monitored
PROCESS_NAME VARCHAR2(5) Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)
SQL_ID VARCHAR2(13) SQL identifier of the statement being monitored
SQL_EXEC_START DATE Time when the execution started
SQL_EXEC_ID NUMBER Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.
SQL_PLAN_HASH_VALUE NUMBER SQL Plan hash value
SQL_CHILD_ADDRESS 8) Address of the child cursor (can be used with SQL_ID to join with V$SQL)
SESSION_SERIAL# NUMBER Session serial number executing the statement being monitored
PX_SERVER# NUMBER Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).
PX_SERVER_GROUP NUMBER Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.
PX_SERVER_SET NUMBER Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server
PX_QCINST_ID NUMBER Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL
PX_QCSID NUMBER Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL
ELAPSED_TIME NUMBER Elapsed time (in microseconds); updated as the statement executes
CPU_TIME NUMBER CPU time (in microseconds); updated as the statement executes
FETCHES NUMBER Number of fetches associated with the SQL statement; updated as the statement executes
BUFFER_GETS NUMBER Number of buffer get operations; updated as the statement executes
DISK_READS NUMBER Number of disk reads; updated as the statement executes
DIRECT_WRITES NUMBER Number of direct writes; updated as the statement executes
APPLICATION_WAIT_TIME NUMBER Application wait time (in microseconds); updated as the statement executes
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time (in microseconds); updated as the statement executes
CLUSTER_WAIT_TIME NUMBER Cluster wait time (in microseconds); updated as the statement executes
USER_IO_WAIT_TIME NUMBER User I/O Wait Time (in microseconds); updated as the statement executes
PLSQL_EXEC_TIME NUMBER PL/SQL execution time (in microseconds); updated as the statement executes
JAVA_EXEC_TIME NUMBER Java execution time (in microseconds); updated as the statement executes
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=V$SQL_MONITOR&oldid=712"