Re: looking for blocking sql statement(s)
Date: Tue, 12 Feb 2008 04:40:33 -0800 (PST)
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:
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
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