Re: kill session ... is of course only Ultima Ratio.

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<bu2uia$cpfr7$1_at_ID-152732.news.uni-berlin.de>...
> > >
> > > I would almost never kill the UNIX process but rather the session by
> KILL
> > > SESSION,
> > > because the lock and the session status 'KILLED' has a _meaning_:
> > > it cleans up after the user, and so long the lock is hold,
> > > but if You kill the UNIX process before time,
> > > You may have Your database corrupted just because of senseless
> impatience !
> >
> > On the other hand, the unix kill gets it cleaned up by PMON, while the
> > session kill waits on SMON. In the past, SMON was sometimes way too
> > busy, while PMON was rapid. I have the general impression that SMON
> > is once again getting lots of responsibility as the Oracle versions
> > progress.
> >
> > Can't say as I've seen a corruption from PMON, but I've sure seen SMON
> > be worse than useless for this. And of course, seen people kill the
> > wrong process both ways.
> >
> > >
> > > I use a DBMS_JOB running every 3 minutes, and if a user is detected
> twice
> > > blocking others
> > > (locking itself is not critical), then he gets killed - if he/she is
> within
> > > a defined group of everyday users.
> > > Of course, I do not kill admin sessions. All actions are logged for
> further
> > > investigations.
> >
> > Man, that sounds like nasty DBA. Shoot first and let The Committee handle
> it.
> >
>
> To take Your metaphore, Killing is what "The Commettee" ;) had decided as
> Ultima Ratio.
> But I am pityfully not a member of this Commettee :(
>
> Of course there maybe be better solutions about blockings,
> but one of my tasks is also to convince a "nasty" Committee saying:
> "Solve problem A, but do not go ways X,Y and Z ! Dont ask why, DO it !"
>
> My script for the job was to log only in the first place.
> We logged for a while and found many blockings over the day, leading to no
> clue.
> I also do not want to kill sessions, if it was my decision.
> And later I found the real cause for the problem (unindexed FKs in the data
> model),
> and after I fixed this, the session killing job has become useless,
> and blockings have become very rare: 1 per month compared to about 20-50 per
> day before.
>
> Thus, session blockings are only a symptom
> for something terribly wrong in the data model or/and the app.

You are real lucky. So many times the reason is a poorly programmed app that does something like set a flag while several transactions take place, rather than doing it all in one transaction. So killing the session winds up being a rather large exercise in tracing down flags and updates, when it should have just rolled back with the kill.  But then if it were killed, there would be huge rollback issues.

jg

--
@home.com is bogus.
"CEOs as a group are greatly overrated. I can say that because I've
been a CEO, and I've been greatly overrated." - Jack Bogle