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

> >
> > 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.

greetings,
Jan Received on Wed Jan 14 2004 - 02:24:57 CST