Re: Can't find the sql text from v$sql via the hash value

Well, statspack might be OK in certain cases mostly "pseudo-hangs", i.e. instance-wide performance related. Often in hanging situation you might not even be able to take statspack snap or it will not give much useful information.

Another similar to statspack idea is to take several samples of v$session and v$sesison_wait, as well as sample enqueues, latches and transactions. Perhaps, even SQL area.

Good option is to take systemstate dump or hanganalyze using oradebug. It's note that easy to read, though, but can be used later by say Oracle support engineer.

2006/6/17, Dennis Williams <oracledba.williams_at_gmail.com>:
>
> Fan,
>
> For hanging situations, I find STATSPACK to work pretty well. When notified,
> just execute a snapshot, then execute another one maybe 5 minutes later.
> This leaves you free to deal with panicked users and managers. STATSPACK
> collects a lot of information, so usually what you need to diagnose the
> problem is available for later perusal.
>
>
> Dennis Williams

-- 
Best regards,
Alex Gorbachev

http://blog.oracloid.com
--
http://www.freelists.org/webpage/oracle-l