Re: looking for blocking sql statement(s)

Comments embedded.
On Feb 12, 8:06 am, Andreas Mosmann <mosm..._at_expires-29-02-2008.news- group.org> wrote:
> 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.

>
> 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.

>
> 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.

> 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.

>
> 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.

>
> Thank you
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

David Fitzjarrell Received on Tue Feb 12 2008 - 09:18:38 CST