Connection pooled session detail

I would like to find out which SQL statements that come in through
connection pooled sessions, cause the most work (logical I/Os) on my
database. I want to get a periodical report that shows me:
  a) the single SQL statements that caused lots of LIOs
  b) the SQL statments thay may not individually cause a lot of LIOs, but
because they ran frequently, accumulated a lot of LIOs.
Ideally, I would also like to capture which programs/modules/users executed
these statements, so I can see which of these caused to most work on the
database.

At this point I do not have the option of instrumenting the applications
that use the connection pooled sessions.
One way I thought of doing this, is by turning on SQL trace (10046) for the
connection pooled sessions, then analyzing the trace files. This could
cause a lot of overhead, and would have to be done carefully.

Another possibility would be to mine the SGA.

I don't have AWR.

I would like to get some thoughts from the group about this.

Regards,
Christoph
http://ruepprich.wordpress.com

-- 
"Men do not quit playing because they grow old; they grow old because they
quit playing."
- Justice Oliver Wendell Holmes


--
http://www.freelists.org/webpage/oracle-l


Other related posts: