Monday, September 21, 2026

Oracle Database Performance Troubleshooting – Complete SQL Diagnostic Toolkit

Oracle Database Performance Troubleshooting – Complete SQL Diagnostic Toolkit

Oracle Database performance issues can originate from many areas: long-running SQL statements, blocking sessions, database waits, inefficient execution plans, stale object statistics, SQL plan changes, excessive I/O, or application-level workload.

Instead of checking each area separately, an Oracle DBA can use a structured set of SQL queries to quickly move from a database-level overview to session-level and SQL-level diagnostics.

This post provides a practical troubleshooting approach that can be used for Oracle Database and Oracle E-Business Suite environments, including RAC environments where GV$ views are required.

1. Performance Troubleshooting Flow

A practical troubleshooting sequence is:

  1. Capture database and instance information.
  2. Review total database connections.
  3. Identify active and inactive sessions.
  4. Find currently executing SQL.
  5. Check SQL Monitor information.
  6. Identify blocking sessions.
  7. Drill down into the affected SID and instance.
  8. Review session wait events.
  9. Check long-running operations.
  10. Generate SQL Monitor reports.
  11. Retrieve the complete SQL statement.
  12. Review bind variables.
  13. Compare historical SQL performance.
  14. Determine where the SQL spends its time.
  15. Check table and index statistics.
  16. Review execution plans from memory and AWR.
  17. Check SQL Profiles and SQL Plan Baselines.

2. Generate a Timestamped Diagnostic Output File

Before collecting diagnostic information, spool the results into a timestamped file. This makes it easier to preserve evidence for incident analysis and RCA. 

set echo off
set trimspool on
set define on

column filename new_value filename
select to_char(sysdate,'yyyymmdd-hh-mi-ss') filename from dual;

column dbname new_value dbname noprint
select name dbname from v$pdbs;

spool &dbname-&filename..txt

3. Database and Instance Information

Always capture the database name, PDB, database role, instance name, host name and Oracle version before starting detailed analysis. 

set lines 750 pages 9999

select name CDB_NAME,
       (select name from v$pdbs) PDB_NAME,
       database_role
from v$database;

select INSTANCE_NAME,
       HOST_NAME,
       logins,
       VERSION
from v$instance;

4. Check Total Database Connections

The following query summarizes active and inactive connections by database username. 

set lines 750 pages 9999

break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report

col username for a50

select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
       count(*) TOT,
       COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
       COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;

This is useful for identifying connection growth, application connection pools, unusually high inactive sessions and overall workload distribution.

5. Session Details

Once a workload issue is suspected, identify the sessions currently connected to the database. 

set linesize 750 pages 9999

column box format a30
col serial# for 999999
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,
       b.sid,
       b.serial#,
       a.spid,
       substr(b.machine,1,30) box,
       to_char(b.logon_time,'dd-mon-yyyy hh24:mi:ss') logon_time,
       substr(b.username,1,30) username,
       substr(b.osuser,1,20) os_user,
       substr(b.program,1,30) program,
       status,
       b.last_call_et AS last_call_et_secs,
       b.sql_id
from gv$session b,
     gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER'
order by b.inst_id,b.sid;

Important columns include INST_ID, SID, SERIAL#, operating-system process ID, application program, session status and current SQL ID.

6. Find SQL Currently Executing

column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35

select a.inst_id,
       a.sid,
       a.username,
       b.PARSING_SCHEMA_NAME,
       a.module,
       a.sql_id,
       a.sql_child_number child,
       b.hash_value,
       to_char(a.sql_exec_start,'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,
       (sysdate-sql_exec_start)*24*60*60 SECS,
       b.rows_processed,
       a.status,
       substr(b.sql_text,1,50) sql_text
from gv$session a,
     gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;

7. SQL Monitor – Currently Executing SQL

Real-Time SQL Monitoring is extremely useful when investigating resource-intensive SQL statements. 

set lines 1000 pages 9999

SELECT *
FROM
(
 SELECT status,
        inst_id,
        sid,
        SESSION_SERIAL# as Serial,
        username,
        sql_id,
        SQL_PLAN_HASH_VALUE,
        program,
        TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
        ROUND(elapsed_time/1000000) AS "Elapsed (s)",
        ROUND(cpu_time/1000000) AS "CPU (s)",
        substr(sql_text,1,30) sql_text
 FROM gv$sql_monitor
 WHERE status='EXECUTING'
 and module not like '%emagent%'
 ORDER BY sql_exec_start desc
);

8. Identify Blocking Sessions

Blocking sessions should be checked early during performance incidents because one blocker can affect multiple application sessions. 

set lines 750 pages 9999
col blocking_status for a100

select s1.inst_id,
       s2.inst_id,
       s1.username || '@' || s1.machine ||
       ' ( SID=' || s1.sid || ' ) is blocking ' ||
       s2.username || '@' || s2.machine ||
       ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1,
     gv$session s1,
     gv$lock l2,
     gv$session s2
where s1.sid=l1.sid
and s2.sid=l2.sid
and s1.inst_id=l1.inst_id
and s2.inst_id=l2.inst_id
and l1.BLOCK=1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;

9. Drill Down Using SID and Instance ID

accept sid default '' -
'Please provide the sid: '

accept inst_id default '' -
'Please provide the inst_id: '

In RAC environments, always capture both SID and INST_ID. A SID alone does not uniquely identify a session across all RAC instances.

10. What Is the Session Waiting For?

COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 999999
COLUMN event FORMAT A40

SELECT NVL(s.username,'(oracle)') AS username,
       s.sid,
       s.serial#,
       se.event,
       se.total_waits,
       se.total_timeouts,
       se.time_waited,
       se.average_wait,
       se.max_wait,
       se.time_waited_micro
FROM gv$session_event se,
     gv$session s
WHERE s.sid = se.sid
AND s.sid = &sid
AND s.inst_id = se.inst_id
AND s.inst_id = &inst_id
ORDER BY se.time_waited DESC;

Wait events help determine whether the session is spending time on I/O, locking, concurrency, network activity, commit activity or other database resources.

11. Current Session Wait

col WAIT_CLASS for a10

SELECT sw.inst_id,
       NVL(s.username,'(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM gv$session_wait sw,
     gv$session s
WHERE s.sid = sw.sid
and s.inst_id = sw.inst_id
and s.sid = &sid
and s.inst_id = &inst_id
ORDER BY sw.seconds_in_wait DESC;

12. Check Long-Running Operations

SET VERIFY OFF

SELECT a.sid,
       RPAD(a.opname,30),
       a.sofar,
       a.totalwork,
       a.ELAPSED_SECONDS,
       ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",
       time_remaining,
       RPAD(a.username,10) username,
       a.SQL_HASH_VALUE,
       B.STATUS
FROM GV$SESSION_LONGOPS a,
     gv$session b
WHERE a.sid=&sid
and b.inst_id=&inst_id
AND a.sofar<>a.totalwork;

This can help monitor operations such as full scans, RMAN operations, index builds and other operations exposed through V$SESSION_LONGOPS.

13. Real-Time SQL Resource Consumption

SELECT *
FROM
(
 SELECT status,
        sql_id,
        sql_exec_id,
        TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
        ROUND(elapsed_time/1000000) AS "Elapsed (s)",
        ROUND(cpu_time/1000000) AS "CPU (s)",
        buffer_gets,
        ROUND(physical_read_bytes/(1024*1024)) AS "Phys reads (MB)",
        ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
 FROM gv$sql_monitor
 WHERE sid=&sid
 and inst_id=&inst_id
 ORDER BY elapsed_time DESC
)
WHERE rownum<=20;

This quickly shows whether the SQL is CPU intensive, performing significant physical reads/writes or generating large numbers of buffer gets.

14. Generate SQL Monitor Report

set pagesize 0
set echo off
set timing off
set linesize 1000
set trimspool on
set trim on
set long 2000000
set longchunksize 2000000

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
       sql_id=>'&sql_id',
       report_level=>'ALL',
       type=>'TEXT')
from dual;

15. Retrieve Full SQL Statement

set lines 1000 pages 9999
set long 20000
col sql_text for a500

select sql_text
from dba_hist_sqltext
where sql_id = '&sql_id';

16. Check Bind Variables

col VALUE_STRING for a50

SELECT NAME,
       POSITION,
       DATATYPE_STRING,
       VALUE_STRING
FROM gv$sql_bind_capture
WHERE sql_id='&sql_id'
and inst_id=&inst_id;

Bind values are particularly useful when SQL performance differs depending on input values or data distribution.

17. SQL Historical Performance

One of the most important troubleshooting techniques is comparing current SQL performance against historical AWR information.

Useful measurements include:

  • Plan hash value
  • Executions
  • Rows per execution
  • Elapsed time per execution
  • CPU time per execution
  • I/O wait time
  • Cluster wait time
  • Application wait time
  • Concurrency wait time
  • PL/SQL execution time
  • Java execution time

A change in PLAN_HASH_VALUE combined with a significant increase in elapsed time is an important clue when investigating SQL performance regressions.

18. What Is the SQL ID Waiting On?

select sql_id,
       event,
       time_waited "time_waited(s)",
       case
         when time_waited = 0 then 0
         else round(time_waited*100 / sum(time_waited) Over(),2)
       end "percentage"
from
(
 select sql_id,
        event,
        sum(time_waited) time_waited
 from gv$active_session_history
 where sql_id='&sql_id'
 and inst_id=&inst_id
 group by sql_id,event
)
order by time_waited desc;

This provides a useful wait-event breakdown for the SQL ID using Active Session History.

19. Check Table Statistics

col table_name for a40
col owner for a30

select distinct owner,
       table_name,
       STALE_STATS,
       last_analyzed,
       stattype_locked
from dba_tab_statistics
where (owner,table_name) in
(
 select distinct owner,table_name
 from dba_tables
 where table_name in
 (
  select object_name
  from gv$sql_plan
  where upper(sql_id)=upper('&sql_id')
  and inst_id=&inst_id
  and object_name is not null
 )
);

Pay attention to STALE_STATS, LAST_ANALYZED and locked statistics before deciding whether statistics collection is required.

20. Check Index Statistics

After identifying the objects used by the SQL, review the indexes participating in its execution plan.

Important attributes include:

  • Index owner
  • Index name
  • Table name
  • Last analyzed date
  • Sample size
  • Number of rows
  • Partitioned status
  • Global statistics

21. Execution Plan from Cursor Cache

select *
from table(
 dbms_xplan.display_cursor(
   '&sql_id',
   NULL,
   'ALLSTATS LAST'
 )
);

ALLSTATS LAST is particularly useful because it can expose actual execution statistics for the most recent execution when those statistics are available.

22. Execution Plan from AWR

select *
from table(
 dbms_xplan.display_awr(
   '&sql_id',
   NULL,
   null,
   'ALLSTATS LAST'
 )
);

Comparing the current cursor plan with historical AWR plans can help identify execution-plan changes associated with a performance regression.

23. Check SQL Profiles

set lines 1000 pages 9999

col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150

select sql.sql_id,
       sql.child_number as child,
       prof.name,
       prof.category,
       prof.created,
       prof.task_exec_name,
       prof.FORCE_MATCHING,
       prof.status,
       prof.SIGNATURE
from dba_sql_profiles prof,
     gv$sql sql
where sql.sql_id in ('&sql_id')
order by created;

24. Check SQL Plan Baselines

col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30

select sql_handle,
       plan_name,
       origin,
       created,
       last_modified,
       last_verified,
       ENABLED,
       ACCEPTED,
       FIXED,
       REPRODUCED
from dba_sql_plan_baselines
where signature in
(
 select force_matching_signature
 from gv$sql
 where sql_id='&sql_id'
 and inst_id=&inst_id
);

When troubleshooting a plan regression, check whether a SQL Plan Baseline already exists and whether it is enabled, accepted, fixed and reproducible.

25. End the Diagnostic Collection

undef sid
undef sql_id
undef inst_id

spool off;

Recommended DBA Troubleshooting Sequence

Performance Issue
       |
       v
Check DB / Instance
       |
       v
Check Connections
       |
       v
Check Active Sessions
       |
       v
Identify SQL_ID
       |
       +-------------------+
       |                   |
       v                   v
Blocking?              Long Running?
       |                   |
       v                   v
Find Blocker          SQL Monitor
       |                   |
       +---------+---------+
                 |
                 v
           Check Wait Events
                 |
                 v
          Review SQL History
                 |
                 v
         Compare Plan Hashes
                 |
                 v
       Check Object Statistics
                 |
                 v
       Review Execution Plans
                 |
                 v
     SQL Profile / Baseline Check
                 |
                 v
          Identify Root Cause

Important Notes

  • Run diagnostic queries using an appropriately privileged database account.
  • Use GV$ views when troubleshooting Oracle RAC.
  • Always capture INST_ID together with the SID in RAC environments.
  • Do not kill a session simply because it appears long-running.
  • Confirm the blocker, wait event and business impact before terminating sessions.
  • Compare current and historical execution plans before concluding that a plan change caused a regression.
  • Check statistics before gathering them; do not gather statistics blindly in production.
  • AWR, ASH, SQL Monitor and some tuning functionality may require the appropriate Oracle licensing.

Conclusion

Oracle performance troubleshooting becomes much easier when the investigation follows a consistent sequence rather than jumping directly to individual SQL statements.

Start with database and session activity, identify the affected SID and SQL ID, analyze waits and SQL Monitor data, compare historical performance and execution plans, and finally check statistics, SQL Profiles and SQL Plan Baselines.

For Oracle E-Business Suite environments, this approach is particularly useful when investigating slow concurrent programs, online application performance issues, blocked transactions, expensive SQL statements and sudden SQL plan regressions.

AppsDBAStuff
Oracle E-Business Suite | Oracle Database | Performance Tuning | Apps DBA

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)