Re: looking for blocking sql statement(s)

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