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@xxxxxxxxxxxxx]=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@xxxxxxxxxxxxx =3D > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Jeremiah Wilton > Sent: Friday, November 19, 2004 3:14 PM > To: oracle-l@xxxxxxxxxxxxx > 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 > -- > //www.freelists.org/webpage/oracle-l > > -- > //www.freelists.org/webpage/oracle-l > -- //www.freelists.org/webpage/oracle-l