RE: checkpoint incomplete issue

Excellent, Jeremiah; my apologies. Sorry I was careless and didn't look first...

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:

- Performance Diagnosis 101: 1/4 Calgary
- SQL Optimization 101: 11/8 Dallas, 12/13 Atlanta
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
From: Jeremiah Wilton [mailto:jwilton_at_speakeasy.net]=20 Sent: Friday, November 19, 2004 5:53 PM
To: Cary Millsap
Subject: RE: checkpoint incomplete issue

Hey that was in my article (I posted a link to)!

--
Jeremiah Wilton
Independent Oracle Professional
Oracle Certified Master
Disaster Recovery - Seminars - Technical Interviews
http://www.speakeasy.net/~jwilton

On Fri, 19 Nov 2004, Cary Millsap wrote:



> One thing I'm surprised nobody's mentioned is that excessive =3D

> checkpointing

> can be caused by excessive writing. On occasion, we see the =

application =3D


> that

> does something like

>

> 	update t set col=3D3D'X'

> 	where {predicates that don't include "and col<>'X'"}

>

> Get it? If there were a bunch of "col=3D3D'X'" rows to begin with, =

you're


> telling Oracle to change the existing 'X' values to new 'X' values. Of

> course, this can be brutal on DBWR, LGWR, ARCH, processes that are =3D

> trying to

> use CPU or acquire latches, undo management, etc.

>

> I hate to see people trying to solve problems like this by randomly =

=3D


> hacking

> parameters, adding files, or buying hardware, when the best solution =

=3D


> would

> be to make the application not do stuff that it doesn't need to be =

doing =3D


> in

> the first place.

>

>

> Cary Millsap

> Hotsos Enterprises, Ltd.

> http://www.hotsos.com

> * Nullius in verba *

>

> Upcoming events:

> - Performance Diagnosis 101: 1/4 Calgary

> - SQL Optimization 101: 11/8 Dallas, 12/13 Atlanta

> - Hotsos Symposium 2005: March 6-10 Dallas

> - Visit www.hotsos.com for schedule details...

>

>

> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org =3D

> [mailto:oracle-l-bounce_at_freelists.org]

> On Behalf Of Jeremiah Wilton

> Sent: Friday, November 19, 2004 3:14 PM

> To: oracle-l_at_freelists.org

> Subject: RE: checkpoint incomplete issue

>

> On Fri, 19 Nov 2004, Freeman Robert - IL wrote:

>

> > Hmmmm... maybe I was wrong, or the behavor has changed at some point =

=3D


> and

> > time (7 vs 8??).... looking at my 9i and 10g alert logs, it appears =

=3D


> that

> the

> > checkpoint not complete messages only appear if it can't switch =3D

> logfiles.

> >

> > Maybe I'm just getting to old and senile.

>

> Well that may be the case with me as well.  I wrote the article in

> 1932 based on Oracle 7.3.2 on a hand-cranked granite computer.  But I

> don't think the fundamental rule has changed. "Cannot allocate new log

> - checkpoint not complete".

>

> You can have 10 logs, and switch through them all without completing a

> checkpoint (if using LOG_CHECKOINT_INTERVAL it will happily start over

> with a new checkpoint at each log switch even if the previous one is

> not complete), until you get to the last log, and they are all status

> ACTIVE, at which point it runs out of logs that are INACTIVE and has

> nowhere to switch to.  Then you get CNC.

>

> This problem, once rife, is a real rarity these days.

>

> Often someone has decided to use an immature filesystem and it

> serializes on file access (defeating multiple DB writers).

>

> Another fave is the old software mirroring across arrays.  That

> software mirroring software (like VxVM) goes into full resync mode if

> the machine crashes or the mirror gets otherwise out of sync.  This

> can suck up huge quantities of available IO on a running production

> system.

>

> Finally, most people who think they are using async IO are not, due to

> incorrect filesystem, OS kernel or volume manager configuration.  The

> only way to verify is to use a system call tracer to see what flags

> the DB writer is using to open the file.  There are numerous platform

> and filesystem-specific recipes on MetaLink for getting async to work

> and making sure it is really on.

>

> --

> Jeremiah Wilton

> Independent Oracle Professional

> Oracle Certified Master

> Disaster Recovery - Seminars - Technical Interviews

> http://www.speakeasy.net/~jwilton

> --

> http://www.freelists.org/webpage/oracle-l

>

> --

> http://www.freelists.org/webpage/oracle-l

>


--
http://www.freelists.org/webpage/oracle-l