oracle 查看数据库操作进度的方法一 V$SESSION_LONGOPS
系统视图 V$SESSION_LONGOPS
记录在数据库运行时间超过6秒的操作进度。当前记录的主要有备份和恢复功能、统计收集和查询执行,以及其他更多的操作记录。
如果要监视查询操作进度,我们需要基于成本的优化器，设置如下内容:
第一 将TIMED_STATISTICS或SQL_TRACE参数设置为true
第二 用DBMS_STATS包收集统计信息
可以通过
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS程序包,添加关于应用程序特定的运行长时间运行的信息。
视图各列解释：
| Column | Datatype | Description |
| SID | NUMBER | Identifier of the session processing the long-running operation. If multiple sessions are cooperating in the long-running operation, then SID corresponds to the main or master session. |
| SERIAL# | NUMBER | Serial number of the session processing the long-running operation. If multiple sessions are cooperating in the long-running operation, then SERIAL# corresponds to the main or master session. SERIAL# is used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. |
| OPNAME | VARCHAR2(64) | Brief description of the operation |
| TARGET | VARCHAR2(64) | Object on which the operation is carried out |
| TARGET_DESC | VARCHAR2(32) | Description of the target |
| SOFAR | NUMBER | Units of work done so far for the operation specified in the OPNAME column |
| TOTALWORK | NUMBER | Total units of work for the operation specified in the OPNAME column |
| UNITS | VARCHAR2(32) | Units of measurement |
| START_TIME | DATE | Starting time of the operation |
| LAST_UPDATE_TIME | DATE | Time when statistics were last updated for the operation |
| TIMESTAMP | DATE | Timestamp specific to the operation |
| TIME_REMAINING | NUMBER | Estimate (in seconds) of time remaining for the operation to complete |
| ELAPSED_SECONDS | NUMBER | Number of elapsed seconds from the start of the operations |
| CONTEXT | NUMBER | Context |
| MESSAGE | VARCHAR2(512) | Statistics summary message |
| USERNAME | VARCHAR2(30) | User ID of the user performing the operation |
| SQL_ADDRESS | RAW(4 | 8) | Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation |
| SQL_HASH_VALUE | NUMBER | Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation |
| SQL_ID | VARCHAR2(13) | SQL identifier of the SQL statement associated with the long operation, if any |
| SQL_PLAN_HASH_VALUE | NUMBER | SQL plan hash value; NULL if SQL_ID is NULL |
| SQL_EXEC_START | DATE | Time when the execution of the SQL started; NULL if SQL_ID is NULL |
| SQL_EXEC_ID | NUMBER | SQL execution identifier (see V$SQL_MONITOR) |
| SQL_PLAN_LINE_ID | NUMBER | SQL plan line ID corresponding to the long operation; NULL if the long operation is not associated with a line of the execution plan |
| SQL_PLAN_OPERATION | VARCHAR2(30) | Plan operation name; NULL if SQL_PLAN_LINE_ID is NULL |
| SQL_PLAN_OPTIONS | VARCHAR2(30) | Plan operation options; NULL if SQL_PLAN_LINE_ID is NULL |
| QCSID | NUMBER | Session identifier of the parallel coordinator |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include: |
|
| ||
| 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. | ||
|
| ||
| 1: This value is used for rows containing data that pertain to only the root | ||
|
| ||
| n: Where n is the applicable container ID for the rows containing data | ||
|
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26342786/viewspace-2966866/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
