V$SQL MONITOR
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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 |