Greg Rahn, a database performance engineer with Oracle's Real-World Performance group posted a blog entry in January that details a new feature in Oracle 11g that should be extremely useful; real-time SQL monitoring.
There have been multiple occurrences of a developer coming up to me and asking "My query has been running for a long time, can you see where it is?" The answer has always been no (not entirely true if it is classified by the database as a long-running query but then it still just gives you an estimate of what percentage of the query is complete. With Oracle 11g, you can see what portion of the execution plan has been executed and where the time has been spent so far. It is also quite useful for analysis after the query has completed to see what steps are the most expensive, both in terms of CPU and IO, as well as allowing you to compare the estimated rows for each step to the actual rows.
Take a look at Greg's post on his blog Structered Data