V$seesion_longops 视图 中记录了长时间（运行时间超过 6 秒的）运行的事物，一般很多情况是全表扫描操作。 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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：执行操作的用户IDSQL_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 by-LO(r[:XCO^s0user queries, and in fact is unlikely to make a difference if tried.‑mE)i#c A,y5BW0ITPUB 个人空间 2KzT] F?[1]Ij[:I t 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备份。
©著作权归作者所有：来自51CTO博客作者kankanhoney的原创作品，如需转载，请注明出处，否则将追究法律责任
0
收藏