v$session_longops

最新推荐文章于 2025-08-31 09:54:39 发布

转载最新推荐文章于 2025-08-31 09:54:39 发布 · 265 阅读

文章标签：

#数据库

本文介绍了Oracle数据库中如何利用V$SESSION_LONGOPS视图监控长时间运行的事务，并详细讲解了DBMS_APPLICATION_INFO包的使用方法，以便更精确地定位问题SQL及其所在程序模块。

这个视图的来源大致是这样的:

SELECT inst_id, ksulosno, ksulosrn, ksulopna, ksulotna, ksulotde, ksulosfr,
       ksulotot, ksulouni,
       TO_DATE (ksulostm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       TO_DATE (ksulolut, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       DECODE (SIGN (ksulotot - ksulosfr),
               -1, TO_NUMBER (NULL),
               DECODE (ksulosfr,
                       0, TO_NUMBER (NULL),
                       ROUND (ksuloetm * ((ksulotot - ksulosfr) / ksulosfr))
                      )
              ),
       ksuloetm, ksuloctx, ksulomsg, ksulounm, ksulosql, ksulosqh, ksuloqid
  FROM x$ksulop;

补充信息: 一篇非常好的解释文档。有些内容我之前也不知道。

X$ksulop, kernel service, user long operation. V$Session_LONGOPS 这个视图将显示超过六秒钟的操作。

前提条件: 1) Oracle优化器使用 CBO; 2) 数据对象已经收集了统计信息; 3) 初始化参数 TIMED_STATISTICS 或是 SQL_TRACE parameter 设定为 TRUE 例子：

 SELECT opname, time_remaining, elapsed_seconds, MESSAGE
  FROM v$session_longops
 WHERE time_remaining > 0;

很多时候数据库管理员需要监控数据中有哪些长时间运行的操作或者估算末个操作需要花费的时间。这有点类似windows进度条，事实是要真正做到100% 准确是不太现实的，计算机中机会没有任何运算能够线性的运行。oracle也提供了动态性能视图v$session_longops来反映一些长时间运行 的操作。最基本的原则是超过6s的事务将会在视图记录中。但也并不是所有超过6s的事务都会记录，例如hash join会被记录，而nested loop join就不会出现在其中。这个地方有时候会有些困惑，事务时间超过6s也没有在v$session_longops中显示？首先我们要明白的是：v$session_longops线性计算事务进度的(通常以读取的block为单位)而不计其它因素的影响，我们在估算事务时间需要考虑到其中的误差值。 通常记录的事务类型包括：Table scan，Index Fast Full Scan，hash join，sort/merge，sort/output等等，在每一个新的release版本，oracle都会加入新支持的事务类型。

在实际当中(以table scan为例)除了需要满足6s这个条件外，进入v$session_longops还需要一个附加条件：至少包含10000个database block。只有同时满足两个条件的table scan操作才会记录到视图中。当然不同的操作类型，条件可能不同。

应用程序也可以通过DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS定义自己的事务进度，写入v$session_longops。

另外需要注意的是v$session_longops反应的不一定是当前的session信息，可能需要结合v$session。v$session_longops中的条目会被自动重用。

问题提出：
在B/S系统中，页面纷繁复杂，而且经过系统长时间运行，如果发现某个session的wait比较严重，进行sql优化时，很难知道这个sql是用在程序的哪个模块，很多时候只能根据开发人员的经验来定位这个sql，或者打开程序代码，搜索。这样的效率很低，这里介绍一个方法来快速定位发生问题的sql 的位置。

ORACLE提供了DBMS_APPLICATION_INFO包，其中提供了set_client_info、set_module、 set_action、read_module、read_client_info、set_session_longops几个过程，其中分别用于设置客户端信息、设置module信息（这里同时必须设置一个action信息）、设置action信息、读取module信息（同时也会得到action信息）、读取client_info信息和手动设置一个会话为longops。
下面来看一个示例：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as SYS

SQL> select distinct sid from v$mystat;

SID
----------
1036

SQL> select module,action,client_info from v$session where sid=1036;

MODULE ACTION CLIENT_INFO
----------------- ---------------------- -------------------------
PL/SQL Developer Command Window - New

可以看到CLIENT_INFO为空。

SQL> exec dbms_application_info.set_client_info('test client info');

PL/SQL procedure successfully completed

SQL> select module,action,client_info from v$session where sid=1036;

MODULE ACTION CLIENT_INFO
----------------- ---------------------- -------------------------
PL/SQL Developer Command Window - New test client info

设置后的CLIENT_INFO

SQL> exec dbms_application_info.set_module(module_name => 'test module',action_name => 'test action');

PL/SQL procedure successfully completed

SQL> select module,action,client_info from v$session where sid=1036;

MODULE ACTION CLIENT_INFO
----------------- ---------------------- -------------------------
test module test action test client info

设置后的MODULE和ACTION

详细的解释请参考oracle的说明，在oracle的package的说明中就有详细的解释。

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS举例:

PROCEDURE SET_SESSION_LONGOPS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RINDEX                         BINARY_INTEGER          IN/OUT
SLNO                           BINARY_INTEGER          IN/OUT
OP_NAME                        VARCHAR2                IN     DEFAULT
TARGET                         BINARY_INTEGER          IN     DEFAULT << usually object id >>
CONTEXT                        BINARY_INTEGER          IN     DEFAULT << any number >>
SOFAR                          NUMBER                  IN     DEFAULT << current rows processed >>
TOTALWORK                      NUMBER                  IN     DEFAULT << total rows need to be processed >>
TARGET_DESC                    VARCHAR2                IN     DEFAULT
UNITS                          VARCHAR2                IN     DEFAULT
1 declare
2     longops_RINDEX binary_integer:=-1;
3     longops_slno   binary_integer:=-1;
4 begin
5     for i in 1..100 loop
6         dbms_application_info.set_session_longops(longops_RINDEX,longops_slno,
7                'ClearBES',sofar=>i,totalwork=>100,
8                target_desc=>'Delete rows',units=>'rows');
9         dbms_lock.sleep(2);
10     end loop;
11* end;
SQL> /
<< waiting .... >>

Check v$session_longops
MESSAGE                                                      ELAPSEDTIME    REMTIME     HVALUE
------------------------------------------------------------ ----------- ---------- ----------
ClearBES: Delete rows : 8 out of 100 rows done                         0          3 1020083367