Sure-fire "kill"

[Fred]

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?

[Syltrem]

Possibly you are referring to a session where the Oracle side of things has
ceased to exist, but where the client is still alive.
In this case you need to do a kill -9, or CTRL-ALT-DEL to kill the client
process.

Session will remain in KILLED status until the client has disconnected from
Oracle.

--

OpenVMS 7.3-1, Oracle 8.1.7.4

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to me directly, remove zulu from my address

"Fred" <no***@jose.com> a écrit dans le message de
news:no*************************@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?

[Daniel Morgan]

Syltrem wrote:

Possibly you are referring to a session where the Oracle side of things has
ceased to exist, but where the client is still alive.
In this case you need to do a kill -9, or CTRL-ALT-DEL to kill the client
process.

Session will remain in KILLED status until the client has disconnected from
Oracle.

--

OpenVMS 7.3-1, Oracle 8.1.7.4

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to me directly, remove zulu from my address

"Fred" <no***@jose.com> a écrit dans le message de
news:no*************************@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 think what the OP is referring to is the time between when a kill is issued
and the session shows as killed in v_$session.

This time is the time Oracle uses to clean up the mess the session left behind.

And, as I said, if there is someway to avoid it I am not aware of it and would
advise against using it.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Joel Garry]

Fred <no***@jose.com> wrote in message news:<no*************************@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.

[Joel Garry]

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

Joel Garry wrote:

Fred <no***@jose.com> wrote in message news:<no*************************@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...3a.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

[Daniel Morgan]

Joel Garry wrote:

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

Joel Garry wrote:

Fred <no***@jose.com> wrote in message news:<no*************************@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...3a.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

I have yet to see kill -9 not kill a process.

Looking at v_$session is not a reliable indication of whether a session has been killed.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Mark D Powell]

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

Joel Garry wrote:
I have yet to see kill -9 not kill a process.

Looking at v_$session is not a reliable indication of whether a session has been killed.

Daniel, From experience I can confirm that there are circumstances
where UNIX kill -9 commands will in fact fail. But that is a UNIX
issue that is not caused by Oracle though I think the issue can affect
Oracle. We have experienced the problem on both the Pyramid and
Sequent platforms. But I believe it is secondary and think the
information you posted will solve the problem most of the time.

Also from a logical point of view it would seem to me that v$session
should be a reliable indicator of a session being killed. The status
should reflect the current state of the session or the session entry
should be removed. Logically for the view to reflect any other state
would indicate a failure in Oracle's design logic or code.

-- Mark D Powell --

[Daniel Morgan]

Mark D Powell wrote:

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

Joel Garry wrote:
I have yet to see kill -9 not kill a process.

Looking at v_$session is not a reliable indication of whether a session has been killed.

Daniel, From experience I can confirm that there are circumstances
where UNIX kill -9 commands will in fact fail. But that is a UNIX
issue that is not caused by Oracle though I think the issue can affect
Oracle. We have experienced the problem on both the Pyramid and
Sequent platforms. But I believe it is secondary and think the
information you posted will solve the problem most of the time.

Also from a logical point of view it would seem to me that v$session
should be a reliable indicator of a session being killed. The status
should reflect the current state of the session or the session entry
should be removed. Logically for the view to reflect any other state
would indicate a failure in Oracle's design logic or code.

-- Mark D Powell --

What I meant by my statement was that there is a period of time during which a killed session remains in
v_$session
while Oracle cleans up the mess. The fact that an entry remains behind does not mean that the kill did not do
what it
was intended to accomplish. I should have chosen my words more carefully.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Joel Garry]

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

I have yet to see kill -9 not kill a process.
I have. That is what a zombie is. But I wasn't talking about that.

Looking at v_$session is not a reliable indication of whether a session has been killed.

Fair enough. What would be a reliable indication of whether a session
has been killed? Are you saying Oracle doesn't know?

jg
--
@home.com is bogus.
http://groups.google.com/groups?q=zo...inc.com&rnum=5

[Daniel Morgan]

Joel Garry wrote:

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

I have yet to see kill -9 not kill a process.

I have. That is what a zombie is. But I wasn't talking about that.

Looking at v_$session is not a reliable indication of whether a session has been killed.

Fair enough. What would be a reliable indication of whether a session
has been killed? Are you saying Oracle doesn't know?

jg
--
@home.com is bogus.
http://groups.google.com/groups?q=zo...inc.com&rnum=5

And attempt to do anything new with the session after the kill should indicate, immediately, that the session has been killed. The fact that
information related to the session remains in the v_$ views does not.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Joel Garry]

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

Joel Garry wrote:

Daniel Morgan <da******@exxesolutions.com> wrote in message news:<3F***************@exxesolutions.com>...

I have yet to see kill -9 not kill a process.

I have. That is what a zombie is. But I wasn't talking about that.

Looking at v_$session is not a reliable indication of whether a session has been killed.

Fair enough. What would be a reliable indication of whether a session
has been killed? Are you saying Oracle doesn't know?

jg
--
@home.com is bogus.
http://groups.google.com/groups?q=zo...inc.com&rnum=5

And attempt to do anything new with the session after the kill should indicate, immediately, that the session has been killed. The fact that
information related to the session remains in the v_$ views does not.

The idea is to get correct information without actually trying to
connect to the session. A plus would be actually being able to manage
Oracle sessions with OEM.

Why would you care about doing anything new with a killed session?
More often, one cares about why the computer is still being impacted
long after killing the session.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/f...ure-bambi.html