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).

How to trace a specific query, using its SQL_ID?

The answer is to use "set events":

alter system set events 'sql_trace[sql:gb07958tf8xdk] bind=true,wait=false';

A few more detailed examples at:
http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/