Friday, 27 February 2015

On which table/index am I waiting?

Sometimes is handy to know on which object our query is working hard and there is a nice query that could help us with this:

  SQL>select segment_name,segment_type,owner,tablespace_name from
  2  dba_extents,v$session_wait
  3  where file_id=p1
  4  and p2 between block_id and block_id + blocks -1;

Of course, this will work when the wait event in v$session_wait is pointing on table/index scan (db buffer wait, scattered read and so on).

No comments:

Post a Comment