🚀How to Identify and Troubleshoot Slow Queries in Oracle Database 19c

Performance issues are one of the most common challenges faced by database administrators. When users report that “the system is slow”, the root cause is often inefficient SQL queries.

In this guide, we’ll walk through a practical, step-by-step approach to identifying slow queries in Oracle 19c, along with key SQL scripts every DBA should know.

🔍 Why Finding Slow Queries Matters

Slow queries can lead to:

  • 🚫 Poor user experience
  • 💸 Increased infrastructure costs
  • 🔥 High CPU and I/O usage
  • ⛔ System-wide performance degradation

The good news? Oracle provides powerful internal views to diagnose these issues quickly.

🧠 Step 1: Identify Top SQL by Total Execution Time

Start by finding queries consuming the most time overall:

SELECT *
FROM (
    SELECT sql_id,
           parsing_schema_name,
           executions,
           elapsed_time/1000000 AS elapsed_seconds,
           cpu_time/1000000 AS cpu_seconds,
           buffer_gets,
           disk_reads,
           rows_processed,
           SUBSTR(sql_text,1,1000) AS sql_text
    FROM v$sql
    WHERE executions > 0
    ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 20;

👉 This helps you identify:

  • High-impact queries
  • Frequently executed heavy SQL
  • Resource-intensive workloads
⚡ Step 2: Find Queries with High Average Execution Time

Some queries may not run often but are very slow per execution:

SELECT *
FROM (
    SELECT sql_id,
           parsing_schema_name,
           executions,
           ROUND(elapsed_time/1000000,2) AS total_elapsed_sec,
           ROUND((elapsed_time/DECODE(executions,0,1,executions))/1000000,2) AS avg_elapsed_sec,
           ROUND(cpu_time/1000000,2) AS total_cpu_sec,
           buffer_gets,
           disk_reads,
           SUBSTR(sql_text,1,1000) AS sql_text
    FROM v$sql
    WHERE executions > 0
    ORDER BY avg_elapsed_sec DESC
)
WHERE ROWNUM <= 20;

👉 This is one of the most important queries for real-world troubleshooting.

🟢 Step 3: Check Currently Running Slow Queries

If users are experiencing slowness right now:

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

👉 This shows:

  • Active sessions
  • Running SQL statements
  • Wait events causing delays
📊 Step 4: Use AWR to Identify Historical Slow Queries

If the issue occurred earlier, use AWR (Automatic Workload Repository):

SELECT *
FROM (
    SELECT sql_id,
           plan_hash_value,
           executions_delta,
           elapsed_time_delta/1000000 AS elapsed_sec,
           cpu_time_delta/1000000 AS cpu_sec,
           buffer_gets_delta,
           disk_reads_delta
    FROM dba_hist_sqlstat
    ORDER BY elapsed_time_delta DESC
)
WHERE ROWNUM <= 20;

Retrieve SQL text:

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE sql_id = 'your_sql_id';

👉 AWR is essential for:

  • Historical analysis
  • Trend identification
  • Performance comparison
🔎 Step 5: Analyze the Execution Plan

Once you identify a slow query, inspect how Oracle executes it:

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALLSTATS LAST'));

👉 Look for:

  • Full table scans on large tables
  • High buffer gets
  • Inefficient join methods
  • Missing indexes
  • Incorrect cardinality estimates
💽 Step 6: Identify I/O Heavy Queries 
SELECT *
FROM (
    SELECT sql_id,
           executions,
           disk_reads,
           buffer_gets,
           elapsed_time/1000000 AS elapsed_sec,
           SUBSTR(sql_text,1,1000) AS sql_text
    FROM v$sql
    WHERE executions > 0
    ORDER BY disk_reads DESC
)
WHERE ROWNUM <= 20;

👉 High disk reads often indicate:

  • Missing indexes
  • Full table scans
  • Poor query design
🔥 Step 7: Identify CPU Intensive Queries 
SELECT *
FROM (
    SELECT sql_id,
           executions,
           cpu_time/1000000 AS cpu_sec,
           elapsed_time/1000000 AS elapsed_sec,
           buffer_gets,
           SUBSTR(sql_text,1,1000) AS sql_text
    FROM v$sql
    WHERE executions > 0
    ORDER BY cpu_time DESC
)
WHERE ROWNUM <= 20;

👉 High CPU usage usually means:

  • Inefficient logic
  • Poor joins
  • Excessive data processing
🧠 Practical DBA Workflow

When facing performance issues, follow this sequence:

  1. Check active sessions (v$session)
  2. Identify top SQL (v$sql)
  3. Sort by average execution time
  4. Extract sql_id
  5. Analyze execution plan
  6. Use AWR for historical insight
🚨 Common Root Causes of Slow Queries

In real-world environments, slow queries are typically caused by:

  • ❌ Missing or incorrect indexes
  • ❌ Outdated statistics
  • ❌ Poor execution plans
  • ❌ Excessive full table scans
  • ❌ Locking and blocking
  • ❌ High I/O latency
  • ❌ Inefficient application queries
🛠️ Quick Fixes

Depending on your findings:

  • Gather fresh statistics
  • Add or rebuild indexes
  • Rewrite inefficient SQL
  • Tune joins and filters
  • Optimize application logic
  • Resolve locking issues
🎯 Final Thoughts

Finding slow queries is not just about running SQL scripts — it’s about understanding:

  • Workload patterns
  • Query behavior
  • System resource usage

By combining dynamic performance views, execution plans, and AWR analysis, you can quickly pinpoint and resolve performance bottlenecks in Oracle 19c.