Identifying Long Running Queries in Oracle Databases

When working with Oracle databases, it’s not uncommon to encounter performance issues due to long-running queries. These queries can slow down your application or even bring it to a halt. To address this problem, you need to identify which queries are consuming the most resources and investigate them further. In this tutorial, we will explore how to check for long running queries in an Oracle database.

Understanding the Problem

Before diving into the solution, let’s understand why identifying long-running queries is crucial. These queries can be a sign of inefficient SQL code, inadequate indexing, or even issues with the database configuration. By pinpointing these queries, you can take corrective measures to optimize them and improve overall database performance.

Using Oracle Dynamic Performance Views

Oracle provides dynamic performance views that offer insights into the current state of the database. These views are crucial for monitoring and troubleshooting purposes. The key views we will focus on include:

V$SESSION: Provides information about all active sessions.
V$SQLTEXT_WITH_NEWLINES: Contains the text of SQL statements, including newlines.
V$LOCKED_OBJECT and V$LOCK: Help in identifying locks that might be causing delays.
V$SESSION_LONGOPS: Lists long-running operations.

Identifying Active Queries

To find currently active queries, you can use a query like the following:

SELECT S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
FROM v$sqltext_with_newlines t, V$SESSION s
WHERE t.address = s.sql_address
AND t.hash_value = s.sql_hash_value
AND s.status = 'ACTIVE'
AND s.username <> 'SYSTEM'
ORDER BY s.sid, t.piece;

This query joins V$SQLTEXT_WITH_NEWLINES and V$SESSION to get the text of active SQL statements along with session details.

Finding Long-Running Operations

For operations that have been running for a considerable amount of time (like full table scans), you can use:

COLUMN percent FORMAT 999.99 

SELECT sid, TO_CHAR(start_time,'hh24:mi:ss') stime, 
       message, (sofar/totalwork)*100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1;

This query shows the progress of long-running operations.

Identifying Queries Running for More Than 60 Seconds

To find queries that have been running for more than a minute, you can use:

SELECT s.username, s.sid, s.serial#, s.last_call_et/60 mins_running, q.sql_text 
FROM v$session s 
JOIN v$sqltext_with_newlines q
ON s.sql_address = q.address
WHERE status='ACTIVE'
AND type <> 'BACKGROUND'
AND last_call_et > 60
ORDER BY sid, serial#, q.piece;

This query filters sessions based on their last call time and joins with V$SQLTEXT_WITH_NEWLINES to get the SQL text.

Best Practices for Monitoring Long-Running Queries

Regularly monitor your database for long-running queries.
Use Oracle’s built-in views like V$SESSION_LONGOPS for quick insights into ongoing operations.
Analyze locks and waits to understand bottlenecks.
Optimize SQL statements and indexing based on findings.

Conclusion

Identifying long-running queries is a crucial step in maintaining the health and performance of your Oracle database. By leveraging dynamic performance views like V$SESSION, V$SQLTEXT_WITH_NEWLINES, and V$SESSION_LONGOPS, you can pinpoint resource-intensive operations and take corrective actions to optimize them. Regular monitoring, coupled with best practices for query optimization, will help in ensuring that your database runs smoothly and efficiently.