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

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.

• Original Message ----- From: Chris Marquez To: Oracle-L_at_freelists.org Sent: Monday, January 16, 2006 6:37 PM 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

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