Wednesday, May 27, 2026

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.

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! 🚀

Sunday, May 17, 2026

Oracle EBS 12.2 – Patch File System Validation Checklist

1. Take Backup of Critical Files

Take backup of the following files from all RUN/PATCH nodes and keep them in a shared NAS mount location for easy recovery.

a) Context Files

Backup all context XML files.

Example:


$INST_TOP/appl/admin/*.xml

b) Environment Files


$APPL_TOP/*.env

c) WebLogic Domain Configuration


$FMW_HOME/user_projects/domains/EBS_domain_<SID>/config/config.xml

2. Copy Patch to Shared Mount Point

Copy the required patch files to the extranet/shared mount location.

Example:


/nas/ebs_patches/

3. Run ADOP Validation

Execute validation before starting the patching cycle.


adop phase=validate

Ensure validation completes successfully without errors.

4. Validate Admin Server and Node Manager

Connect to PATCH file system and validate startup/shutdown of:

Admin Server (Primary Node)
Node Manager (All Nodes)

Source Patch Environment


cd ~

. ./EBSapps.env patch

Admin Server Start/Stop Validation


$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start
$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh stop

Node Manager Start/Stop Validation


$ADMIN_SCRIPTS_HOME/adnodemgrctl.sh start
$ADMIN_SCRIPTS_HOME/adnodemgrctl.sh stop

5. Ensure No Patch File System Processes Are Running

Verify no residual PATCH filesystem processes are active across application nodes.

Example:


ps -ef | grep fs2

If any unwanted processes exist, stop them cleanly before proceeding.

6. Validate Node Manager Port Configuration

Ensure:

RUN filesystem ports are identical across all RUN nodes.
PATCH filesystem uses a different Node Manager port.

Verify Node Manager Listen Port


grep -i listen-port \
$FMW_HOME/user_projects/domains/EBS_domain_<SID>/config/config.xml

Extract Port Number


grep -i listen-port \
$FMW_HOME/user_projects/domains/EBS_domain_<SID>/config/config.xml \
| sed -n 1p \
| cut -d'>' -f2 \
| cut -d'<' -f1

Recommended DBA Validation Checks

Verify Node Manager Process


ps -ef | grep NodeManager

Check Listening Ports


netstat -an | grep LISTEN | grep <PORT>

Verify WebLogic Admin Connectivity


curl -I http://hostname:port

Important Best Practices

Always validate PATCH filesystem before adop prepare.
Ensure no orphan Java/WLS processes exist.
Maintain backup of config.xml before changes.
Validate SSH equivalency between nodes.
Confirm shared filesystem mount availability on all nodes.
Check free space in:
- $PATCH_TOP
- $NE_BASE
- $COMMON_TOP

Typical Real-Time Use Cases

This checklist is commonly used before:

adop phase=prepare
adop fs_clone
WebLogic recovery
Node Manager troubleshooting
Dual filesystem synchronization issues
Patch filesystem corruption validation

Generic Oracle EBS JAR Signing Certificate Renewal Runbook

Objective

This document provides generic steps to renew or replace JAR signing certificates in Oracle E-Business Suite environments.

Applicable for:

Oracle EBS 12.1 / 12.2
Internal and Extranet environments
Certificate renewal activities
Java/JAR signing maintenance

1. Backup Existing Files

Navigate to Admin Directory


cd $NE_BASE/EBSapps/appl/ad/admin

Backup Existing Certificate Files


cp adkeystore.dat adkeystore.dat_bkp_$(date +%Y%m%d)

cp adsign.txt adsign.txt_bkp_$(date +%Y%m%d)

cp appltop.cer appltop.cer_bkp_$(date +%Y%m%d)

cp jarsecurefile jarsecurefile_bkp_$(date +%Y%m%d)

2. Backup Java Security cacerts


cd $OA_JRE_TOP/lib/security

cp cacerts cacerts_bkp_$(date +%Y%m%d)

3. Backup Existing Signed JAR


cd $OA_JAVA

cp GetClientInfo.jar GetClientInfo.jar_sign_bkp_$(date +%Y%m%d)

4. Copy New Certificate Files

Navigate to Admin Directory


cd $NE_BASE/EBSapps/appl/ad/admin

Copy New Signing Files


cp /path_to_new_certificates/adkeystore* .

cp /path_to_new_certificates/adsign.txt .

Copy Updated cacerts


cd $OA_JRE_TOP/lib/security

cp /path_to_new_certificates/cacerts .

5. Replace Signed JAR File


cd $OA_JAVA

cp /path_to_new_certificates/GetClientInfo.jar.sig .

6. Extranet Environment Steps (If Applicable)

Backup Existing Extranet Files


cd $NE_BASE/EBSapps/appl/ad/admin

cp adkeystore.dat adkeystore.dat_extranet_bkp

cp adsign.txt adsign.txt_extranet_bkp

cp appltop.cer appltop.cer_extranet_bkp

Backup Extranet cacerts


cd $OA_JRE_TOP/lib/security

cp cacerts cacerts_extranet_bkp

7. Copy New Files to Extranet Environment


cd $NE_BASE/EBSapps/appl/ad/admin

cp /path_to_new_certificates/adkeystore* .

cp /path_to_new_certificates/adsign.txt .

Copy cacerts


cd $OA_JRE_TOP/lib/security

cp /path_to_new_certificates/cacerts .

8. Stop Oracle EBS Application Services


sh $ADMIN_SCRIPTS_HOME/adstpall.sh apps/<apps_password> -mode=allnodes

9. Regenerate JAR Files

Run ADADMIN


adadmin

Select:


Maintain Applications Files Menu
   →
Generate Product JAR files

Important Prompt


Do you wish to force regeneration of all jar files?

Answer:

Yes

10. Compile JSP Files


perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

11. Validate New Certificate

Navigate to JAR Directory


cd $FND_TOP/java/jar

Verify JAR Signature


jarsigner -verify -verbose -certs fndforms.jar | grep -i sign

Validate:

New certificate expiry date
Signer information
No verification errors

12. Start Oracle EBS Services


sh $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/<apps_password> -mode=allnodes

13. Post Validation Checks

Verify Services


sh $ADMIN_SCRIPTS_HOME/adstrtal.sh status

Validate Forms Launch

Check:

Oracle Forms open successfully
No certificate warning
No Java security popup
OAF pages working properly

14. Browser & Java Cache Cleanup

Recommended on Client Machines

Clear Java Cache
Clear Browser Cache
Restart Browser
Relaunch EBS

15. Rollback Plan

Restore Old Files


cp *_bkp_* original_filename

Restore cacerts


cp cacerts_bkp_* cacerts

Restart Services


sh $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/<apps_password> -mode=allnodes

Best Practices

Area	Recommendation
Backup	Always backup before replacement
Testing	Validate in TEST/UAT first
Downtime	Perform during maintenance window
Validation	Verify Forms and OAF pages
Rollback	Keep rollback ready
Cache	Clear Java/browser cache
Security	Verify certificate expiry before deployment

Common Troubleshooting

Issue	Possible Cause	Action
Forms not launching	Invalid signing	Verify jarsigner output
Certificate warning	Old cache	Clear Java cache
OACORE issues	Incomplete restart	Restart services
JSP errors	JSP cache stale	Run ojspCompile
JAR mismatch	Partial generation	Force regenerate JARs

Final Validation Checklist

Validation	Status
Backup completed	☐
Certificates copied	☐
cacerts updated	☐
Services stopped	☐
JARs regenerated	☐
JSP compile completed	☐
jarsigner validation successful	☐
Services started	☐
Forms tested	☐
No certificate warnings	☐

Wednesday, May 27, 2026

Realtime Performance Tunning Queries

100 Essential Oracle EBS Performance Tuning SQL Queries

Introduction

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

Conclusion

Sunday, May 17, 2026

Oracle EBS 12.2 – Patch File System Validation Checklist

Oracle EBS 12.2 – Patch File System Validation Checklist

1. Take Backup of Critical Files

a) Context Files

b) Environment Files

c) WebLogic Domain Configuration

2. Copy Patch to Shared Mount Point

3. Run ADOP Validation

4. Validate Admin Server and Node Manager

Source Patch Environment

Admin Server Start/Stop Validation

Node Manager Start/Stop Validation

5. Ensure No Patch File System Processes Are Running

6. Validate Node Manager Port Configuration

Verify Node Manager Listen Port

Extract Port Number

Recommended DBA Validation Checks

Verify Node Manager Process

Check Listening Ports

Verify WebLogic Admin Connectivity

Important Best Practices

Typical Real-Time Use Cases

Generic Oracle EBS JAR Signing Certificate Renewal Runbook

Generic Oracle EBS JAR Signing Certificate Renewal Runbook

Objective

1. Backup Existing Files

Navigate to Admin Directory

Backup Existing Certificate Files

2. Backup Java Security cacerts

3. Backup Existing Signed JAR

4. Copy New Certificate Files

Navigate to Admin Directory

Copy New Signing Files

Copy Updated cacerts

5. Replace Signed JAR File

6. Extranet Environment Steps (If Applicable)

Backup Existing Extranet Files

Backup Extranet cacerts

7. Copy New Files to Extranet Environment

Copy cacerts

8. Stop Oracle EBS Application Services

9. Regenerate JAR Files

Run ADADMIN

Select:

Important Prompt

10. Compile JSP Files

11. Validate New Certificate

Navigate to JAR Directory

Verify JAR Signature

Validate:

12. Start Oracle EBS Services

13. Post Validation Checks

Verify Services

Validate Forms Launch

14. Browser & Java Cache Cleanup

Recommended on Client Machines

15. Rollback Plan

Restore Old Files

Restore cacerts

Restart Services

Best Practices

Common Troubleshooting

Final Validation Checklist