Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sure-fire "kill"

Re: Sure-fire "kill"

From: Joel Garry <joel-garry_at_home.com>
Date: 16 Jul 2003 17:43:20 -0700
Message-ID: <91884734.0307161643.3b6e79b@posting.google.com>


Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F156F24.1DF9F7CC_at_exxesolutions.com>...
> Joel Garry wrote:
>
> > Fred <noway_at_jose.com> wrote in message news:<noway-0F6052.14454715072003_at_vienna7.his.com>...
> > > There is a setting in INIT.ORA that has the unintended side-effect of
> > > making sure the ALTER SYSTEM KILL SESSION command has immediate affect.
> > > Without this setting, I've seen some instances where the session reports
> > > as being KILLED in V$SESSION but is not physically removed until the
> > > instance is bounced. Does anyone remember this value offhand?
> >
> > I would like to know this if true. It's my understanding that the
> > alter system kill command tells SMON to kill things, which SMON may or
> > may not do depending on it's mood. For example, I'm having issues
> > with an imp hanging, and alter session kill has no effect, since imp
> > is off spinning the cpu and ignoring the database entirely. So I have
> > the long-time habit of killing processes with the OS and letting PMON
> > clean up.
> >
> > jg
> > --
> > @home.com is bogus.
> > If a tree falls in the forrest and no one is around, does it make a
> > sound? No, but all the squirrels screeming "EEEEEEEH!" do.
>
> Source: Oracle9i SQL Reference / Release 2 (9.2) / Part Number A96540-02
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_23a.htm#2065113
>
> KILL SESSION Clause
>
> The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions,
> release all session locks, and partially recover session resources. To use this clause, your
> instance must have the database open, and your session and the session to be killed must be on
> the same instance. You must identify the session with both of the following values from the
> V$SESSION view:
>
> For integer1, specify the value of the SID column.
> For integer2, specify the value of the SERIAL# column.
>
> If the session is performing some activity that must be completed, such as waiting for a reply
> from a remote database or rolling back a transaction, then Oracle waits for this activity to
> complete, marks the session as terminated, and then returns control to you. If the waiting lasts
> a minute, then Oracle marks the session to be killed and returns control to you with a message
> that the session is marked to be killed. The PMON background process then marks the session as
> terminated when the activity is complete.
>
> Whether or not the session has an ongoing transaction, Oracle does not recover the entire
> session state until the session user issues a request to the session and receives a message that
> the session has been killed.
>
> But most imporantly ... the following paragraph:
>
> IMMEDIATE
>
> Specify IMMEDIATE to instruct Oracle to roll back ongoing transactions, release all session
> locks, recover the entire session state, and return control to you immediately.

Well, as Anton posted in the other thread, this doesn't have much effect. And if whatever it is you are trying to kill doesn't bother to listen to Oracle, the session entry will stay there until you bounce the db.

This used to be a big frustrating issue on some platforms, certainly in the V7 time frame. Some process would run away with a processor, until you OS kill it - and sometimes not even then, and unix sysadmins really don't like being told a reboot is necessary. Sometimes the process would be SMON, which is why I get so skittish every time I see something else has been added to its duties, seems like architectural abuse to me. Fortunately it doesn't happen too much any more, or at least support can give some actual reasons for it to be so busy.

jg

--
@home.com is bogus.  "One day in 1965: The future Joe Strummer buys
his first Chuck Berry single, "Rock 'n' Roll Music," while visiting
his father in Tehran, Iran's capital. He is surprised, he says later,
that the Beatles didn't write it. He duly memorizes the Berry
songbook." - Peter S. Scholtes
Received on Wed Jul 16 2003 - 19:43:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US