How to Identify Slow SQL Queries in Oracle (Practical Guide for Performance Tuning)

 

How to Identify Slow SQL Queries in Oracle (Practical Guide for Performance Tuning)

Performance tuning is one of the most valuable skills when working with Oracle Database. Slow SQL queries can degrade application performance, increase CPU usage, and frustrate users.

In this guide, we’ll cover how to identify slow SQL using:

• V$SQL

• V$SQLAREA

• AWR Reports

• Simple and effective tuning ideas

---

🔍 1. Using V$SQL to Find Slow Queries

The V$SQL view contains detailed statistics about SQL statements currently in the shared pool.

---

✅ Find Top Slow Queries by Elapsed Time

SELECT 
    sql_id,
    executions,
    elapsed_time/1000000 AS elapsed_sec,
    cpu_time/1000000 AS cpu_sec,
    disk_reads,
    buffer_gets,
    sql_text
FROM 
    v$sql
ORDER BY 
    elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

---

📌 Key Columns Explained

• elapsed_time → total execution time

• cpu_time → CPU usage

• disk_reads → physical I/O (slow operations)

• buffer_gets → logical reads

• executions → how many times query ran

👉 Focus on:

• High elapsed time

• High disk reads

• High buffer gets

---

🎯 Find Slow Queries Per Execution

SELECT 
    sql_id,
    executions,
    elapsed_time / DECODE(executions,0,1,executions) / 1000000 AS avg_time_sec,
    sql_text
FROM 
    v$sql
ORDER BY 
    avg_time_sec DESC;

👉 This helps identify queries that are slow individually (not just frequent ones).

---

📊 2. Using V$SQLAREA (Aggregated View)

V$SQLAREA provides aggregated statistics for SQL statements.

---

✅ Top Queries by Resource Usage

SELECT 
    sql_id,
    executions,
    buffer_gets,
    disk_reads,
    elapsed_time/1000000 AS elapsed_sec,
    sql_text
FROM 
    v$sqlarea
ORDER BY 
    buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

---

📌 When to Use V$SQLAREA

• To identify heavy queries overall

• When multiple executions are involved

• For workload-level analysis

---

📈 3. Using AWR Reports (Advanced Method)

🔍 What is AWR?

AWR (Automatic Workload Repository) stores historical performance data in Oracle Database.

---

🛠️ Generate AWR Report

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

---

📊 Key Sections in AWR Report

🔥 Top SQL by Elapsed Time

• Shows queries consuming most time

💽 Top SQL by Disk Reads

• Identifies I/O-heavy queries

🧠 Top SQL by CPU Time

• CPU-intensive SQL

---

📌 What to Look For

• High elapsed time SQL

• SQL with high executions

• SQL with high I/O

👉 AWR is best for:

• Historical analysis

• Production troubleshooting

• Identifying trends

---

🚨 4. Common Signs of Slow SQL

• High response time

• High CPU usage

• Excessive disk reads

• Full table scans

• Long-running sessions

---

🛠️ 5. Simple SQL Tuning Ideas

---

✅ 1. Use Proper Indexes

CREATE INDEX idx_emp_name ON emp(name);

👉 Helps avoid full table scans

---

✅ 2. Avoid SELECT *

-- Bad
SELECT * FROM employees;

-- Good
SELECT id, name FROM employees;

👉 Reduces unnecessary data retrieval

---

✅ 3. Use Bind Variables

SELECT * FROM emp WHERE emp_id = :id;

👉 Improves parsing efficiency

---

✅ 4. Check Execution Plan

EXPLAIN PLAN FOR 
SELECT * FROM emp WHERE id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

👉 Understand how Oracle executes query

---

✅ 5. Reduce Full Table Scans

• Add indexes

• Use selective conditions

---

✅ 6. Optimize Joins

• Use proper join conditions

• Avoid Cartesian joins

---

✅ 7. Gather Statistics

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

👉 Helps optimizer choose better plans

---

🧪 6. Real-World Example

Problem Query

SELECT * FROM orders WHERE customer_id = 101;

Issue

• No index on customer_id

• Full table scan

---

Solution

CREATE INDEX idx_orders_cust ON orders(customer_id);

👉 Result:

• Query becomes significantly faster

---

⚖️ V$SQL vs V$SQLAREA vs AWR

Feature	V$SQL	V$SQLAREA	AWR
Data Type	Real-time	Aggregated	Historical
Scope	Individual SQL	Combined SQL	System-wide
Use Case	Immediate tuning	Workload analysis	Deep performance analysis

---

📌 Conclusion

Identifying slow SQL in Oracle Database involves a combination of:

• Real-time views (V$SQL, V$SQLAREA)

• Historical analysis (AWR Reports)

Once identified, applying simple tuning techniques like indexing, query optimization, and statistics gathering can dramatically improve performance.

---

🚀 Next Topics You Can Cover

Since you're building performance-focused content, you can also write about:

• SQL Execution Plans deep dive

• Index types in Oracle

• Wait events and session tuning

• ASH vs AWR

Just tell me if you want those ready 👍