looking for blocking sql statement(s)

#1

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

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#2

On Feb 12, 7:15*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

Quote:

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/2...le-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.

#3

On Feb 12, 7:15*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

Quote:

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/2...le-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.

#4

On Feb 12, 7:15*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

Quote:

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/2...le-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.

#5

On Feb 12, 7:15*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

Quote:

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/2...le-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.

#6

Hi Charles,

thank you for your answer, it took me closer to the problem. But can you
tell me if it is possible to find out especially that SQL statement(s)
that is (are) blocking?

SELECT S.sid, S.sql_hash_value, Q.sql_text
FROM V$SESSION S
left join V$SQL Q
on S.sql_hash_value = Q.hash_value
WHERE S.SID IN (7,9,12,13);

will give me the statements of all waiting sessions, but the blocking
sessions SQL is not displayed anymore.

I read the article from Jonathan Lewis and if I understood correctly I
must hit exact the point the blocking (long) SQL statement is running.
But if f.e. the statement doesn't need much time but it is a long time
to commit I do not know what was the block reason. Another case is that
there are more than 1 statements, f.e.

update tableA set ColumnA=1 where ColumnA=2;
update tableA set ColumnA=2 where ColumnA=3;
..
update TableA set ColumnA=n-1 where ColumnA=n;

thank you for your alter system- statements. Actually I do not think
about server performance, because it is that slow because of the locks,
that it is more important to find the bottle neck then to work with
maximum performance.

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#7

Hi Charles,

thank you for your answer, it took me closer to the problem. But can you
tell me if it is possible to find out especially that SQL statement(s)
that is (are) blocking?

SELECT S.sid, S.sql_hash_value, Q.sql_text
FROM V$SESSION S
left join V$SQL Q
on S.sql_hash_value = Q.hash_value
WHERE S.SID IN (7,9,12,13);

will give me the statements of all waiting sessions, but the blocking
sessions SQL is not displayed anymore.

I read the article from Jonathan Lewis and if I understood correctly I
must hit exact the point the blocking (long) SQL statement is running.
But if f.e. the statement doesn't need much time but it is a long time
to commit I do not know what was the block reason. Another case is that
there are more than 1 statements, f.e.

update tableA set ColumnA=1 where ColumnA=2;
update tableA set ColumnA=2 where ColumnA=3;
..
update TableA set ColumnA=n-1 where ColumnA=n;

thank you for your alter system- statements. Actually I do not think
about server performance, because it is that slow because of the locks,
that it is more important to find the bottle neck then to work with
maximum performance.

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#8

Hi Charles,

thank you for your answer, it took me closer to the problem. But can you
tell me if it is possible to find out especially that SQL statement(s)
that is (are) blocking?

SELECT S.sid, S.sql_hash_value, Q.sql_text
FROM V$SESSION S
left join V$SQL Q
on S.sql_hash_value = Q.hash_value
WHERE S.SID IN (7,9,12,13);

will give me the statements of all waiting sessions, but the blocking
sessions SQL is not displayed anymore.

I read the article from Jonathan Lewis and if I understood correctly I
must hit exact the point the blocking (long) SQL statement is running.
But if f.e. the statement doesn't need much time but it is a long time
to commit I do not know what was the block reason. Another case is that
there are more than 1 statements, f.e.

update tableA set ColumnA=1 where ColumnA=2;
update tableA set ColumnA=2 where ColumnA=3;
..
update TableA set ColumnA=n-1 where ColumnA=n;

thank you for your alter system- statements. Actually I do not think
about server performance, because it is that slow because of the locks,
that it is more important to find the bottle neck then to work with
maximum performance.

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#9

Hi Charles,

thank you for your answer, it took me closer to the problem. But can you
tell me if it is possible to find out especially that SQL statement(s)
that is (are) blocking?

SELECT S.sid, S.sql_hash_value, Q.sql_text
FROM V$SESSION S
left join V$SQL Q
on S.sql_hash_value = Q.hash_value
WHERE S.SID IN (7,9,12,13);

will give me the statements of all waiting sessions, but the blocking
sessions SQL is not displayed anymore.

I read the article from Jonathan Lewis and if I understood correctly I
must hit exact the point the blocking (long) SQL statement is running.
But if f.e. the statement doesn't need much time but it is a long time
to commit I do not know what was the block reason. Another case is that
there are more than 1 statements, f.e.

update tableA set ColumnA=1 where ColumnA=2;
update tableA set ColumnA=2 where ColumnA=3;
..
update TableA set ColumnA=n-1 where ColumnA=n;

thank you for your alter system- statements. Actually I do not think
about server performance, because it is that slow because of the locks,
that it is more important to find the bottle neck then to work with
maximum performance.

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#10

Comments embedded.
On Feb 12, 8:06*am, Andreas Mosmann <mosm... (AT) expires-29-02-2008 (DOT) news-
group.org> wrote:

Quote:

Hi Charles,

thank you for your answer, it took me closer to the problem. But can you
tell me if it is possible to find out especially that SQL statement(s)
that is (are) blocking?

Sometimes you can, sometimes you can't. It depends upon how quickly
the statement executes and what the blocked sessions are waiting on.
If they're waiting on a statement in progress (a large update) most
likely you can; if it's a commit or rollback then most likely you
cannot.

Quote:

SELECT S.sid, S.sql_hash_value, Q.sql_text
FROM V$SESSION S
left join V$SQL Q
on S.sql_hash_value = Q.hash_value
WHERE S.SID IN (7,9,12,13);

will give me the statements of all waiting sessions, but the blocking
sessions SQL is not displayed anymore.

That's because the statements didn't take a long time to execute and
the wait is based upon a commit or rollback, as Jonathan Lewis
explained in his article. Thus the SQL_ADDRESS and SQL_HASH_VALUE are
both 0 in V$SESSION for the blocking session and cannot be linked back
to V$SQL or V$SQLTEXT.

Quote:

I read the article from Jonathan Lewis and if I understood correctly I
must hit exact the point the blocking (long) SQL statement is running.
But if f.e. the statement doesn't need much time but it is a long time
to commit I do not know what was the block reason.

Such is the issue with 'select .. for update' statements and other,
fast running SQL.

Quote:

Another case is that
there are more than 1 statements, f.e.

update tableA set ColumnA=1 where ColumnA=2;
update tableA set ColumnA=2 where ColumnA=3;
.
update TableA set ColumnA=n-1 where ColumnA=n;

A session will be blocked by one other session as shown in the V
$SESSION view in the BLOCKING_SESSION column. Thus you really can't
have more than one blocking SQL statement for a single blocked
session. Of course you may have more than one blocked session; you
could generate an IN list and retrieve the entire list of blocking sql
if it's available.

Quote:

thank you for your alter system- statements. Actually I do not think
about server performance, because it is that slow because of the locks,
that it is more important to find the bottle neck then to work with
maximum performance.

Apparently the bottleneck is the application. But, that's simply a
guess.

Quote:

Thank you
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

David Fitzjarrell