Re: looking for blocking sql statement(s)
Date: Tue, 12 Feb 2008 04:40:33 -0800 (PST)
Message-ID: <eacf68a8-f686-4625-b321-186d6d484b63@m34g2000hsb.googlegroups.com>
On Feb 12, 7:15 am, Andreas Mosmann <mosm..._at_expires-29-02-2008.news-
group.org> wrote:
> Hi ng,
>
> we got a software that often causes any locks/deadlocks.
> We look for the reason for this and I found V$LOCK and V$SQL.
> The problem is, that I can find out SQL of the waiting sessions, but I
> can not find out the SQL statement(s) that caused the problem.
> Do you have any idea?
>
> Is it f.e. possible to log all sql statements used at the database
> (except of course the logging statement)?
>
> Hope anyone can help
>
> Andreas Mosmann
Jonathan Lewis recently posted an article to his blog that may be very
helpful for you:
http://jonathanlewis.wordpress.com/2008/02/06/trouble-shooting-2/
Yes, you can log all SQL statements (and wait events and bind variables and execution plans) for all sessions, but doing so decreases the server's performance a bit. You will find the generated trace files (one for each session) in the udump directory.
To enable system-wide logging of SQL statements, wait events, and
binds:
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
To disable system-wide logging of SQL statements, wait events, and
binds:
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Feb 12 2008 - 06:40:33 CST