Home » RDBMS Server » Server Administration » Blocking sessions (Oracle 11.2.0.4.0 , Linux)
Blocking sessions [message #622188] Fri, 22 August 2014 07:26 Go to next message
mc1825263
Messages: 7
Registered: August 2014
Location: india
Junior Member
Hi,

I see numerous blocking sessions in my database say around 100 blocking sessions.It looks user is deleting some data from application end and he wants to kill all those blocking sessions which is tedious task to do.What to do here and how to kill all those blocking sessions in one time ?

Regds,
mc

Report message to a moderator

Re: Blocking sessions [message #622190 is a reply to message #622188] Fri, 22 August 2014 07:36 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You could write a script to generate it using v$session, however I'd be asking if you should be killing open transactions personally!

Report message to a moderator

Re: Blocking sessions [message #622193 is a reply to message #622190] Fri, 22 August 2014 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd also want to know how you ended up with one session being blocked by 100 others, that sounds like the delete is something that should be done with the application shut down.

Report message to a moderator

Re: Blocking sessions [message #622194 is a reply to message #622193] Fri, 22 August 2014 07:58 Go to previous messageGo to next message
mc1825263
Messages: 7
Registered: August 2014
Location: india
Junior Member
Hi Roachcoach/Cookiemaster ,

Based on your suggestion i prepared a script, but even after killing all those blocking sessions i could see 100 sessions
exist in the database. I have attached the blocking sessions for your reference.

Regds,
mc

Report message to a moderator

Re: Blocking sessions [message #622195 is a reply to message #622194] Fri, 22 August 2014 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
What query did you use to get that list?

Report message to a moderator

Re: Blocking sessions [message #622197 is a reply to message #622195] Fri, 22 August 2014 08:21 Go to previous messageGo to next message
mc1825263
Messages: 7
Registered: August 2014
Location: india
Junior Member
 
select sid,blocking_session, blocking_session_status,username
from gv$session
where blocking_session is not null
 order by 1 asc
/

Report message to a moderator

Re: Blocking sessions [message #622198 is a reply to message #622197] Fri, 22 August 2014 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I suspect that application has a serious design flaw.

Report message to a moderator

Re: Blocking sessions [message #622199 is a reply to message #622198] Fri, 22 August 2014 08:30 Go to previous messageGo to next message
mc1825263
Messages: 7
Registered: August 2014
Location: india
Junior Member
How to prove that issue is on application side ? Gradually blocking sessions are also increasing.

Regds,
mc

[Updated on: Fri, 22 August 2014 08:32]

Report message to a moderator

Re: Blocking sessions [message #622200 is a reply to message #622199] Fri, 22 August 2014 08:38 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
>How to prove that issue is on application side
These are DML locks - the application is issuing the DML.


I agree with CM - looks like someone is running something which should be run in an outage.

Report message to a moderator

Re: Blocking sessions [message #622201 is a reply to message #622199] Fri, 22 August 2014 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't have 100 sessions blocking 1 session.
You have 1 session blocking 100 sessions.


blocking_session is the sid of the session blocking the session that row in the view is for.

Report message to a moderator

Re: Blocking sessions [message #622203 is a reply to message #622201] Fri, 22 August 2014 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://alexzeng.wordpress.com/2012/06/01/get-the-final-blocker-sessions-in-oracle/

Report message to a moderator

Re: Blocking sessions [message #622207 is a reply to message #622198] Fri, 22 August 2014 09:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
BlackSwan wrote on Fri, 22 August 2014 18:58
I suspect that application has a serious design flaw.


+1

Mostly it is seen that, having blocking sessions, locks, deadlocks etc in an application... Oracle is just the victim, the application is the culprit.

Report message to a moderator

Re: Blocking sessions [message #622217 is a reply to message #622207] Fri, 22 August 2014 09:55 Go to previous messageGo to next message
mc1825263
Messages: 7
Registered: August 2014
Location: india
Junior Member
Thank you very much for your assistance.

Report message to a moderator

Re: Blocking sessions [message #622219 is a reply to message #622217] Fri, 22 August 2014 10:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So what did you find out? Please feedback, it would be useful to other folks looking for similar issue and resolution.

Report message to a moderator

Re: Blocking sessions [message #622380 is a reply to message #622207] Mon, 25 August 2014 21:47 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Lalit Kumar B wrote on Fri, 22 August 2014 14:10
BlackSwan wrote on Fri, 22 August 2014 18:58
I suspect that application has a serious design flaw.


+1

Mostly it is seen that, having blocking sessions, locks, deadlocks etc in an application... Oracle is just the victim, the application is the culprit.


No, I do not think all of them are even victim or culprit. The very reasons apear around this problem, example:

- 1 user do DML into 1 milion rows table by another tool for business, it done by commit, at this time, 20 milion rows are to be inserted into this table by 3 or 4 progressions java, yes, right, but how about online_redo_log groups? In the end of commit by user manual DML, the redo would be switch by configuration small log, now, 20 millions rows would be inserted continous. And what? I see the even "log file switch " and its wait class is "Configuration", the bloking sid occur. Is Oracle victim or culprit?

[Updated on: Mon, 25 August 2014 21:48]

Report message to a moderator

Re: Blocking sessions [message #622407 is a reply to message #622380] Tue, 26 August 2014 03:01 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
A log file switch falls under a configuration wait class. Deletes locking rows falls under application wait class.

The reasons for this is the log file switches are indeed internal oracle enforced waits, but unless you're having severe hardware/system/design problems, these are extremely fast and won't be a bottleneck on a reasonable system.

Application wait classes, the DB can't really do much about (hence the class) as if someone is holding an open, uncommitted transaction, there is no automatic fix available. The database cannot arbitrarily decide to kill it.

You need to understand the blocking session and what it is doing. From the OPs description it read to be application waits as opposed to config ones.

Report message to a moderator

Re: Blocking sessions [message #622423 is a reply to message #622201] Tue, 26 August 2014 05:11 Go to previous message
martens_b2014
Messages: 13
Registered: June 2014
Junior Member
cookiemonster wrote on Fri, 22 August 2014 15:40
You don't have 100 sessions blocking 1 session.
You have 1 session blocking 100 sessions.


blocking_session is the sid of the session blocking the session that row in the view is for.



Hi,

I think you are right. There can only be one session blocking the rest. When this session is gone then most likely all the other sessions will be able to perform there mutation followed by a commit and so slowly all blocking session will vanish. If not then you have to kill them one by one until all other not blocking sessions are freed. It is no use of killing all sessions in the list of v$session.

I have uploaded a small query that hopefull helps to find the main blocking session.

Report message to a moderator

Previous Topic: Database down but database missing
Next Topic: Reuse storage of tablespace
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 00:49:32 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.