Fixing Slow Queries in Oracle

December 26, 2025

Fixing Slow Queries in Oracle: A Step-by-Step Approach

Slow SQL queries are one of the most common and most critical problems faced by Oracle DBAs. A single poorly performing query can impact application response time, user experience, and even system stability.

Oracle performance tuning is not guesswork. It’s a methodical process of identifying bottlenecks, validating execution paths, and applying targeted fixes.

In this blog, we’ll walk through a step-by-step approach to fixing slow queries in Oracle, using proven DBA techniques.

Step 1: Confirm the Problem (Is the Query Really Slow?)

Before tuning, always validate the issue.

Ask these questions:

Is the query slow now, or was it slow earlier?
Is it slow for all users or only specific sessions?
Is the slowness consistent or intermittent?

Useful Checks

SELECT status, state, seconds_in_wait
FROM v$session
WHERE username = 'APP_USER';

Check:

Active vs waiting sessions
Long-running SQL
Blocking sessions

Step 2: Identify the SQL Causing the Slowness

Once confirmed, identify the exact SQL statement.

From Active Sessions

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_id = '&sql_id';

From AWR / ASH Reports

Look for:

High Elapsed Time
High CPU Time
High Buffer Gets
Frequent executions.

Step 3: Check the Execution Plan (Explain Plan Is Mandatory)

The execution plan tells Oracle how it is fetching data.

Generate Execution Plan

EXPLAIN PLAN FOR
SELECT ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Or for actual execution:

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

Red Flags in Execution Plans

Full Table Scans on large tables
Nested Loops on huge datasets
Cartesian joins
Missing index access paths

Step 4: Verify Index Usage and Strategy

Indexes are critical, but wrong or missing indexes cause slow queries.

Check Existing Indexes

SELECT index_name, column_name
FROM dba_ind_columns
WHERE table_name = 'EMPLOYEES';

Common Index Issues

Index exists but not used
Index on low-cardinality columns
Function used on indexed column:
WHERE UPPER(name) = 'SCOTT';

💡 Solution:

Use function-based indexes
Rewrite SQL to avoid functions on columns

Step 5: Check Statistics (Very Common Root Cause)

Oracle relies heavily on optimizer statistics. Stale or missing stats lead to bad plans.

Check Last Analyzed

SELECT table_name, last_analyzed
FROM dba_tables
WHERE table_name = 'ORDERS';

Gather Fresh Statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA',
  tabname => 'ORDERS',
  cascade => TRUE
);

Step 6: Analyze Bind Variables & Hard Parsing

Lack of bind variables causes:

Excessive hard parsing
High CPU usage
Library cache contention

Bad Example

SELECT * FROM emp WHERE empno = 7369;
SELECT * FROM emp WHERE empno = 7499;

Good Example

Check parsing:

SELECT parse_calls, executions
FROM v$sql
WHERE sql_id = '&sql_id';

Step 7: Check Wait Events (Database Is Waiting for What?)

If SQL is slow, Oracle is waiting on something.

Identify Wait Events

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

Common Wait Events

Wait Event	Meaning
db file sequential read	Index I/O
db file scattered read	Full table scan
log file sync	Commit issues
buffer busy waits	Hot blocks

Step 8: Review SQL Logic & Rewrite If Needed

Sometimes the issue is not Oracle — it’s bad SQL design.

Common SQL Mistakes

Using SELECT *
Unnecessary joins
Correlated subqueries
Using IN instead of EXISTS (in some cases)

Example Rewrite

-- Bad
SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM payments);

-- Better
SELECT o.*
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p WHERE p.order_id = o.order_id
);

Step 9: Validate Fixes in Lower Environment First

Never apply changes directly in production without testing.

Validate:

Execution plan improvement
Reduced elapsed time
No functional impact

Use:

SQL Monitor
AWR comparison
Application testing

Step 10: Monitor After Fix (Very Important)

After tuning:

Monitor performance for next few days
Check for plan regression
Use SQL Plan Baselines if required

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

Final Thoughts

Fixing slow queries in Oracle is about discipline and process, not shortcuts.

Key Takeaways

Identify the real problem
Analyze execution plans
Use correct indexes & statistics
Optimize SQL logic
Monitor continuously

When done correctly, performance tuning leads to:

Faster applications
Stable production systems
Happy users 😄

Explore more with Learnomate Technologies!

Want to see how we teach?
Head over to our YouTube channel for insights, tutorials, and tech breakdowns:
www.youtube.com/@learnomate

To know more about our courses, offerings, and team:
Visit our official website:
www.learnomate.org

Interested in mastering Oracle Database Administration?
Check out our comprehensive Oracle DBA Training program here:
https://learnomate.org/oracle-dba-training/

Want to explore more tech topics?
Check out our detailed blog posts here:
https://learnomate.org/blogs/

And hey, I’d love to stay connected with you personally!
Let’s connect on LinkedIn: Ankush Thavali

Happy learning!

Ankush😎

Search This Blog

Oracle DBA