Objects and Blocks in waits

When you find a session waiting for either sequential read or scattered read, it might be useful to find which object is being accessed for further tuning.

To find the object and the block number the session is accessing,

SQL> Select SID, Event, P1 File#, p2 Block#, p3 “Blocks Fetched”,
wait_time, seconds_in_wait, state
From V$Session_Wait
Where Sid in (Select Sid From V$Session where osuser != ‘oracle’
and status = ‘ACTIVE’);

From the above query get the file# and the block#.

To find the name of the file, issue the following query.

SQL> SELECT tablespace_name, file_name FROM dba_data_files
WHERE file_id = &File#;

To find the object, issue the following query.

SQL> SELECT owner , segment_name , segment_type, partition_name
FROM dba_extents
WHERE file_id = &File#
AND &Block# BETWEEN block_id AND block_id + blocks -1 ;

Thanks