Realtime Performance Tunning Queries

100 Essential Oracle EBS Performance Tuning SQL Queries

A Complete Reference Guide for Oracle EBS Production DBAs

---

Introduction

Performance tuning in Oracle EBS production environments requires quick and accurate diagnostics. This post contains 100 ready-to-use SQL queries categorized into 10 sections to help you troubleshoot performance issues efficiently.

Tip: Bookmark this page for quick reference during production incidents.

Table of Contents

• 1–15: Real-Time Session & Active Monitoring
• 16–30: Wait Events & System Statistics
• 31–45: Top SQL Analysis
• 46–55: AWR & ASH Queries
• 56–70: Concurrent Requests & Managers (EBS Specific)
• 71–80: Object Statistics & Health
• 81–88: Memory Tuning (SGA & PGA)
• 89–94: Locking & Blocking
• 95–100: I/O, Tablespace & Miscellaneous

---

1–15: Real-Time Session & Active Monitoring

-- 1. Active sessions with wait events
SELECT sid, serial#, username, status, event, wait_class, seconds_in_wait, sql_id 
FROM v$session 
WHERE status = 'ACTIVE' AND username IS NOT NULL 
ORDER BY seconds_in_wait DESC;

-- 2. Sessions waiting on CPU
SELECT sid, serial#, username, sql_id, event 
FROM v$session 
WHERE status = 'ACTIVE' AND event = 'ON CPU' AND username IS NOT NULL;

-- 3. Long running sessions (by elapsed time)
SELECT sid, serial#, username, sql_id, (SYSDATE - sql_exec_start)*86400 "Elapsed (sec)" 
FROM v$session 
WHERE status = 'ACTIVE' AND sql_exec_start IS NOT NULL 
ORDER BY 5 DESC;

-- 4. Sessions by username and module
SELECT username, module, COUNT(*) sessions 
FROM v$session 
GROUP BY username, module ORDER BY sessions DESC;

-- 5. Find session by SID
SELECT sid, serial#, username, osuser, machine, program, sql_id, event 
FROM v$session WHERE sid = &sid;

-- 6. Find session by Concurrent Request ID
SELECT s.sid, s.serial#, s.sql_id, s.event, r.request_id, p.user_concurrent_program_name
FROM v$session s, apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.oracle_session_id = s.audsid 
  AND r.concurrent_program_id = p.concurrent_program_id 
  AND r.request_id = &request_id;

-- 7. Show current SQL for a session
SELECT sql_id, sql_text FROM v$sql 
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &sid);

-- 8. Sessions with high logical reads
SELECT sid, serial#, username, logical_reads 
FROM v$sesstat s, v$statname n, v$session se
WHERE s.statistic# = n.statistic# AND s.sid = se.sid 
  AND n.name = 'session logical reads'
ORDER BY logical_reads DESC FETCH FIRST 10 ROWS ONLY;

-- 9. Sessions with high physical reads
SELECT sid, serial#, username, physical_reads 
FROM v$sesstat s, v$statname n, v$session se
WHERE s.statistic# = n.statistic# AND s.sid = se.sid 
  AND n.name = 'physical reads'
ORDER BY physical_reads DESC FETCH FIRST 10 ROWS ONLY;

-- 10. Inactive sessions holding locks
SELECT s.sid, s.serial#, s.username, s.status, l.type, l.lmode, l.request 
FROM v$session s, v$lock l 
WHERE s.sid = l.sid AND s.status = 'INACTIVE' AND l.lmode > 0;

-- 11. Sessions connected from specific machine
SELECT sid, serial#, username, machine, program 
FROM v$session WHERE machine LIKE '%&machine%';

-- 12. Sessions with high PGA usage
SELECT s.sid, s.serial#, s.username, ROUND(p.pga_used_mem/1024/1024,2) "PGA MB" 
FROM v$session s, v$process p 
WHERE s.paddr = p.addr 
ORDER BY pga_used_mem DESC FETCH FIRST 15 ROWS ONLY;

-- 13. Sessions running for more than 1 hour
SELECT sid, serial#, username, (SYSDATE - logon_time)*24 "Hours" 
FROM v$session 
WHERE (SYSDATE - logon_time) > 1/24 
ORDER BY 4 DESC;

-- 14. Parallel sessions currently running
SELECT sid, serial#, username, sql_id, degree 
FROM v$px_session px, v$session s 
WHERE px.sid = s.sid AND s.status = 'ACTIVE';

-- 15. Sessions with high temp space usage
SELECT s.sid, s.serial#, s.username, ROUND(t.blocks*8/1024,2) "Temp MB" 
FROM v$session s, v$sort_usage t 
WHERE s.saddr = t.saddr 
ORDER BY blocks DESC;

16–30: Wait Events & System Statistics

-- 16. Top 10 wait events (system level)
SELECT event, total_waits, ROUND(time_waited/100,2) "Time (sec)", 
       ROUND(time_waited*100/SUM(time_waited) OVER(),2) "Pct"
FROM v$system_event 
WHERE wait_class NOT IN ('Idle') 
ORDER BY time_waited DESC FETCH FIRST 10 ROWS ONLY;

-- 17. Wait events by wait class
SELECT wait_class, ROUND(SUM(time_waited)/100,2) "Time (sec)" 
FROM v$system_event 
WHERE wait_class NOT IN ('Idle') 
GROUP BY wait_class ORDER BY 2 DESC;

-- 18. Current wait events for active sessions
SELECT sid, event, wait_class, seconds_in_wait 
FROM v$session 
WHERE status = 'ACTIVE' AND username IS NOT NULL 
ORDER BY seconds_in_wait DESC;

-- 19. Average wait time per event
SELECT event, ROUND(time_waited/NULLIF(total_waits,0)/100,4) "Avg Wait (sec)" 
FROM v$system_event 
WHERE total_waits > 0 AND wait_class NOT IN ('Idle') 
ORDER BY 2 DESC FETCH FIRST 10 ROWS ONLY;

-- 20. DB time vs CPU time ratio
SELECT name, value FROM v$sysstat 
WHERE name IN ('DB time', 'CPU used by this session');

-- 21. Redo log buffer waits
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event LIKE '%log buffer%';

-- 22. Log file sync waits
SELECT event, total_waits, ROUND(time_waited/100,2) 
FROM v$system_event WHERE event = 'log file sync';

-- 23. Buffer busy waits analysis
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event LIKE '%buffer busy%';

-- 24. Enqueue waits (locks)
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event LIKE '%enqueue%';

-- 25. Direct path read/write waits
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event LIKE 'direct path%';

-- 26. Latch free waits breakdown
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event LIKE 'latch%';

-- 27. Library cache waits
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event LIKE 'library cache%';

-- 28. Row cache lock waits
SELECT event, total_waits, time_waited 
FROM v$system_event WHERE event = 'row cache lock';

-- 29. System event delta (last 1 hour)
SELECT event, total_waits, time_waited 
FROM v$system_event 
WHERE wait_class <> 'Idle' ORDER BY time_waited DESC;

-- 30. Idle vs Non-idle wait ratio
SELECT wait_class, ROUND(SUM(time_waited)/100,2) 
FROM v$system_event GROUP BY wait_class;

31–45: Top SQL Analysis

-- 31. Top 10 SQLs by elapsed time
SELECT sql_id, ROUND(elapsed_time/1000000,2) "Elapsed (sec)", executions, 
       ROUND(elapsed_time/1000000/NULLIF(executions,0),2) "Avg (sec)"
FROM v$sql 
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- 32. Top 10 SQLs by CPU time
SELECT sql_id, ROUND(cpu_time/1000000,2) "CPU (sec)", executions 
FROM v$sql 
ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;

-- 33. Top 10 SQLs by buffer gets
SELECT sql_id, buffer_gets, executions 
FROM v$sql 
ORDER BY buffer_gets DESC FETCH FIRST 10 ROWS ONLY;

-- 34. Top 10 SQLs by disk reads
SELECT sql_id, disk_reads, executions 
FROM v$sql 
ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

-- 35. Top 10 SQLs by executions
SELECT sql_id, executions 
FROM v$sql 
ORDER BY executions DESC FETCH FIRST 10 ROWS ONLY;

-- 36. Top 10 SQLs by parse calls
SELECT sql_id, parse_calls 
FROM v$sql 
ORDER BY parse_calls DESC FETCH FIRST 10 ROWS ONLY;

-- 37. SQLs with high average execution time
SELECT sql_id, ROUND(elapsed_time/1000000/NULLIF(executions,0),2) "Avg (sec)" 
FROM v$sql 
WHERE executions > 10 
ORDER BY 2 DESC FETCH FIRST 10 ROWS ONLY;

-- 38. SQLs with high variance in execution time
SELECT sql_id, elapsed_time, executions 
FROM v$sql 
WHERE executions > 5 
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- 39. SQLs using full table scans
SELECT sql_id, SUBSTR(sql_text,1,80) 
FROM v$sql 
WHERE sql_text LIKE '%FULL%' OR sql_text LIKE '%TABLE ACCESS FULL%';

-- 40. SQLs doing high physical reads
SELECT sql_id, disk_reads 
FROM v$sql 
ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

-- 41. SQLs with high temp space usage
SELECT sql_id, ROUND(temp_space_allocated/1024/1024,2) "Temp MB" 
FROM v$sql 
ORDER BY temp_space_allocated DESC FETCH FIRST 10 ROWS ONLY;

-- 42. SQLs with high PGA memory usage
SELECT sql_id, ROUND(pga_memory/1024/1024,2) "PGA MB" 
FROM v$sql 
ORDER BY pga_memory DESC FETCH FIRST 10 ROWS ONLY;

-- 43. SQLs with high number of sorts
SELECT sql_id, sorts 
FROM v$sql 
ORDER BY sorts DESC FETCH FIRST 10 ROWS ONLY;

-- 44. Recently executed heavy SQLs
SELECT sql_id, last_active_time, elapsed_time 
FROM v$sql 
WHERE last_active_time > SYSDATE - 1/24 
ORDER BY elapsed_time DESC;

-- 45. SQLs with high elapsed time in last 15 mins
SELECT sql_id, elapsed_time 
FROM v$sql 
WHERE last_active_time > SYSDATE - 15/1440 
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

46–55: AWR & ASH Queries

-- 46. Top SQLs from AWR (Last 2 hours)
SELECT sql_id, ROUND(elapsed_time_delta/1000000,2) "Elapsed (sec)", executions_delta
FROM dba_hist_sqlstat s
JOIN dba_hist_sqltext t USING (sql_id)
WHERE s.snap_id IN (SELECT snap_id FROM dba_hist_snapshot 
                    WHERE begin_interval_time > SYSDATE - 2/24)
ORDER BY elapsed_time_delta DESC FETCH FIRST 15 ROWS ONLY;

-- 47. Top SQLs from AWR (Last 24 hours)
SELECT sql_id, ROUND(elapsed_time_delta/1000000,2) "Elapsed (sec)", executions_delta
FROM dba_hist_sqlstat s
WHERE s.snap_id IN (SELECT snap_id FROM dba_hist_snapshot 
                    WHERE begin_interval_time > SYSDATE - 1)
ORDER BY elapsed_time_delta DESC FETCH FIRST 15 ROWS ONLY;

-- 48. AWR snapshot interval check
SELECT snap_id, begin_interval_time, end_interval_time 
FROM dba_hist_snapshot 
ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY;

-- 49. Top wait events from AWR (Last 2 hours)
SELECT event_name, ROUND(time_waited_micro/1000000,2) "Time (sec)"
FROM dba_hist_system_event
WHERE snap_id IN (SELECT snap_id FROM dba_hist_snapshot 
                  WHERE begin_interval_time > SYSDATE - 2/24)
ORDER BY time_waited_micro DESC FETCH FIRST 10 ROWS ONLY;

-- 50. Top sessions from ASH (Last 30 minutes)
SELECT session_id, session_serial#, username, sql_id, event, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - 30/1440
GROUP BY session_id, session_serial#, username, sql_id, event
ORDER BY samples DESC FETCH FIRST 15 ROWS ONLY;

-- 51. Find SQL from ASH by SID
SELECT sql_id, event, COUNT(*) samples
FROM v$active_session_history
WHERE session_id = &sid AND sample_time > SYSDATE - 1/24
GROUP BY sql_id, event ORDER BY samples DESC;

-- 52. Top blocking sessions from ASH
SELECT blocking_session, COUNT(*) blocks
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
  AND sample_time > SYSDATE - 2/24
GROUP BY blocking_session ORDER BY blocks DESC FETCH FIRST 10 ROWS ONLY;

-- 53. SQLs with high DB time from AWR
SELECT sql_id, ROUND(elapsed_time_delta/1000000,2) "DB Time (sec)"
FROM dba_hist_sqlstat
WHERE snap_id IN (SELECT snap_id FROM dba_hist_snapshot 
                  WHERE begin_interval_time > SYSDATE - 1)
ORDER BY elapsed_time_delta DESC FETCH FIRST 15 ROWS ONLY;

-- 54. ASH report summary (Last 1 hour)
SELECT session_id, username, program, event, COUNT(*) 
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY session_id, username, program, event
ORDER BY COUNT(*) DESC FETCH FIRST 20 ROWS ONLY;

-- 55. AWR SQL elapsed time trend (Last 7 days)
SELECT sql_id, TRUNC(begin_interval_time) day,
       ROUND(SUM(elapsed_time_delta)/1000000,2) "Total Elapsed"
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id
WHERE begin_interval_time > SYSDATE - 7
GROUP BY sql_id, TRUNC(begin_interval_time)
ORDER BY day DESC, 3 DESC;

56–70: Concurrent Requests & Managers (EBS Specific)

-- 56. Currently running concurrent requests
SELECT request_id, phase_code, status_code, user_concurrent_program_name,
       ROUND((SYSDATE - actual_start_date)*1440,2) "Running Mins"
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND r.phase_code = 'R' ORDER BY actual_start_date;

-- 57. Long running concurrent requests (> 2 hours)
SELECT request_id, user_concurrent_program_name,
       ROUND((SYSDATE - actual_start_date)*1440,2) "Mins Running"
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND r.phase_code = 'R' AND (SYSDATE - actual_start_date) > 2/24;

-- 58. Top 20 slowest concurrent programs (Last 7 days)
SELECT p.user_concurrent_program_name,
       ROUND(AVG((actual_completion_date - actual_start_date)*1440),2) "Avg Mins",
       COUNT(*) runs
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND actual_completion_date > SYSDATE - 7
GROUP BY p.user_concurrent_program_name
ORDER BY 2 DESC FETCH FIRST 20 ROWS ONLY;

-- 59. Concurrent requests by status
SELECT phase_code, status_code, COUNT(*) 
FROM apps.fnd_concurrent_requests
WHERE request_date > SYSDATE - 1
GROUP BY phase_code, status_code ORDER BY COUNT(*) DESC;

-- 60. Concurrent manager utilization
SELECT concurrent_queue_name, running_processes, max_processes
FROM apps.fnd_concurrent_queues;

-- 61. Requests waiting for manager
SELECT request_id, phase_code, status_code, user_concurrent_program_name
FROM apps.fnd_concurrent_requests
WHERE phase_code = 'P' ORDER BY request_date;

-- 62. Requests with high elapsed time today
SELECT request_id, user_concurrent_program_name,
       ROUND((actual_completion_date - actual_start_date)*1440,2) "Mins"
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND actual_start_date > TRUNC(SYSDATE)
ORDER BY 3 DESC FETCH FIRST 15 ROWS ONLY;

-- 63. Concurrent requests completed with error (Last 24 hours)
SELECT request_id, user_concurrent_program_name, status_code
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND status_code = 'E' AND request_date > SYSDATE - 1;

-- 64. Average runtime of a specific concurrent program
SELECT p.user_concurrent_program_name,
       ROUND(AVG((actual_completion_date - actual_start_date)*1440),2) "Avg Mins"
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND p.concurrent_program_name = '&program_name'
GROUP BY p.user_concurrent_program_name;

-- 65. Concurrent requests by user (Last 24 hours)
SELECT requested_by, COUNT(*) requests
FROM apps.fnd_concurrent_requests
WHERE request_date > SYSDATE - 1
GROUP BY requested_by ORDER BY requests DESC;

-- 66. Find concurrent request details by request_id
SELECT request_id, phase_code, status_code, actual_start_date, actual_completion_date,
       user_concurrent_program_name
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND request_id = &request_id;

-- 67. Requests running more than 4 hours
SELECT request_id, user_concurrent_program_name,
       ROUND((SYSDATE - actual_start_date)*1440,2) "Mins"
FROM apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_vl p
WHERE r.concurrent_program_id = p.concurrent_program_id
  AND r.phase_code = 'R' AND (SYSDATE - actual_start_date) > 4/24;

-- 68. OPP (Output Post Processor) requests status
SELECT request_id, status_code, actual_start_date
FROM apps.fnd_concurrent_requests
WHERE concurrent_program_id IN 
      (SELECT concurrent_program_id FROM apps.fnd_concurrent_programs 
       WHERE concurrent_program_name LIKE '%OPP%')
  AND phase_code = 'R';

-- 69. Workflow background process status
SELECT request_id, status_code, actual_start_date
FROM apps.fnd_concurrent_requests
WHERE concurrent_program_id IN 
      (SELECT concurrent_program_id FROM apps.fnd_concurrent_programs 
       WHERE concurrent_program_name LIKE '%WF%');

-- 70. Concurrent manager sleep time and cache size
SELECT concurrent_queue_name, sleep_seconds, cache_size
FROM apps.fnd_concurrent_queues;

71–80: Object Statistics & Health

-- 71. Tables with stale statistics
SELECT owner, table_name, last_analyzed, num_rows
FROM dba_tab_statistics
WHERE owner IN ('APPS','XX%') 
  AND last_analyzed < SYSDATE - 7
ORDER BY last_analyzed;

-- 72. Tables with no statistics
SELECT owner, table_name FROM dba_tables 
WHERE owner IN ('APPS') AND last_analyzed IS NULL;

-- 73. Indexes with stale statistics
SELECT owner, index_name, last_analyzed 
FROM dba_ind_statistics 
WHERE owner = 'APPS' AND last_analyzed < SYSDATE - 10;

-- 74. Tables with high row count but low sample size
SELECT owner, table_name, num_rows, sample_size,
       ROUND(sample_size/NULLIF(num_rows,0)*100,2) "Sample %"
FROM dba_tab_statistics
WHERE num_rows > 100000 AND sample_size/NULLIF(num_rows,0) < 0.1;

-- 75. Last analyzed date of key EBS tables
SELECT table_name, last_analyzed 
FROM dba_tab_statistics 
WHERE table_name IN ('GL_JE_LINES','AP_INVOICES_ALL','AR_RECEIVABLES');

-- 76. Tables with locked statistics
SELECT owner, table_name, stattype_locked 
FROM dba_tab_statistics 
WHERE stattype_locked IS NOT NULL;

-- 77. Gather stats recommendation for large tables
SELECT owner, table_name, num_rows 
FROM dba_tab_statistics 
WHERE num_rows > 5000000 AND last_analyzed < SYSDATE - 5;

-- 78. Histogram status on columns
SELECT owner, table_name, column_name, histogram 
FROM dba_tab_col_statistics 
WHERE owner = 'APPS' AND histogram <> 'NONE';

-- 79. Tables with high row count change
SELECT owner, table_name, num_rows, last_analyzed 
FROM dba_tab_statistics 
WHERE num_rows > 1000000 ORDER BY num_rows DESC;

-- 80. Partitioned tables with missing stats
SELECT owner, table_name, partition_name, last_analyzed 
FROM dba_tab_partitions 
WHERE last_analyzed IS NULL;

81–88: Memory Tuning (SGA & PGA)

-- 81. SGA component sizes
SELECT name, ROUND(bytes/1024/1024,2) "Size (MB)" 
FROM v$sgainfo;

-- 82. Shared pool free memory
SELECT * FROM v$sgastat 
WHERE name = 'free memory' AND pool = 'shared pool';

-- 83. Buffer cache hit ratio
SELECT ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) "Hit Ratio %"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads';

-- 84. Library cache hit ratio
SELECT ROUND((SUM(pins) - SUM(reloads)) / SUM(pins) * 100, 2) "Library Cache Hit %"
FROM v$librarycache;

-- 85. PGA aggregate target usage
SELECT name, value FROM v$pgastat 
WHERE name IN ('aggregate PGA target parameter', 'total PGA allocated');

-- 86. Top PGA consuming sessions
SELECT s.sid, s.serial#, s.username, ROUND(p.pga_used_mem/1024/1024,2) "PGA Used MB"
FROM v$session s, v$process p 
WHERE s.paddr = p.addr 
ORDER BY pga_used_mem DESC FETCH FIRST 10 ROWS ONLY;

-- 87. PGA memory over-allocation check
SELECT name, value FROM v$pgastat 
WHERE name = 'over allocation count';

-- 88. Auto memory management status
SELECT name, value FROM v$parameter 
WHERE name IN ('memory_target', 'sga_target', 'pga_aggregate_target');

89–94: Locking & Blocking

-- 89. Current blocking sessions
SELECT blocker.sid "Blocker", blocker.serial# "Serial",
       blocked.sid "Blocked", blocked.event, blocked.seconds_in_wait
FROM v$session blocker, v$session blocked
WHERE blocker.sid = blocked.blocking_session;

-- 90. Blocking tree (multiple levels)
SELECT LEVEL, sid, serial#, username, event, blocking_session
FROM v$session
START WITH blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session;

-- 91. Locks held by a session
SELECT sid, type, lmode, request, id1, id2 
FROM v$lock WHERE sid = &sid;

-- 92. Deadlock history
SELECT * FROM dba_hist_blocking_locks 
ORDER BY snap_id DESC FETCH FIRST 10 ROWS ONLY;

-- 93. Row lock waits by object
SELECT do.object_name, COUNT(*) waits
FROM v$lock l, dba_objects do
WHERE l.type = 'TX' AND l.id1 = do.object_id
GROUP BY do.object_name;

-- 94. Sessions waiting on TX enqueue
SELECT sid, serial#, event, seconds_in_wait 
FROM v$session WHERE event LIKE '%TX%';

95–100: I/O, Tablespace & Miscellaneous

-- 95. Tablespace I/O statistics
SELECT tablespace_name, 
       ROUND(SUM(phyblkrd + phyblkwrt)/1024/1024,2) "MB Read+Write"
FROM v$filestat fs, dba_data_files df
WHERE fs.file# = df.file_id
GROUP BY tablespace_name ORDER BY 2 DESC;

-- 96. Top datafiles by I/O
SELECT file_name, phyblkrd + phyblkwrt "Total I/O"
FROM v$filestat fs, dba_data_files df
WHERE fs.file# = df.file_id 
ORDER BY 2 DESC FETCH FIRST 10 ROWS ONLY;

-- 97. Temp tablespace usage
SELECT tablespace_name, ROUND(SUM(blocks)*8/1024,2) "MB Used"
FROM v$sort_usage GROUP BY tablespace_name;

-- 98. Undo tablespace usage and contention
SELECT tablespace_name, status, COUNT(*) 
FROM dba_undo_extents GROUP BY tablespace_name, status;

-- 99. Redo log switch frequency
SELECT thread#, sequence#, first_time 
FROM v$log_history 
WHERE first_time > SYSDATE - 1 ORDER BY first_time DESC;

-- 100. Database uptime and startup time
SELECT instance_name, host_name, startup_time 
FROM v$instance;

---

Conclusion

These 100 queries cover the most critical areas of Oracle EBS performance tuning. Keep this list handy for quick diagnostics during production performance issues.

Pro Tip: Save all queries in a single `.sql` file for easy access during incidents.

Happy Tuning! 🚀