Re: How to dump/trace/log all (ACTIVE) session SQL?

Thanks Tanel and Bruce.
I will investigate and read up on these options. This is exactly the kind of help I was looking for.

Chris Marquez
Oracle DBA

On 1/16/06, Tanel Põder <tanel.poder.003_at_mail.ee> wrote:

    Hi,

    Either go with oradebug hanganalyze (even if you don't have a hang yet) or check http://oraperf.sourceforge.net for a simple SGA attach program, which is able to sample session wait info (and is quite easy to customize to check current SQL for sessions as well).

    Tanel.

On 1/16/06, McCartney, Bruce <bruce.mccartney_at_dbinfosystems.com> wrote:
>
> Hi Chris,
> Have a look at Metalink note 61552.1; it describes the HANGANALYZE and
> SYSTEMSTATE and PROCESSSTATE dumps that can be helpful. You get cryptic
> trace files; but it does show who is running and waiting for what.
> Bruce
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Chris Marquez
> *Sent:* January 16, 2006 5:38 PM
> *To:* Oracle-L_at_freelists.org
> *Subject:* How to dump/trace/log all (ACTIVE) session SQL?
>
> Env:
> Oracle 9205
> RHEL3
>
> I have situation (another post on this list) where we have severe spikes
> server load and db ACTIVE sessions (waits) spikes.
> When left alone the db and server spiral out of control (and hang), but
> before it gets really bad I can kill and stop stuff.
>
> But I have no idea what (SQL) these session are doing...I know they are
> active and waiting ( "latch free" "library cache" waits)
> I don't want to trace the whole database all the time, but I would like to
> dump/trace/log the current SQL run (waiting) for all database sessions?
>
> Any ideas?
>
> PS I can NOT run SQL (v$sqltext) because my own session gets caught up as
> any ACTIVE waiting session.
>
> Thanks,
>
> --
> Chris Marquez
> Oracle DBA
>
>

--
Chris Marquez
Oracle DBA

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