Connection pooled session detail

  • From: Christoph <cruepprich@xxxxxxxxx>
  • To: Oracle L-List <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 15 Nov 2011 10:31:05 -0600

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

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


Other related posts: