Oracle12c Blocking Session Information [message #682126] |
Tue, 06 October 2020 05:22 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4dd5c08253baf08cd18e103ca7a7ca2b?s=64&d=mm&r=g) |
rohitmathur11@gmail.com
Messages: 10 Registered: March 2012 Location: INDIA
|
Junior Member |
|
|
Hi All,
We are using Oracle 12c,2 Node RAC. Application users are connecting from different application servers(25) to database.
We have a small table, when ever a user connects to db, first it will lock the table get current count and then do further actions on other tables.
This table is getting blocked very frequently from different users connecting from application servers.
Some times , due to huge locking , all user session started blocking each other and the waiting queue increasing almost 2k-3k in 5-10 minutes.
We want to find who was the first blocker on this table because of which all others started waiting and how many user sessions waiting for that session to release the lock.
Is there any query we can use to find out chain of locking , Like first blocker and how many session were waiting for other session to release the lock.
Thanks.
|
|
|
Re: Oracle12c Blocking Session Information [message #682127 is a reply to message #682126] |
Tue, 06 October 2020 05:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Can you not get what you need from v$session? FINAL_BLOCKING_SESSION and FINAL_BLOCKING_INSTANCE?
Or you could run the utllockt script, though I do not know how well it handle that many sessions. You usually have to edit it a bit to get the output you want.
|
|
|
|
Re: Oracle12c Blocking Session Information [message #682129 is a reply to message #682128] |
Tue, 06 October 2020 06:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:Can you not get what you need from v$session? FINAL_BLOCKING_SESSION and FINAL_BLOCKING_INSTANCE?
And as for utllockt, you will find it with all the other utl* scripts.
Overall, though, finding the blocking session will not help you because the problem will recur. You need to consider why you are locking that table at all. It is a very odd thing to do: deliberately limit the scalability of your application. How are you doing it? And why?
|
|
|
|
|
|
|
|
|
Re: Oracle12c Blocking Session Information [message #682143 is a reply to message #682141] |
Tue, 06 October 2020 10:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I wouldn't think there is any disc access involved. If the query is using an index access path, the IO will be indirect through cache. If the query is using a scan access path, the repeated scans will also cause reads to be indirect (recent releases are very clever about that). Either way, after a few attempts all the necessary blocks will be cached.
The problem seems to be at the application level: locks. Whether these are table locks or row locks is not clear as different posts say different things. Without seeing the code that is doing this locking, I don't see how one can offer any advice.
|
|
|
|
|
|
Re: Oracle12c Blocking Session Information [message #682164 is a reply to message #682160] |
Wed, 07 October 2020 02:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Your developers really need to follow MC's suggestion.
As an instant get-them-out-of-trouble, they could replace the SELECT ... FOR UPDATE statement with SELECT ... FOR UPDATE NOWAIT which will prevent the locks from escalating. That might reduce the problem a bit while they write a proper solution.
|
|
|
|