Master in Oracle Golden Gate | Join us for the demo session on 22nd November 2025 at 6:00 PM IST

Top 25 Oracle Performance Tuning Queries Every DBA Must Know

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarPradip
  • 24 Nov, 2025
  • 0 Comments
  • 4 Mins Read

Top 25 Oracle Performance Tuning Queries Every DBA Must Know

Top 25 Oracle Performance Tuning Queries Every DBA Must Know (With Examples)

Performance tuning is one of the most critical responsibilities for any Oracle DBA. Whether you’re diagnosing slow SQL statements, checking resource usage, or validating execution plans, having the right set of queries makes troubleshooting faster and more efficient.

In this blog, we’ll explore the most essential Oracle performance tuning queries, along with what they reveal and when to use them. These queries help DBAs quickly assess database health, diagnose bottlenecks, and improve overall performance.

Why Performance Tuning Matters

A well-tuned Oracle database:

  • Reduces CPU & memory consumption

  • Improves application response time

  • Prevents locking & blocking issues

  • Ensures better user experience

  • Helps in capacity planning

Top Oracle Performance Tuning Queries

Check Database Wait Events

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

Use: Identifies what the database is waiting on.

Find Top SQL by CPU Usage

SELECT sql_id, cpu_time, elapsed_time, executions
FROM v$sql
ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;

Identify Long-Running Queries

SELECT sid, serial#, opname, start_time, sofar, totalwork
FROM v$session_longops
WHERE totalwork > 0;

Check Current Active Sessions

SELECT sid, serial#, status, username, event
FROM v$session
WHERE status = 'ACTIVE';

AWR: Top SQL in Current Snapshot

SELECT sql_id, executions_delta, buffer_gets_delta
FROM dba_hist_sqlstat
ORDER BY buffer_gets_delta DESC;

Find SQL Using Full Table Scan

SELECT *
FROM v$sql
WHERE upper(sql_fulltext) LIKE '%SELECT%'
AND (command_type = 3);

Check Blocking Sessions

SELECT blocking_session, sid, serial#, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

Check Table Hot Blocks

SELECT segment_name, COUNT(*) hot_blocks
FROM v$bh
GROUP BY segment_name
ORDER BY hot_blocks DESC;

Check Buffer Cache Hit Ratio

SELECT (1 - (phy.value / (lob.value + con.value))) * 100 AS hit_ratio
FROM v$sysstat phy, v$sysstat lob, v$sysstat con
WHERE phy.name='physical reads'
AND lob.name='session logical reads'
AND con.name='consistent gets';

Check Library Cache Hit Ratio

SELECT namespace, gethits, gets,
(1 - (gets - gethits) / gets) * 100 AS hit_ratio
FROM v$librarycache;

Top Segments by Logical Reads

SELECT owner, object_name, logical_reads
FROM v$segment_statistics
WHERE statistic_name = 'logical reads'
ORDER BY logical_reads DESC;

Table Fragmentation Check

SELECT table_name, blocks, empty_blocks
FROM dba_tables
WHERE owner='SCOTT';

Find Sessions Using High Temp

SELECT s.sid, s.serial#, t.blocks*8/1024 MB
FROM v$sort_usage t, v$session s
WHERE t.session_addr = s.saddr;

Check PGA Usage

SELECT name, value
FROM v$pgastat;

Check SGA Usage

SELECT name, bytes/1024/1024 MB
FROM v$sgastat
ORDER BY bytes DESC;

Find I/O Intensive SQL

SELECT sql_id, disk_reads, executions
FROM v$sql
ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

Execution Plan for Any SQL

SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Identify High CPU Sessions

SELECT sid, serial#, value cpu_usage
FROM v$sesstat
WHERE statistic# = 12
ORDER BY value DESC;

Check Top Wait Events for a Session

SELECT event, total_waits, time_waited
FROM v$session_event
WHERE sid = &sid;

Find SQL With High Parsing

SELECT sql_id, parse_calls, executions
FROM v$sql
ORDER BY parse_calls DESC;

Detect Latch Contention

SELECT latch, gets, misses
FROM v$latch
ORDER BY misses DESC;

Check Redo Generation

SELECT name, value
FROM v$sysstat
WHERE name LIKE '%redo%';

Monitor Temp Usage by Tablespace

SELECT tablespace_name, SUM(used_blocks)*8/1024 MB
FROM v$sort_segment
GROUP BY tablespace_name;

Identify Unindexed Foreign Keys

SELECT a.table_name, a.column_name
FROM dba_cons_columns a
JOIN dba_constraints b
ON a.constraint_name = b.constraint_name
WHERE b.constraint_type = 'R'
AND NOT EXISTS (
SELECT 1 FROM dba_ind_columns i
WHERE i.table_name = a.table_name
AND i.column_name = a.column_name
);

Check SQL That Caused Recent Alerts

SELECT reason, message_type, message_level, host_id, originating_timestamp
FROM x$dbgalertext
ORDER BY originating_timestamp DESC;

Final Thoughts

These Oracle performance tuning queries help you rapidly diagnose issues and boost the performance of your database. As a DBA, keeping these queries handy will help you solve bottlenecks in minutes instead of hours.

 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 😎

PostgreSQL Audit Logging with pgaudit Extension

Let's Talk

Find your desired career path with us!