Monday, July 27, 2026

How to know and troubleshoot how much time a session has waited on each wait event ?

 If you want to know how much time a session has waited on each wait event, here are the most useful queries.

1. Current Session Wait Information

For a specific session:

SELECT
sid,
serial#,
username,
event,
wait_class,
state,
seconds_in_wait
FROM v$session
WHERE sid = &SID;

This shows the current wait event and how long the session has been waiting.

2. Session Wait History (Recent Waits)

SELECT
sid,
event,
wait_time_micro/1000 AS wait_time_ms,
time_since_last_wait_micro/1000 AS since_last_wait_ms,
wait_count
FROM v$session_event
WHERE sid = &SID
ORDER BY wait_time_micro DESC;

Output example:

EVENT WAIT_TIME_MS WAIT_COUNT
------------------------ ------------ ----------
db file sequential read 150000 5000
log file sync 25000 1000
enq: TX row lock contention 10000 100

This is usually the first query I use.

3. Total Time Spent by a Session in Each Wait Event

SELECT
sid,
event,
total_waits,
ROUND(time_waited_micro/1000000,2) wait_time_sec,
ROUND(time_waited_micro/
NULLIF(total_waits,0)/1000,2) avg_wait_ms
FROM v$session_event
WHERE sid = &SID
ORDER BY time_waited_micro DESC;

Example:

EVENT TOTAL_WAITS WAIT_TIME_SEC AVG_WAIT_MS
------------------------ ----------- ------------ -----------
db file sequential read 50000 1200 24
log file sync 10000 150 15

4. All Active Sessions with Their Top Waits

SELECT
se.sid,
s.serial#,
s.username,
se.event,
se.total_waits,
ROUND(se.time_waited_micro/1000000,2) wait_time_sec
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
ORDER BY se.time_waited_micro DESC;

5. Using ASH (Last 1 Hour)

If Diagnostics Pack is licensed:

SELECT
session_id,
sql_id,
event,
COUNT() samples,
ROUND(COUNT()*10,2) wait_seconds
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_id = &SID
GROUP BY session_id, sql_id, event
ORDER BY samples DESC;

Note: Each ASH sample ≈ 1 second.

6. Historical Session Waits from AWR

SELECT
ash.session_id,
ash.sql_id,
ash.event,
COUNT() samples,
ROUND(COUNT()*10,2) wait_seconds
FROM dba_hist_active_sess_history ash
WHERE ash.session_id = &SID
GROUP BY ash.session_id,
ash.sql_id,
ash.event
ORDER BY samples DESC;

7. Find the Top Wait Event for a Session

SELECT *
FROM (
SELECT
event,
total_waits,
ROUND(time_waited_micro/1000000,2) wait_time_sec
FROM v$session_event
WHERE sid = &SID
ORDER BY time_waited_micro DESC
)
WHERE ROWNUM = 1;

Most Useful DBA Query

Replace 123 with the session ID:

SELECT
event,
total_waits,
ROUND(time_waited_micro/1000000,2) total_wait_sec,
ROUND(time_waited_micro/
NULLIF(total_waits,0)/1000,2) avg_wait_ms
FROM v$session_event
WHERE sid = 123
ORDER BY time_waited_micro DESC;



To get the total wait time aggregated by wait event across all logged-in sessions, use:

SELECT
se.event,
SUM(se.total_waits) AS total_waits,
ROUND(SUM(se.time_waited_micro)/1000000, 2) AS total_wait_time_sec,
ROUND(
SUM(se.time_waited_micro) /
NULLIF(SUM(se.total_waits), 0) / 1000,
2
) AS avg_wait_ms
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY se.event
ORDER BY total_wait_time_sec DESC;

If you also want to see the number of sessions affected by each wait event:

SELECT
se.event,
COUNT(DISTINCT se.sid) AS sessions,
SUM(se.total_waits) AS total_waits,
ROUND(SUM(se.time_waited_micro)/1000000, 2) AS total_wait_time_sec,
ROUND(
SUM(se.time_waited_micro) /
NULLIF(SUM(se.total_waits), 0) / 1000,
2
) AS avg_wait_ms
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY se.event
ORDER BY total_wait_time_sec DESC;

To identify the top wait events by active user:

SELECT
s.username,
se.event,
SUM(se.total_waits) AS total_waits,
ROUND(SUM(se.time_waited_micro)/1000000, 2) AS wait_time_sec
FROM v$session_event se
JOIN v$session s
ON se.sid = s.sid
WHERE s.username IS NOT NULL
GROUP BY s.username, se.event
ORDER BY wait_time_sec DESC;

And if you're troubleshooting performance, exclude idle waits:

SELECT
se.event,
SUM(se.total_waits) total_waits,
ROUND(SUM(se.time_waited_micro)/1000000,2) wait_time_sec
FROM v$session_event se
JOIN v$event_name en
ON se.event = en.name
WHERE en.wait_class <> 'Idle'
GROUP BY se.event
ORDER BY wait_time_sec DESC;

This last query is typically the most useful because it highlights only the waits that contribute to database response-time issues.

- No comments:
Labels:

How to troubleshoot and get to know about current top wait events since oracle database instance startup ?

 To get the current top wait events since instance startup, use:

SELECT *
FROM (
SELECT
event,
total_waits,
ROUND(time_waited_micro/1000000,2) AS time_waited_sec,
ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC
)
WHERE ROWNUM <= 20;

Top Wait Events by Total Wait Time (%)

SELECT
event,
wait_class,
ROUND(time_waited_micro/1000000,2) AS wait_time_sec,
ROUND(
100 * time_waited_micro /
SUM(time_waited_micro) OVER (),
2
) AS pct_wait_time
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY pct_wait_time DESC;

Current Active Waits (Right Now)

SELECT
event,
COUNT(*) sessions_waiting
FROM v$session
WHERE state = 'WAITING'
AND wait_class <> 'Idle'
GROUP BY event
ORDER BY sessions_waiting DESC;

Top Wait Events from ASH (Last 1 Hour)

SELECT
event,
COUNT() samples,
ROUND(
COUNT() * 100 /
SUM(COUNT(*)) OVER (),
2
) pct
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND wait_class <> 'Idle'
GROUP BY event
ORDER BY samples DESC;

AWR Top Wait Events for Last 24 Hours

SELECT
event_name,
SUM(total_waits_delta) total_waits,
ROUND(SUM(time_waited_micro_delta)/1000000,2) wait_time_sec
FROM dba_hist_system_event
WHERE wait_class <> 'Idle'
GROUP BY event_name
ORDER BY wait_time_sec DESC;

Quick DBA Query (Most Useful)

This gives the top waits consuming database time:

SELECT *
FROM (
SELECT
event,
wait_class,
ROUND(time_waited_micro/1000000,2) seconds_waited,
ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) avg_wait_ms
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC
)
WHERE ROWNUM <= 10;

If you're investigating a performance issue right now, also run:

SELECT
sid,
serial#,
username,
event,
sql_id,
seconds_in_wait
FROM v$session
WHERE state='WAITING'
AND wait_class <> 'Idle'
ORDER BY seconds_in_wait DESC;

This shows the exact sessions, SQL_IDs, and wait events currently contributing to the database slowdown.

- No comments:
Labels:

How to Troubleshoot high db file sequential read waits in Oracle Database ?

If you see high db file sequential read waits, it usually means sessions are doing single-block physical reads (typically index lookups, nested loop joins, row-by-row access, or reading blocks that are not in the buffer cache).

The objective is:

  1. Identify which SQLs are generating the reads.
  2. Identify which users/sessions are running them.
  3. Verify whether the issue is SQL, indexing, buffer cache, or storage latency.

Step 1: Check Current Sessions Waiting on db file sequential read

SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.sql_id,
s.event,
s.seconds_in_wait,
s.state,
sw.p1 "FILE#",
sw.p2 "BLOCK#"
FROM v$session s
JOIN v$session_wait sw
ON s.sid = sw.sid
WHERE s.event = 'db file sequential read'
ORDER BY s.seconds_in_wait DESC;

This tells you:

  • Who is waiting
  • Which SQL_ID is involved
  • Which file/block Oracle is reading

Step 2: Find SQLs Causing Most Physical Reads

Current cache:

SELECT *
FROM
(
SELECT
sql_id,
parsing_schema_name,
executions,
buffer_gets,
disk_reads,
ROUND(disk_reads/NULLIF(executions,0),2) reads_per_exec,
SUBSTR(sql_text,1,100) sql_text
FROM v$sql
WHERE disk_reads > 0
ORDER BY disk_reads DESC
)
WHERE ROWNUM <= 20;

Focus on:

disk_reads
reads_per_exec

High values indicate likely contributors.

Step 3: Identify Top Users Generating Physical Reads

SELECT
s.username,
SUM(q.disk_reads) disk_reads,
COUNT(*) sql_count
FROM v$session s
JOIN v$sql q
ON s.sql_id = q.sql_id
WHERE s.username IS NOT NULL
GROUP BY s.username
ORDER BY disk_reads DESC;

Step 4: Check Historical SQLs from AWR

Top SQLs causing reads:

SELECT *
FROM
(
SELECT
sql_id,
SUM(disk_reads_delta) disk_reads,
SUM(executions_delta) executions,
ROUND(
SUM(disk_reads_delta) /
NULLIF(SUM(executions_delta),0),
2
) reads_per_exec
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY SUM(disk_reads_delta) DESC
)
WHERE ROWNUM <= 20;

Get SQL text:

SELECT sql_id,
sql_text
FROM dba_hist_sqltext
WHERE sql_id = '&SQL_ID';

Step 5: Find SQLs Waiting on db file sequential read

Historical AWR:

SELECT
ash.sql_id,
ash.user_id,
COUNT(*) samples
FROM dba_hist_active_sess_history ash
WHERE ash.event = 'db file sequential read'
GROUP BY ash.sql_id, ash.user_id
ORDER BY samples DESC;

Resolve user names:

SELECT user_id, username
FROM dba_users;

Step 6: Current ASH Analysis

If licensed for Diagnostics Pack:

SELECT
sql_id,
session_id,
user_id,
COUNT(*) samples
FROM v$active_session_history
WHERE event='db file sequential read'
GROUP BY sql_id, session_id, user_id
ORDER BY samples DESC;

Top SQL causing waits appears at the top.

Step 7: Check Storage Latency

Sometimes the SQL is fine but storage is slow.

SELECT
file_no,
filetype_name,
small_read_reqs,
ROUND(
small_read_servicetime /
NULLIF(small_read_reqs,0),
2
) avg_read_ms
FROM v$iostat_file
ORDER BY avg_read_ms DESC;

Guidelines:

< 1 ms Excellent
1-5 ms Good
5-10 ms Acceptable
>10 ms Investigate
>20 ms Critical

Step 8: Check Which Objects Are Being Read

From ASH:

SELECT
current_obj#,
COUNT(*) samples
FROM v$active_session_history
WHERE event='db file sequential read'
GROUP BY current_obj#
ORDER BY samples DESC;

Object details:

SELECT
owner,
object_name,
object_type
FROM dba_objects
WHERE object_id=&OBJECT_ID;

This often identifies a hot table or index.

Step 9: Check Execution Plan

For the top SQL:

SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&SQL_ID',
NULL,
'ALLSTATS LAST'
));

Look for:

TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
INDEX UNIQUE SCAN
NESTED LOOP

These are the most common sources of db file sequential read.

Step 10: Determine the Root Cause

Case 1: High disk reads + poor index

Check missing indexes:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID'));

Symptoms:

  • Millions of rows visited
  • Many index lookups

Action:

  • Create/modify index
  • Review predicates

Case 2: High Reads per Execution

disk_reads/executions

Example:

Disk Reads : 10,000,000
Executions : 50
Reads/Exec : 200,000

Action:

  • Tune SQL
  • Reduce rows accessed

Case 3: Good SQL but Slow Read Time

Check:

SELECT event,
ROUND(time_waited_micro/total_waits/1000,2) avg_ms
FROM v$system_event
WHERE event='db file sequential read';

If:

avg_ms > 10

Investigate:

  • Storage
  • ASM
  • SAN
  • Multipathing
  • Exadata Cells

One Query I Use First as a DBA

SELECT
ash.sql_id,
u.username,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_users u
ON ash.user_id = u.user_id
WHERE ash.event = 'db file sequential read'
GROUP BY ash.sql_id, u.username
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

Then:

SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_id='&SQL_ID';

This quickly identifies which SQL and which user are responsible for the majority of db file sequential read waits.


Stale Statistics


SELECT owner,
table_name,
stale_stats
FROM dba_tab_statistics
WHERE stale_stats='YES';


Poor Buffer Cache Hit Ratio


SELECT name, value
FROM v$sysstat
WHERE name IN (
'physical reads',
'db block gets',
'consistent gets'
);


SELECT
ROUND((1 - (
physical_reads /
(db_block_gets + consistent_gets)
))*100,2) hit_ratio
FROM v$buffer_pool_statistics;

Hot Objects

Find objects frequently involved:


SELECT
o.owner,
o.object_name,
COUNT(*) samples
FROM v$active_session_history ash,
dba_objects o
WHERE ash.current_obj# = o.object_id
AND ash.event='db file sequential read'
GROUP BY o.owner,o.object_name
ORDER BY samples DESC;

Slow Storage

Query:


SELECT
file_no,
filetype_name,
ROUND(
small_read_servicetime/
NULLIF(small_read_reqs,0),
2
) avg_read_ms
FROM v$iostat_file
ORDER BY avg_read_ms DESC;


Decide:

  • Missing index → Create/tune index
  • Bad plan → Gather stats / SQL tuning
  • High reads per execution → SQL rewrite
  • High latency → Storage team
  • Frequent rereads → Increase cache

- No comments:
Labels:

How to know average time Oracle waited for a physical block to be read from disk and placed into the buffer cache (SGA) ?

Oracle does not directly record "time taken to move blocks from disk to SGA" for every read. However, we can estimate it using I/O wait events and read time statistics.

1. Average Single-Block Read Time (Disk → Buffer Cache)

SELECT event,
total_waits,
time_waited_micro/1000000 AS time_waited_sec,
ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms
FROM v$system_event
WHERE event = 'db file sequential read';

This event represents:

  • Reading a data block from disk into the buffer cache (SGA).
  • Commonly seen during index lookups and single-row access.

2. Average Multi-Block Read Time

SELECT event,
total_waits,
time_waited_micro/1000000 AS time_waited_sec,
ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) AS avg_wait_ms
FROM v$system_event
WHERE event = 'db file scattered read';

For full table scans, Oracle reads multiple blocks from disk into the buffer cache.

3. Direct Read Time (Bypassing Buffer Cache)

SELECT event,
total_waits,
ROUND(time_waited_micro/total_waits/1000,2) avg_wait_ms
FROM v$system_event
WHERE event LIKE 'direct path read%';

Used by:

  • Parallel queries
  • Large scans
  • Temp operations

4. Physical Read Statistics

SELECT
physical_reads,
physical_read_bytes/1024/1024 AS read_mb,
physical_read_total_io_requests,
ROUND(physical_read_total_bytes/
physical_read_total_io_requests/1024,2) avg_kb_per_read
FROM v$sysstat
WHERE name LIKE 'physical read%';

5. Best Query: Average Physical Read Latency

SELECT
ROUND(SUM(read_time) /
NULLIF(SUM(phyblkrd),0), 2) AS avg_ms_per_block
FROM v$filestat;

or in newer versions:

SELECT
ROUND(
(SUM(singleblkrdtim) / NULLIF(SUM(singleblkrds),0)),
2
) AS avg_single_block_read_ms
FROM v$iostat_file;

6. Real-Time Storage Latency by Datafile

SELECT
file_no,
filetype_name,
small_read_reqs,
ROUND(small_read_servicetime/
NULLIF(small_read_reqs,0),2) avg_read_ms
FROM v$iostat_file
ORDER BY avg_read_ms DESC;

For AWR (historical)

To see how much time the database spent waiting for disk reads during a period:

SELECT event_name,
total_waits,
ROUND(time_waited_micro/1000000,2) seconds_waited
FROM dba_hist_system_event
WHERE event_name IN (
'db file sequential read',
'db file scattered read',
'direct path read'
);

Practical Interpretation

Avg Read TimeInterpretation
< 1 msExcellent (All Flash/Exadata)
1–5 msGood
5–10 msAcceptable
10–20 msSlow
> 20 msInvestigate storage

For a DBA, the most commonly used metric to determine disk-to-SGA read latency is:

SELECT event,
ROUND(time_waited_micro/total_waits/1000,2) avg_read_ms
FROM v$system_event
WHERE event='db file sequential read';


To include the snapshot date and time from AWR, join DBA_HIST_SYSTEM_EVENT with DBA_HIST_SNAPSHOT:

SELECT
s.begin_interval_time,
s.end_interval_time,
e.event_name,
e.total_waits,
ROUND(e.time_waited_micro / 1000000, 2) AS seconds_waited,
ROUND(e.time_waited_micro / NULLIF(e.total_waits,0) / 1000, 2) AS avg_wait_ms
FROM dba_hist_system_event e
JOIN dba_hist_snapshot s
ON e.snap_id = s.snap_id
AND e.dbid = s.dbid
AND e.instance_number = s.instance_number
WHERE e.event_name IN (
'db file sequential read',
'db file scattered read',
'direct path read'
)

ORDER BY s.begin_interval_time;

This gives the average time Oracle waited for a physical block to be read from disk and placed into the buffer cache (SGA).


- No comments:
Labels:

Friday, July 24, 2026

Detailed 2-Node Oracle RAC Cluster architecture

 2-Node Oracle RAC Cluster architecture image with:

  • RAC Node 1 and Node 2 physical servers
  • Oracle Database instances
  • Oracle Clusterware / CRS
  • ASM instances
  • Public/client network
  • SCAN VIPs and node VIPs
  • Private RAC interconnect / Cache Fusion
  • Shared SAN/NAS storage
  • ASM disk groups: DATA / FRA / OCR / VOTING
  • RAC database services
  • HA, storage, network, and operations details






- No comments:

Oracle Database - 2-Node RAC Cluster on Physical Servers with Data Guard (Single Standby - Without FSFO)

2-Node RAC on Physical Servers with Single Physical Standby and Data Guard (Without FSFO) architecture diagram, showing:

  • Primary: 2-node Oracle RAC cluster
  • DR: Single-instance physical standby server
  • Data Guard Broker (DGMGRL)
  • Manual Switchover and Failover
  • No Observer Server
  • SYNC + AFFIRM redo transport
  • Maximum Availability mode
  • Near-zero RPO
  • Manual role transitions controlled by DBA





- No comments:

Oracle Database - 2-Node RAC Cluster on Physical Servers with Data Guard + FSFO

 Oracle Database 2-Node RAC Cluster on Physical Servers with Data Guard + FSFO :

  • Physical Server Node 1 & Node 2 RAC Cluster
  • ASM Shared Storage (SAN/NAS)
  • SCAN Listeners
  • Primary Database (PROD_PRI)
  • Standby RAC Cluster and Standby Database (PROD_DR)
  • Data Guard Broker
  • FSFO Observer on an independent host
  • SYNC + AFFIRM redo transport
  • Automatic failover workflow
  • Network, storage, and interconnect layers



- No comments:
Subscribe to: Posts (Atom)

How to know and troubleshoot how much time a session has waited on each wait event ?

  If you want to know how much time a session has waited on each wait event , here are the most useful queries. 1. Current Session Wait Info...