Hi Christian,
PMON also uses parallel executions slaves to do transaction rollback. You can trace it
with event 10218. This event
causes the undo block address of applied undo to be written to the trace file of the
process applying it. Set the system
wide and kill an active session. You'll see the trace of undo applied in the trace
file of the parallel execution slave
process used. So far as I am aware, this is not documented anywhere other than on the
Ixora website.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Tuesday, 16 October 2001 19:41
To: Multiple recipients of list ORACLE-L
Hi Steve,
Regarding what you said about PMON parallel rollback,
you are refering to the parameter
fast_start_parallel_rollback, aren't you ?. Because
this is for parallel rollback but it is coordinated by
the SMON.
Kind regards.
- Steve Adams <steve.adams_at_ixora.com.au> wrote:
> Hi Jared,
>
> If you've already "killed" the session with ALTER
> SYSTEM and it is rolling back then there is no
> benefit in also killing
> the process from the operating system. Under Oracle8
> and later, that just makes PMON spawn a parallel
> execution slave to
> continue the rollback. It's not a huge cost; there's
> just no point. Under Oracle7, there was a risk of
> overloading PMON.
>
> As to whether one should kill the process rather
> than the session - it depends. Oracle state a clear
> preference for
> killing sessions. However, as you mention there have
> been bugs with lock retention after killing
> sessions, particularly
> early in Oracle7, that made killing processes more
> reliable. But those seem to have been fixed and
> replaced with a
> series of 8i bugs that cause locks to be retained
> after killing processes, but not after killing
> sessions. Of course,
> both should work and it should be a matter of
> indifference. Nevertheless, at this stage I am
> inclined to support the
> Oracle policy and go for killing the session first.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Saturday, 13 October 2001 7:55
> To: Multiple recipients of list ORACLE-L
>
>
>
>
> Steve,
>
> I have a problem with using 'alter system kill' to
> kill sessions.
>
> On occasion when using 'alter system kill', the unix
> process dies and
> PMON takes over, but the session never goes away,
> even after several
> days.
>
> The only time this seems to happen is when the
> session is holding a lock,
> and when this happens, the only way to release the
> lock is to bounce
> the database.
>
> I've had the same thing happen on Solaris, DG/UX and
> NT.
>
> To avoid that, I've developed the habit of using the
> system 'kill' or
> 'orakill' on NT. When doing so, I've never been
> left with a hung
> session holding a lock.
>
> I've seen this happen from 7.0.16 through 8.0.3.
>
> Don't know about 8i.
>
> Any insights into this?
>
> Thanks
>
> Jared
>
>
>
>
>
> "Steve Adams"
> <steve.adams_at_ixo To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> ra.com.au> cc:
> Sent by: Subject:
> RE: "Session marked for kill"
> root_at_fatcity.com
>
>
> 10/11/01 11:35
> PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Hi Arn,
>
> Your understanding of the message is correct. When
> you attempt to kill an
> active session with ALTER SYSTEM KILL SESSION,
> you are actually telling the session to commit
> suicide. What actually
> happens is that a bit is set in the session's
> state object to show that it has been told to die,
> and an interrupt signal
> (SIGINT) is then sent to the process that
> owns the target session.
>
> In general, the process gets the signal, notices
> that its session has been
> told to die, aborts its current call, returns
> ORA-00028 to the user, and then terminates the
> session. However, the
> process may be ignoring interrupt signals
> temporarily, if it is rolling back for example, or
> it may be stuck in a
> system call and thus unable to get any signals.
> If so, the killer waits for up to 60 seconds for the
> doomed session to wake
> up to its fate, and failing that times out
> with ORA-00031.
>
> Often when you see this error it will be because the
> doomed process is
> rolling back a large transaction. You can monitor
> V$TRANSACTION.USED_UBLK to check. If so, killing the
> process from Unix does
> not reduce the amount of work that needs to
> be done. It just makes PMON do the work instead, and
> there is no benefit in
> that. However, if the "killed" session is
> not rolling back and is still chewing up lots of CPU
> time, then by all
> means kill it from Unix.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Friday, 12 October 2001 10:50
> To: Multiple recipients of list ORACLE-L
>
>
> Sometimes we get a runaway Oracle process on our
> development machine. In
> those circumstances, I usually issue an alter
> system kill session command. Sometimes I get the
> above message after the
> kill times out. As I understand it, the
> process was doing something that couldn't be
> interrupted, and so when it
> completes its current command, it will then be
> killed. Is this correct?
>
> In these instances, the corresponding Unix process
> is still active, still
> chewing up a lot of CPU, and to alleviate the
> impact as soon as possible, I kill the Unix process
> as well.
>
> Is this a bad thing? Is there a better way I can
> handle these runaway
> processes?
>
> Thanx
>
> -a
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: steve.adams_at_ixora.com.au
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Oct 18 2001 - 01:45:42 CDT
