Efficiently Tracking Oracle Long-Running Queries

Efficiently Tracking Oracle Long-Running Queries!!
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 = AND SERIAL# = ORDER BY START_TIME DESC ) WHERE ROWNUM <=1; / spool off
V_$SQLAREA:
You can use below query to find the top five SQL statements with the highest user I/O waits
SET PAGESIZE 80 SET LINESIZE 100 COL SQL_TEXT WORD_WRAP spool OracleLongRunningQueries.lst SELECT * FROM ( SELECT SQL_TEXT, SQL_ID, ELAPSED_TIME, CPU_TIME, USER_IO_WAIT_TIME FROM SYS.V_$SQLAREA ORDER BY 5 DESC ) WHERE ROWNUM < 6; / spool off
Similarly you can use below code to find top SQL statements ordered in a descending order based on the addition of the buffer gets and disk reads.
SET PAGESIZE 80 SET LINESIZE 100 COL SQL_TEXT WORD_WRAP spool OracleLongRunningQueries.lstSELECT HASH_VALUE, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, EXECUTIONS, SORTS,ADDRESS, A.SQL_TEXT FROM V$SQLAREA WHERE BUFFER_GETS > 10000000 OR DISK_READS > 1000000 ORDER BY BUFFER_GETS + 100*DISK_READS DESC / spool off
DBA_JOBS and DBA_JOBS_RUNNING:
Oracle DBA_JOBS lists all jobs in the database whereas DBA_JOBS_RUNNING lists all jobs currently running in the instance. You can use below code to get a detailed information on Oracle long running jobs.
SET PAGESIZE 80 SET LINESIZE 100 COL SQL_TEXT WORD_WRAP spool OracleLongRunningQueries.lst SELECT DJ2.SID, DJ1.LOG_USER, DJ1.JOB, DJ1.BROKEN, DJ1.FAILURES, DJ1.LAST_DATE, DJ1.LAST_SEC, DJ1.THIS_DATE, DJ1.THIS_SEC, DJ1.NEXT_DATE, DJ1.NEXT_SEC, DJ1.INTERVAL, DJ1.WHAT FROM DBA_JOBS DJ1, DBA_JOBS_RUNNING DJ2 WHERE DJ1.JOB = DJ2.JOB; / spool off
_SQLEXEC_PROGRESSION_COST:
Oracle _SQLEXEC_PROGRESSION_COST is an internal Oracle parameter that controls the optimizer cost used as the threshold for cutting off progression monitoring. Oracle _SQLEXEC_PROGRESSION_COST is used to avoid the overhead for all SQL's because progression monitoring involves extra function calls and row sources. By default the value of Oracle _SQLEXEC_PROGRESSION_COST is set to 1000.
Setting the value of _SQLEXEC_PROGRESSION_COST to 0 will turn off progression monitoring and setting it to nonzero will cause any statement with costs less than the value not to be monitored. In some instances this parameter is turned off to avoid or workaround certain bugs. But the side effect of setting it to zero is that the V$SESSION_LONGOPS view is not populated by long running queries.
Remember Oracle _SQLEXEC_PROGRESSION_COST parameter is an internal Oracle parameter and should never be used unless instructed by Oracle Support.