Re: Fast roll-back
Date: Sat, 24 Jan 2009 16:10:51 -0800 (PST)
On Jan 24, 3:55 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Sat, 24 Jan 2009 02:38:36 +0100, The Boss wrote:
> > If the following comment on oracle-l is true, both Mladen and yourself
> > could be right:
> > "When you kill a process using alter system kill session then pmon will
> > do the rollback/recovery, if instead you kill the OS process smon will
> > do the recovery which might look more attractive sometimes since
> > parallel can be used. Be aware of 100% CPU usage by smon and his
> > parallel processes though."
> The manual, which I used to consider the ultimate resource which is always
> right says this:
> "System monitor (SMON)
> The system monitor performs recovery when a failed instance starts up
> again. In an Oracle Real Application Clusters database, the SMON process
> of one instance can perform instance recovery for other instances that
> have failed. SMON also cleans up temporary segments that are no longer in
> use and recovers dead transactions skipped during system failure and
> instance recovery because of file-read or offline errors. These
> transactions are eventually recovered by SMON when the tablespace or file
> is brought back online.
> Process monitor (PMON)
> The process monitor performs process recovery when a user process fails.
> PMON is responsible for cleaning up the cache and freeing resources that
> the process was using. PMON also checks on the dispatcher processes
> (described later in this table) and server processes and restarts them if
> they have failed."
> This is from the 11g DBA guide:http://tinyurl.com/bu5gpy
> I did, however, read the article from Fairlie Rego and I did look
> in V$SESSTAT and V$STATNAME for all statistics with the name like '%roll%'
> Result looks like this:
> IMU CR rollbacks 0
> cleanouts and rollbacks - consistent read gets 33
> rollback changes - undo records applied 2
> rollbacks only - consistent read gets 32
> transaction rollbacks 1
> transaction tables consistent read rollbacks 0
> user rollbacks 0
> So, SMON has definitely done a transaction rollback and some other
> rollbacks. The same stats for PMON are all zeroes.
Wow, I hadn't realized how far out of date my conceptions here are. Really appreciate this, since I'm having to kill some runaway processes - a really simple obvious requirement for a short list of values that winds up recursively fast-full scanning a huge index, so users kill their hourglass-displaying client and go home, and later I notice a cpu going full blast for hours when no one should be on... I've started using the EM variant of kill session, since it is so easy to go to the session from the performance screen, but was wondering whether I should be manually killing processes, as is my very old habit. Since I'm having to prove this silly stuff to the vendor anyways, I'll hopefully be checking these metrics out.
Re-reading this thread, I'm now more confused about which does what when. I definitely don't need RAC.
-- _at_home.com is bogus. This would be along my weekday commute on 76: http://www3.signonsandiego.com/stories/2009/jan/24/bn24plane135655-plane-crash/?zIndex=42512Received on Sat Jan 24 2009 - 18:10:51 CST