Oracle v$session_longops 视图说明

 

 

. 官网说明

1.1 v$session_longops

       V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

 

       To monitor query execution progress, you must be using the cost-based optimizer and you must:

       1Set the TIMED_STATISTICS or SQL_TRACE parameters to true

       2Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

 

       -- 使用条件

 

       You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.

 

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

TOTALWORK

NUMBER

Total units of work

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

 

1.2  SQL_TRACE

Property

Description

Parameter type

Boolean

Default value

false

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

true | false

 

       SQL_TRACE enables or disables the SQL trace facility. Setting this parameter to true provides information on tuning that you can use to improve performance.

 

Caution:

       Using this initialization parameter to enable the SQL trace facility for the entire instance can have a severe performance impact. Enable the facility for specific sessions using the ALTER SESSION statement. If you must enable the facility on an entire production environment, then you can minimize performance impact by:

(1).      Maintaining at least 25% idle CPU capacity

(2).      Maintaining adequate disk space for the USER_DUMP_DEST location

(3).      Striping disk space over sufficient disks

 

Note:

       The SQL_TRACE parameter is deprecated. Oracle recommends that you use the DBMS_MONITOR and DBMS_SESSION packages instead. SQL_TRACE is retained for backward compatibility only.

      SQL_TRACE 已经被弃用了.

 

1.3  TIMED_STATISTICS

Property

Description

Parameter type

Boolean

Default value

If STATISTICS_LEVEL is set to TYPICAL or ALL, then true

If STATISTICS_LEVEL is set to BASIC, then false

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

true | false

 

TIMED_STATISTICS specifies whether or not statistics related to time are collected.

 

Values:

true: The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.

 

false: The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.

 

       Starting with release 11.1.0.7.0, the value of the TIMED_STATISTICS parameter cannot be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL.

 

       On some systems with very fast timer access, Oracle might enable timing even if this parameter is set to false. On these systems, setting the parameter to true can sometimes produce more accurate statistics for long-running operations.

 

 

.  相关测试

 

SYS@anqing2(rac2)> show parameter sql_trace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sql_trace                            boolean     FALSE

 

 

SYS@anqing2(rac2)> show parameter TIMED_STATISTICS

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

timed_statistics                     boolean     TRUE

 

 

该视图通常配合V$SESSION视图,来分析SQL运行缓慢的原因。

 

查询未完成操作的信息

单实例

/* Formatted on 2011/6/22 21:20:53 (QP5 v5.163.1008.3004) */

SELECT sid,

       MESSAGE,

       start_time,

       last_update_time,

       time_remaining,

       elapsed_seconds

  FROM V$SESSION_LONGOPS

 WHERE time_remaining > 0;

 

 

RAC

/* Formatted on 2011/6/22 21:21:27 (QP5 v5.163.1008.3004) */

SELECT inst_id,

       sid,

       MESSAGE,

       start_time,

       last_update_time,

       time_remaining,

       elapsed_seconds

  FROM GV$SESSION_LONGOPS

 WHERE time_remaining > 0;

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716();   DBA2 群:62697977()   DBA3 群:62697850()  

DBA 超级群:63306533();  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

posted @ 2011-06-22 21:42  hibernate我最强  阅读(217)  评论(0编辑  收藏  举报