v$session_longops

最新推荐文章于 2016-03-09 13:03:03 发布

Laughing

最新推荐文章于 2016-03-09 13:03:03 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 基础 Oracle SQL 调优文章标签： session sql statistics insert date query

本文链接：https://blog.youkuaiyun.com/aqszhuaihuai/article/details/7781651

Oracle 基础同时被 2 个专栏收录

56 篇文章

订阅专栏

Oracle SQL 调优

30 篇文章

订阅专栏

本文介绍Oracle数据库中如何使用v$session_longops视图监控长时间运行的操作，如备份、恢复、统计信息收集及查询执行等，并展示了如何设置参数及使用SQL进行具体监控。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

--模拟一下
SQL> create table long_ as select level lv,rownum rn from dual connect by level<10000000;

表已创建。

SQL> insert into long_ select level lv,rownum rn from dual connect by level<10000000;

已创建9999999行。

SQL> commit;

提交完成。

SQL> insert into long_ select level lv,rownum rn from dual connect by level<10000000;

已创建9999999行。

SQL> commit;

提交完成。

SQL> insert into long_ select level lv,rownum rn from dual connect by level<10000000;

已创建9999999行。

SQL> commit;

提交完成。

SQL> select count(*) from long_;

  COUNT(*)
----------
  39999996

SQL> desc v$session_longops
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 OPNAME                                             VARCHAR2(64)
 TARGET                                             VARCHAR2(64)
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 TIMESTAMP                                          DATE
 TIME_REMAINING                                     NUMBER
 ELAPSED_SECONDS                                    NUMBER
 CONTEXT                                            NUMBER
 MESSAGE                                            VARCHAR2(512)
 USERNAME                                           VARCHAR2(30)
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 QCSID                                              NUMBER

SQL> col sid for 99999
SQL> col opname for a20
SQL> col sofar for 9999999999
SQL> col totalwork for 9999999999
SQL> col units for a20
SQL> col sql_hash_value for 999999999999
SQL> col sql_id for a15
SQL> set linesize 200
--v$session_longops中记录了这次全表扫描操作
SQL> select sid,opname,sofar,totalwork,units,sql_hash_value,sql_id from v$session_longops where sql_id is not null;

   SID OPNAME                     SOFAR   TOTALWORK UNITS                SQL_HASH_VALUE SQL_ID
------ -------------------- ----------- ----------- -------------------- -------------- ---------------
   145 Table Scan                 92908       92908 Blocks                   1268759093 gw2gzt55tzfjp

--找出sql
SQL> select a.sql_text from v$sqlarea a,v$session_longops b where b.sql_id=a.sql_id;

SQL_TEXT
-------------------------------------------------------------------------------------------------------

select count(*) from long_

v$session_longops.START_TIME字段代表操作开始时间。