Oracle长事务诊断-优快云博客

col opname format a32
col target_desc format a32
col perwork format a12
set lines 131
select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork
from v$session_longops where sofar!=totalwork;

select inst_id,
       OPNAME,
       TARGET,
       elapsed_seconds,
       trunc(sofar / totalwork * 100, 2) || '%' as perwork,
       (select sql_id
          from gv$session t
         where t.sid = sl.SID
           and status = 'ACTIVE'
           and sql_id is not null) as sql_id,
       (select sql_fulltext
          from gv$sqlarea t
         where t.sql_id = (select sql_id
                             from gv$session t
                            where t.sid = sl.SID
                              and status = 'ACTIVE'
                              and sql_id is not null)) as sql_fulltext,
       message,
       start_time,
       last_update_time,
       time_remaining
from GV$SESSION_LONGOPS sl
where ELAPSED_SECONDS > 0
   and sofar != totalwork
   and totalwork != 0
order by ELAPSED_SECONDS desc;

附V$seesion_longops视图中记录了长时间（运行时间超过6秒的）运行的事物，一般很多情况是全表扫描操作。
V$SESSION_LONGOPS列说明
SID：Session标识
SERIAL#：Session串号
OPNAME：操作简要说明
TARGET：操作运行所在的对象
TARGET_DESC：目标对象说明
SOFAR：至今为止完成的工作量
TOTALWORK：总工作量
UNITS：工作量单位
START_TIME：操作开始时间
LAST_UPDATE_TIME：统计项最后更新时间
TIME_REMAINING：预计完成操作的剩余时间(秒)
ELAPSED_SECONDS：从操作开始总花费时间(秒)
CONTEXT：前后关系
MESSAGE：统计项的完整描述
USERNAME：执行操作的用户ID
SQL_ADDRESS：用于连接查询的列
SQL_HASH_VALUE：用于连接查询的列
本系统查询如下：
1.查询v$seesion_longops视图，发现23点时有个session执行时间为27秒
select sid,serial#,target ,last_update_time,SQL_HASH_VALUE from v$session_longops order by last_update_time;
SID SERIAL# TARGET LAST_UPDATE_TIME SQL_HASH_VALUE
-------------------------------------------------------------------
39 2038 ITSM.ITSM_INCIDENT 2011/10/26 23:30 1640565597
2.通过v$sqltext视图查出该sql语句
SQL> select sql_text from v$sqltext where hash_value='1640565597' order by piece;
SQL_TEXT
----------------------------------------------------------------
SELECT /*+NESTED_TABLE_GET_REFS+*/ "ITSM"."ITSM_INCIDENT".* FROM
"ITSM"."ITSM_INCIDENT"
3.查metalink:
This hint specifies that nested tables should be retrieved as refs.It is used for exporting, importing and loading. It should never be issued byuser queries, and in fact is unlikely to make a difference if tried.
This is also consistent with seeing large amounts of queries with this hint, and that those queries are not reused. If this is a problem for you, try to export at another time of day. If the exports are really long-running or the system load is constant, try to find out what you are using exports for and suggest alternatives (eg. backup and recovery purposes, etc). If this is a DSS system, you probably have a smaller shared pool and use import/export heavily. If so, it might be a good idea to use a bigger shared pool when doing this activity.
该hint用于expimp的嵌套表，想起该库每天晚上23点半要自动exp备份。