Top 10 SQL Performance Bottlenecks and How to Fix Them – Real Oracle DBA Guide

SQL Performance Bottlenecks in Oracle are every DBA’s daily challenge.
One poorly written query or missing index can cause CPU spikes, I/O slowdowns, or even full table scans that bring the system to its knees.

Whether you manage small databases or multi-terabyte systems, understanding where SQL performance breaks down — and how to tune it — is one of the most important DBA skills.

Let’s explore the top 10 SQL performance bottlenecks and how to fix them with practical, real-world DBA solutions.

---

1. Missing or Inefficient Indexes

Symptom: Queries scanning entire tables instead of using indexes.

Fix:

Check execution plans using:

EXPLAIN PLAN FOR <your SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Create missing indexes:

CREATE INDEX idx_emp_dept ON employees(department_id);

Avoid over-indexing — it slows down DML operations (INSERT, UPDATE, DELETE).

💡 Tip: Use Oracle’s SQL Access Advisor to identify missing or unused indexes.

---

2. Bad Execution Plans Due to Stale Statistics

Symptom: Queries suddenly slow down after updates or deployments.

Fix:

Gather fresh statistics:

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);

Schedule weekly stats jobs for large schemas.

Lock stable stats for static reference tables using:

EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'COUNTRIES');

---

3. Full Table Scans on Large Tables

Symptom: Queries reading millions of rows instead of using indexes.

Fix:

• Ensure WHERE conditions use indexed columns.
• Use partition pruning:

SELECT * FROM sales PARTITION (p_2025) WHERE sale_date > SYSDATE - 30;

• Consider materialized views for precomputed aggregates.

💡 Tip: Enable SQL Plan Baselines to preserve known good execution paths.

---

4. Unoptimized Joins

Symptom: Queries with multiple joins taking too long or consuming excessive PGA.

Fix:

• Ensure join columns are indexed on both tables.
• Use HASH JOIN or MERGE JOIN where appropriate.
• Review join order — Oracle optimizes left-to-right in the FROM clause.

Example:

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

---

5. Inefficient SQL Writing (Too Many Nested Subqueries)

Symptom: Query runs multiple subselects or correlated subqueries.

Fix:

• Replace correlated subqueries with joins or WITH clauses:

WITH dept_avg AS (
  SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id
)
SELECT e.employee_name, e.salary, d.avg_sal
FROM employees e JOIN dept_avg d
ON e.department_id = d.department_id;

• Avoid SELECT * — retrieve only necessary columns.

---

6. Excessive Hard Parsing

Symptom: High CPU usage due to repeated SQL parsing.

Fix:

• Use bind variables instead of literals:

SELECT * FROM employees WHERE department_id = :dept_id;

Check shared pool usage:

SELECT namespace, gets, gethits FROM v$librarycache;

• Enable SESSION_CACHED_CURSORS to reduce parsing overhead.

---

7. Inefficient Parallel Execution

Symptom: Parallel queries cause high I/O waits or PGA exhaustion.

Fix:

• Limit parallel degree per query:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

Adjust parameters:

ALTER SYSTEM SET parallel_max_servers = 64 SCOPE=BOTH;

• Avoid using parallel execution on small datasets.

💡 Tip: Always analyze AWR “Parallel Execution” section before increasing parallelism.

---

8. Poor Join Order or Cardinality Estimates

Symptom: Optimizer chooses wrong join order, causing excessive scans.

Fix:

• Use optimizer hints cautiously:

SELECT /*+ LEADING(e d) */ e.name, d.dept_name ...

• Ensure accurate statistics for join columns.
• Enable OPTIMIZER_DYNAMIC_SAMPLING for better estimates.

---

9. Contention on Hot Blocks or Latches

Symptom: High waits on buffer busy waits or gc buffer busy.

Fix:

• Spread I/O across multiple datafiles.
• Use ASSM (Automatic Segment Space Management).
• Rebuild indexes and reorganize tables to reduce contention.

Check contention:

SELECT event, total_waits, time_waited/100 AS seconds FROM v$system_event
WHERE event LIKE '%buffer busy%';

---

10. Inefficient Temporary Space Usage

Symptom: Sorts or joins spill to TEMP tablespace.

Fix:

• Increase PGA size:

ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

• Resize TEMP tablespace:

ALTER DATABASE TEMPFILE '/u01/oradata/temp01.dbf' RESIZE 10G;

• Review queries performing large sorts — rewrite or index them.

---

Real-World Example

A retail analytics database experienced sudden performance degradation after a schema refresh.
AWR reports showed:

• High hard parse rate
• Excessive full table scans
• Low buffer cache hit ratio

Root Cause: Missing statistics and bind variable misuse.
Fixes Applied:

• Gathered fresh stats (DBMS_STATS.GATHER_SCHEMA_STATS)
• Rewrote dynamic SQL to use binds
• Created missing indexes

Result: Query response time dropped from 12 seconds to 0.3 seconds ⚡

---

Best Practices for Consistent SQL Performance

1. Use bind variables wherever possible.
2. Keep optimizer statistics up to date.
3. Review execution plans regularly.
4. Enable SQL Plan Management in production.
5. Monitor AWR for top SQL by elapsed time.
6. Eliminate unnecessary nested views and subqueries.
7. Rebuild indexes periodically for large OLTP systems.

---

Related Articles

• ORA-04031: Unable to Allocate Shared Memory – Fix Guide
• ORA-04031: Unable to Allocate Shared Memory – Causes, Fixes, and Prevention (Oracle DBA Guide)
• Fixing the “Error in invoking target ‘agent nmhs’ of makefile ins_emagent.mk” During Oracle 11.2.0.4 Installation on Linux

---

Final Thoughts

SQL tuning isn’t about magic — it’s about understanding how Oracle executes queries and ensuring the optimizer has the right data to work with.

By focusing on statistics, indexing, and query design, you can eliminate 90% of performance bottlenecks before they ever reach production.

The best DBAs don’t just tune queries — they build stable, predictable performance environments.