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

From: McCartney, Bruce <bruce.mccartney_at_dbinfosystems.com>
Date: Mon, 16 Jan 2006 19:54:42 -0500
Message-ID: <200FBD2049E8B744B3A27031D7695803594825@ntxbeus05.exchange.xchg>

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 

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

Received on Mon Jan 16 2006 - 18:55:10 CST