Connection pooled session detail

From: Christoph <cruepprich_at_gmail.com>
Date: Tue, 15 Nov 2011 10:31:05 -0600
Message-ID: <CAJdEiBqs9xLpE8aaTNqrTe0bqBzQEpHfOZ1a_Op+2zx76u=1ng_at_mail.gmail.com>

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.

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

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