FAQ
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

Search Discussions

  • Allen, Brandon at Nov 15, 2011 at 4:36 pm
    Have you considered Statspack?

    Look at $ORACLE_HOME/rdbms/admin/spdoc.txt for more details.

    Regards,
    Brandon



    ________________________________

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Bobak, Mark at Nov 15, 2011 at 8:26 pm
    Also, consider Tanel Poder's Session Snapper.

    -Mark

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Allen, Brandon
    Sent: Tuesday, November 15, 2011 11:35 AM
    To: cruepprich@gmail.com; Oracle L-List
    Subject: RE: Connection pooled session detail

    Have you considered Statspack?

    Look at $ORACLE_HOME/rdbms/admin/spdoc.txt for more details.

    Regards,
    Brandon



    ________________________________

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
    --
    http://www.freelists.org/webpage/oracle-l




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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 15, '11 at 4:32p
activeNov 15, '11 at 8:26p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase