• 25 Dec, 2025
• 0 Comments
• 3 Mins Read

Fixing Slow Queries in Oracle

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

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😎