Why Your Oracle SQL Is Slow and How to Fix It: A Practical DBA Guide to SQL Tuning in Oracle 26ai

I have been working with Oracle environments for several years, and the same situation repeats in almost every organization: someone reports that “the system is slow,” and the database is immediately blamed.

What happens next determines whether the issue is resolved in hours or continues for weeks.

SQL tuning is not guesswork. It is a structured, measurable process of diagnosing and fixing performance problems using real data.

This article explains the Oracle SQL tuning framework and demonstrates it using a real production scenario.

What SQL Tuning Actually Is

Oracle defines SQL tuning as an iterative process of improving SQL performance to meet measurable goals.

Two concepts are critical:

• Iterative → tuning is continuous

• Measurable → success must be defined

A query that runs fast today can become slow tomorrow due to:

• Data growth

• Stale statistics

• Execution plan changes

The Two Tuning Goals

1. Reduce Response Time

Important for OLTP systems where users expect fast responses.

2. Improve Throughput

Important for batch workloads where resource efficiency matters.

The 6-Step SQL Tuning Framework

1. Identify high-load SQL

2. Gather performance data

3. Determine root cause

4. Define scope

5. Apply fix

6. Prevent regression

🔥 Real Production Case: Slow Query Investigation and Fix

Problem

In a production system:

• Query execution time: 10+ minutes

• High CPU usage

• Other sessions slowed down

SQL:

SELECT customer_id, SUM(amount) AS total_amount
FROM   orders
WHERE  order_date = DATE '2025-01-01'
GROUP  BY customer_id;

Step 1: Identify High-Load SQL

SELECT sql_id,
       executions,
       elapsed_time,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1e6, 2) AS avg_secs,
       buffer_gets,
       disk_reads
FROM   v$sql
WHERE  executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

👉 This safely filters invalid execution counts and identifies top SQL.

Step 2: Analyze Execution Plan

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id => 'your_sql_id',
    format => 'ALLSTATS LAST'
  )
);

Observation

• Full table scan on ORDERS

• High buffer gets

• Large mismatch between estimated vs actual rows
  

👉 Indicates optimizer misestimation.

Step 3: Check Statistics

SELECT table_name,
       last_analyzed,
       stale_stats
FROM   dba_tab_statistics
WHERE  owner = 'SCOTT'
AND    table_name = 'ORDERS';

👉 Added owner filter (important in real systems).

Step 4: Check Existing Indexes

SELECT index_name
FROM   dba_indexes
WHERE  owner = 'SCOTT'
AND    table_name = 'ORDERS';

👉 Ensures accurate schema-level validation.

Step 5: Apply Fix

Gather statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'ORDERS'
);

Create index

CREATE INDEX orders_idx1 
ON SCOTT.orders(order_date);

Step 6: Validate Improvement

After applying fixes:

• Query time reduced from 10 minutes → 2 seconds

• Buffer gets reduced significantly

• Execution plan changed to INDEX RANGE SCAN

Root Cause

The issue was caused by:

• Missing index on filter column

• Stale optimizer statistics

Example: Function Preventing Index Usage

Problem

SELECT *
FROM   orders
WHERE  TO_CHAR(order_date, 'YYYY') = '2025';

Solution

SELECT *
FROM   orders
WHERE  order_date >= DATE '2025-01-01'
AND    order_date <  DATE '2026-01-01';

👉 Range condition allows index usage.

SQL Tuning Tools

Execution Plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

AWR (Top SQL)

SELECT sql_id,
       ROUND(cpu_time/1e6,2) AS cpu_secs,
       ROUND(elapsed_time/1e6,2) AS elapsed_secs
FROM   v$sql
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

SQL Monitoring

SELECT sql_id,
       status,
       elapsed_time/1e6 AS elapsed_secs
FROM   v$sql_monitor
WHERE  status = 'EXECUTING';

SQL Trace

ALTER SESSION SET EVENTS 
'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Best Practices

• Always analyze execution plan first

• Keep statistics up to date

• Use indexes carefully

• Avoid functions on indexed columns

• Use bind variables

• Monitor high-load SQL regularly

Conclusion

Slow SQL is not random it is diagnosable and fixable.

By following a structured approach:

• Identify the real problem

• Analyze execution plan

• Fix the root cause
  

You can transform query performance from minutes to seconds and improve overall system stability.