Home » RDBMS Server » Server Administration » Oracle12c Blocking Session Information (Oracle 12c)
Oracle12c Blocking Session Information [message #682126] Tue, 06 October 2020 05:22 Go to next message
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.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682127 is a reply to message #682126] Tue, 06 October 2020 05:32 Go to previous messageGo to next message
John Watson
Messages: 8962
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.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682128 is a reply to message #682127] Tue, 06 October 2020 06:04 Go to previous messageGo to next message
rohitmathur11@gmail.com
Messages: 10
Registered: March 2012
Location: INDIA
Junior Member
Hi John,

Yes , we have tried with GV$session_blockers and Gv$wait_chains but we are not getting expected results. Also we need to find such
information form history view. So that we can find out root cause of issue occurred before in time.
As we do have 500-600 concurrent session from different RAC nodes , it is getting difficult to find out source, session information.

Where we can find utllockt script and any query you have which can provide this information from history views.


Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682129 is a reply to message #682128] Tue, 06 October 2020 06:10 Go to previous messageGo to next message
John Watson
Messages: 8962
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?

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682130 is a reply to message #682129] Tue, 06 October 2020 06:26 Go to previous messageGo to next message
rohitmathur11@gmail.com
Messages: 10
Registered: March 2012
Location: INDIA
Junior Member
Hi John,

Yes, agree with you. Issue is from application side as so many users logs in at same time and try to hold lock on few rows of one table.
But as DBA we need to share such Root cause analysis information to application team so that they can use this information to fine tune from application end. Let me search that script and try to see if that can help us.
Thanks for your inputs.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682133 is a reply to message #682130] Tue, 06 October 2020 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

The problem is in the design not fine tune.
As John said "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.".

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682136 is a reply to message #682133] Tue, 06 October 2020 08:47 Go to previous messageGo to next message
rohitmathur11@gmail.com
Messages: 10
Registered: March 2012
Location: INDIA
Junior Member
Hi Michel,

Agree, Some how application is designed to fetch latest count from a table every time a user logs in to validate its access.
May it was working fine when number of concurrent loging were less but as now it has increased they are facing issue with it.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682138 is a reply to message #682136] Tue, 06 October 2020 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Root cause & fix are at application level & can't be resolved at the database level.
Application is working as designed, but the design does NOT scale. So you live with what you have or change the design.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682140 is a reply to message #682138] Tue, 06 October 2020 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

... or the server(s) to increase their power by the same ratio than the number of concurrent users.

http://www.google.com:80/watch?v=5WPOUvzyLjc

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682141 is a reply to message #682140] Tue, 06 October 2020 10:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Tue, 06 October 2020 08:25

... or the server(s) to increase their power by the same ratio than the number of concurrent users.

http://www.google.com:80/watch?v=5WPOUvzyLjc

I could be mistaken, but I think that more CPU/servers would make the problem WORSE, not better.
The problem is contention on disk access, so adding more processing power would INCREASE the disk contention by having more sessions trying to access the same table concurrently.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682143 is a reply to message #682141] Tue, 06 October 2020 10:39 Go to previous messageGo to next message
John Watson
Messages: 8962
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.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682144 is a reply to message #682141] Tue, 06 October 2020 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I didn't specify what I meant by "server" and "power" as it highly depends on the application treatments.

The application servers may have to increase the CPU and/or memory which may decrease the lock retention time faster executing the treatment.
You're, of course, right about the DB server unless the treatment is done in PL/SQL with more crunching data than fetching rows.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682160 is a reply to message #682140] Wed, 07 October 2020 02:22 Go to previous messageGo to next message
rohitmathur11@gmail.com
Messages: 10
Registered: March 2012
Location: INDIA
Junior Member
Hi John,Michel,

My mistake if i did not clear about issue. It is a row level lock.
Actually this table is having current max count of cases generated for different domains.
When ever a new db session connects it will take current max values ( Select maxcevalue from table1 for update).
Then it will append other random numbers to this maxvalue and insert data in to other tables. With out getting this maxvalue
users will not be able to raise new cases.
Problem occurs when more then 500 session comes at same time to have lock on that same row.

We have already communicated to product team on it but they use to ask more information on session details those were involved in the issue. That's why i have created this discussion to get help from you.

Thanks for all your suggestions.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682162 is a reply to message #682160] Wed, 07 October 2020 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

The case is clear.

Quote:
When ever a new db session connects it will take current max values ( Select maxcevalue from table1 for update).
Then it will append other random numbers to this maxvalue and insert data in to other tables. With out getting this maxvalue
users will not be able to raise new cases.

This is what sequences are for.
This is the design error and should be changed.
The asked information is pointless and won't change the fact that you will for ever have waiting sessions.
Your only solution is to change the design.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682164 is a reply to message #682160] Wed, 07 October 2020 02:47 Go to previous messageGo to next message
John Watson
Messages: 8962
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.

Report message to a moderator

Re: Oracle12c Blocking Session Information [message #682165 is a reply to message #682164] Wed, 07 October 2020 02:59 Go to previous message
rohitmathur11@gmail.com
Messages: 10
Registered: March 2012
Location: INDIA
Junior Member
Hello John,

Sure, This can help us. Let me suggest this to product team to have it incorporated in the code.

"SELECT ... FOR UPDATE NOWAIT" .

This can be a temporary solution till the application flow can be changed by them.

Thanks Again for suggestions.

Report message to a moderator

Previous Topic: TEMPORARY TABLESPACE UTILIZATION TREND
Next Topic: How to deal with statistics during migration
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 02 17:50:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.