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:
🔍 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
💽 Top SQL by Disk Reads
🧠 Top SQL by CPU Time
📌 What to Look For
High elapsed time SQL
SQL with high executions
SQL with high I/O
👉 AWR is best for:
🚨 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
✅ 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:
⚖️ 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:
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:
Just tell me if you want those ready 👍
