Connection pooled session detail

From: Christoph <>
Date: Tue, 15 Nov 2011 10:31:05 -0600
Message-ID: <>

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:
  1. the single SQL statements that caused lots of LIOs
  2. 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.


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

Received on Tue Nov 15 2011 - 10:31:05 CST

Original text of this message