“Slow SQL Queries → Slow Application Response”
🚀 But why are they slow and how to fix them?
Part 1 of this series showed you how to
identify slow queries in “SQL Ordered by
Elapsed Time & Executions” tables in AWR
Report.
This Part 2 shows you reasons for slow
queries and options to optimize them.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 1
Reasons of Slow Queries ❓
A Query can be slow for many reasons
●AWR highlights the most expensive SQLs across
Elapsed Time, Executions, CPU, I/O, Locks, and
Parsing, Waits, etc.
●Each section focuses on a different root cause.
●They also need to be correlated for a complete
analysis.
Learn to identify, prioritize, and fix the slow queries
now!
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 2
SQL Ordered by Elapsed Time
SQL Ordered by Executions
Importance: These SQLs take the most wall-clock time or
execute frequently.
How to Identify:
● High Elapsed time ➔ expensive per execution.
● High Executions ➔ individually cheap but system
overload.
How to Fix:
● Optimize Execution Plans (indexes, access paths).
● Reduce execution frequency (batching, caching in app
layer).
● Rewrite inefficient queries.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 3
SQL Ordered by User I/O Wait Time
SQL Ordered by Physical Reads (Unoptimized)
Importance: These SQLs read from disks, sometimes bypassing
Cache
How to Identify:
● High User I/O waits ➔ disk bottleneck.
● High Physical Reads ➔ inefficient caching or access.
How to Fix:
● Optimize Joins & WHERE clauses to drive fewer blocks.
● Create/adjust indexes for better filtering.
● Increase buffer cache size to reduce physical reads.
● Move high I/O objects to faster storage (For e.g. flash).
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 4
SQL Ordered by CPU Time
Importance: These queries consume High CPU
How to Identify:
● Focus on SQLs with the highest CPU per Exec (s).
● High CPU Time means excessive computation, inefficient joins,
or bad plans.
Tip: SQLs with the highest CPU Time (s) impact overall database
CPU usage, but may not always cause individual query slowness.
How to Fix:
● Tune SQL execution plans to reduce processing steps (better
indexes, avoid unnecessary computations).
● Rewrite complex expressions in SELECT, WHERE, or JOIN
clauses to be more efficient.
● Optimize functions or computations pushed into the
database (e.g., avoid function-based filtering without indexes).
● Partition or parallelize expensive queries carefully if needed.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 5
SQL Ordered by Gets
SQL Ordered by Reads
Importance: These Queries cause Logical reads (Gets) or
Physical & Logical reads (Reads)
How to Identify:
● High Gets ➔ logical read pressure (memory).
● High Reads ➔ combined memory + disk load.
How to Fix:
● Rewrite queries to access fewer rows/blocks.
● Tune join conditions and eliminate unnecessary full scans.
● Add selective indexes or materialized views.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 6
Top SQL with Top Events
Top SQL with Top Row Sources
Importance: These queries dominate wait events.
How to Identify:
● Look for SQLs tied to waits like "db file sequential read",
"buffer busy waits" etc.
● “Top Row Source: TABLE ACCESS - FULL” means full table
scan (without using an index)
How to Fix:
● Sequential read ➔ optimize single block reads (index tuning).
● Buffer busy waits ➔ spread out access patterns (e.g.,
partitioning, freelists).
● Enqueue waits ➔ fix locking/blocking problems.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 7
SQL ordered by Cluster Wait Time
This indicates RAC interconnect overhead. Results of SQL are
coming from another (RAC) instance of the database.
Importance: RAC interconnect causes waits like ‘gc current
block busy’, ‘gc buffer busy’, ‘gc cr block lost’, etc.
Another reason could be network packet loss.
How to Identify:
● High Cluster Wait Time ➔ global cache contention.
How to Fix:
● Partition data to minimize cross-instance block requests.
● Instance affinity: Keep related users/sessions sticking to one
instance.
● Tune hot objects (reorg indexes, split tablespaces if needed).
● Ensure low-latency, reliable interconnect (correct VLANs,
jumbo frames, QoS settings).
● Fix bad SQL that causes too many full table scans across
nodes.
Note: Some Cluster Waits are acceptable under RAC.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 8
SQL Ordered by Parse Calls
Importance: It indicates underlying design or coding issues
and impacts SQL Query and the overall database
performance.
How to Identify:
If Parse Calls > Executions ➔ Excessive parsing (Hard
& Soft both).
Next Step / Fix:
●Use bind variables instead of literals.
●Check Child cursors.
●Check Cursor Sharing Info.
●Check Shared Pool Health.
●Check application logic of closing statements after
each call.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 9
SQL Ordered by Version Count
Importance: Too many versions = Shared Pool bloat
➔ slower parsing & degraded performance under load.
How to Identify:
●High Version Count ➔ multiple child cursors for
same SQL.
How to Fix:
●Check CURSOR_SHARING settings and
session_cached_cursors.
●Use bind variables to reduce literal-based cursor
flooding.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 10
📢 Important Note Before Tuning!
●Always consult Senior DBA for major changes
(For e.g. cache tuning, partitioning, RAC
optimization, cursor settings).
●Perform thorough impact analysis and testing in
a lower environment before production rollout.
●Every database is unique — no one-size-fits-all
tuning!
🚀 Tune smart, tune safe.
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 11
💡 Final DBA Tuning Tips
● Cross-reference multiple AWR sections for full context. For
e.g. “Top 10 Foreground Events by Total Wait Time” & “Wait
Classes by Total Wait Time”
● Always review Execution Plans before tuning.
● Tune for impact, not just for beauty — focus on top consumers
first!
● Track improvements with before-and-after AWRs.
● Gather fresh optimizer statistics (a.k.a Gather Stats) if
tables/indexes have changed — stale stats can mislead the
optimizer.
● Rebuild fragmented indexes if necessary to maintain efficient
access paths.
● Remember: SQL Id changes if the query text changes —
cross-verify if the new SQL Id belongs to the same functionality!
Bonus Tip: ADDM report often points to top SQL, configuration
issues, or high resource users
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 12
Want to Master Query Tuning Like a Pro DBA?
● 🙌 Feeling more Confident with AWR Now? Let
Me Know!
● Follow me for deep-dive performance content!
🔥
● DM me if you want help building a faster, scalable
database!
https://www.linkedin.com/in/sandeep-pawar21/ | Performance Architect 13
