Author: Frank Bricklen

Page: 1 2

Sometimes it happen that the batch job or long-running queries in your Oracle database takes so much time to complete and you can't help than just keep waiting for it to be completed. In this article we will discuss some great tips to help you efficiently trace Oracle long-running queries.

Detecting Oracle Long Running Queries:

Oracle provides a number of options for detecting oracle long-running queries. Some of these options are discussed as under. We spool results into OracleLongRunningQueries.lst file for review.

V$SESSION_LONGOPS:

Oracle V$SESSION_LONGOPS can be used to display the status of various operations that run for longer than 6 seconds. DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure can be used to add information to this view about application-specific long-running operations.

Below code uses V$SESSION_LONGOPS view to detect Oracle long running queries.

SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
spool OracleLongRunningQueries.lst

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;
/
spool off

Or

SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
spool OracleLongRunningQueries.lst

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE SOFAR != TOTALWORK;
/
spool off

You can apply filter on SID and SERIAL# in the inner query so as to get information about only necessary session.

SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
spool OracleLongRunningQueries.lst

SELECT * FROM
(
  SELECT OPNAME, TARGET, SOFAR, TOTALWORK, UNITS, ELAPSED_SECONDS, MESSAGE
  FROM V$SESSION_LONGOPS
  WHERE SID = <yourSID> AND SERIAL# = <yourSERIAL#>
  ORDER BY START_TIME DESC
)
WHERE ROWNUM <=1;
/
spool off Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Best Setting for Oracle Multiblock Read Count!!
   Oracle Asynchronous COMMIT: Facts and Concerns!
   The Secrets of Handling Tricky Duplicate Data!!
   DBA Tips for Verifying Oracle Data Replication!!