Every once in a while I get confronted with a questions like what sql (and less importantly, sessions) caused bytes to be sent to/from the database, genereated most redo, or cause most extensive sort? Most of the time, it is an after the fact analysis. There are my options so far ...
1) For realtime (so long session is still connected), I resort to session stats v$sessstat. 2) Use wait events showing in sql and ash stats (current and historic) to infer what sql/session caused the most impact e.g. session waiting with 'sql*net more data to client' are more LIKELY ones causing bytes to be sent out.
3) Use Tanel's session snapper.4) Design my own awr-like functionality to save v$sesstat history something like dba_hist_sesstat and may be patent it so I can have oracle pay me royalty fee for the next 20 years.
Where and what else can be done to get these facts? Thank you. -- //www.freelists.org/webpage/oracle-l
