v$session_Longops

This view 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:

• Set the TIMED_STATISTICS or SQL_TRACE parameter to true

• Gather 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.

注意：并没有所有超过6秒的操作都记录在这里，For example one can find hash joins in v$session_longops, but you won’t find there nested loop joins even if they are longer than 6 seconds and are joining very big data sets.

Each new version of Oracle adds several new types of built in long operations that are shown in v$session_longops. Some of them are:

• Table scan;
• Index Fast Full Scan;
• Hash join;
• Sort/Merge;
• Sort Output;
• Rollback;
• Gather Table's Index Statistics.

V$SESSION_LONGOPS列说明

l SID：Session标识

l SERIAL#：Session串号

l OPNAME：操作简要说明

l TARGET：操作运行所在的对象

l TARGET_DESC：目标对象说明

l SOFAR：至今为止完成的工作量

l TOTALWORK：总工作量

l UNITS：工作量单位

l START_TIME：操作开始时间

l LAST_UPDATE_TIME：统计项最后更新时间

l TIME_REMAINING：预计完成操作的剩余时间(秒)

l ELAPSED_SECONDS：从操作开始总花费时间(秒)

l CONTEXT：前后关系

l MESSAGE：统计项的完整描述

l USERNAME：执行操作的用户ID

l SQL_ADDRESS：用于连接查询的列

l SQL_HASH_VALUE：用于连接查询的列

l QCSID：

示例：

SQL> set timing on

SQL> create table ttt as select level lv,rownum rn from dual connect by level<10000000; --创建一个临时表

Table created

Executed in 19.5 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> select * from (select * from ttt order by lv desc) where rownum<2; --执行一个费时的查询

LV RN

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

9999999 9999999

Executed in 9.766 seconds --哈哈，成功超过6秒

SQL> select sid,opname,sofar,totalwork,units,sql_hash_value from v$session_longops; ----看看v$session_longops中是不是已经有记录了

SID OPNAME SOFAR TOTALWORK UNITS SQL_HASH_VALUE

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

10 Table Scan 47276 47276 Blocks 2583310173

Executed in 0.047 seconds

SQL> select a.sql_text from v$sqlarea a,v$session_longops b where a.HASH_VALUE=b.SQL_HASH_VALUE; --通过hash_value联系查询出刚执行的查询语句。

SQL_TEXT

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

select * from (select * from ttt order by lv desc) where rownum<2

Executed in 0.063 seconds

这里有篇好文：http://www.gplivna.eu/papers/v$session_longops.htm

Hash join

Hash joins are usually used joining large data sets. The performance of hash joins strongly depends on available memory either allocated by pga_aggregate_target (if using workarea_size_policy = auto) or hash_area_size (if using workarea_size_policy = manual). Block is the unit to measure hash join work in v$session_longops.

Table scan

Table scan is one of the most common long operations. It is shown only when done via FULL SCAN and is measured in database blocks occupied by the table.

Work behind the scenes

Actually Table scan may hide many other operations behind the scenes, therefore don’t be surprised if it takes very long time according to v$session_longops. Some of the examples are as follows (Example for each of the following bullet can be found here VariousTableScans.txt):

• Simple query scanning all the rows in the table;
• Query with a filter;
• Creation of an index and table scan to gather necessary info for the index;
• Alter table column from NULL to NOT NULL;
• Query with join where outer table is accessed via FULL SCAN and inner table is joined using NESTED LOOPS join, so table scan actually includes a hidden join;
• A cursor in PL/SQL that does a full scan on the table but inside the cursor the possibilities to do something are infinite.

All above statements according to v$session_longops are doing the same amount of work i.e. scanning 94577 blocks of the table BIG, but the time taken as well as the total work done is rather different.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/668365/viewspace-1003332/，如需转载，请注明出处，否则将追究法律责任。