RE: checkpoint incomplete issue
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: "'Alexander Gorbachev'" <gorbyx@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Nov 2004 16:47:59 -0600
Yes, of course. And it would be practically impossible to write =
shareable
SQL that would allow for the update of:
Just column 1
Just column 2
...
Just column n
Just columns 1 and 2
Just columns 1 and 3
...
Just columns 1 and n
Just columns 1, 2, and 3
Just columns 1, 2, and 4
...
Columns 1, 2, 3, ..., and n
Unimaginable, really. But doing nothing about the problem results in =
exactly
what you've observed. A developer ought to at least be able to branch =
around
the update if /none/ of the columns' values have changed.
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: Alexander Gorbachev [mailto:gorbyx@xxxxxxxxx]=20
Sent: Friday, November 19, 2004 4:42 PM
To: cary.millsap@xxxxxxxxxx
Subject: Re: checkpoint incomplete issue
We have recently figured that out on one of our applications.
Developers were "smart" enough updating the whole record (tens of
fields) when even one was changed. Sometimes there are cases when
nothing changed, but update to the whole row is made. Of course, this
would be difficult to address for packaged applications or similar
situation.
On Fri, 19 Nov 2004 16:19:51 -0600, Cary Millsap
<cary.millsap@xxxxxxxxxx> 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
>=20
> update t set col=3D3D'X'
> where {predicates that don't include "and col<>'X'"}
>=20
> 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.
>=20
> 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.
>=20
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>=20
> 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...
>=20
> -----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
>=20
> On Fri, 19 Nov 2004, Freeman Robert - IL wrote:
>=20
> > 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.
>=20
> 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".
>=20
> 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.
>=20
> This problem, once rife, is a real rarity these days.
>=20
> Often someone has decided to use an immature filesystem and it
> serializes on file access (defeating multiple DB writers).
>=20
> 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.
>=20
> 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.
>=20
> --
> Jeremiah Wilton
> Independent Oracle Professional
> Oracle Certified Master
> Disaster Recovery - Seminars - Technical Interviews
> http://www.speakeasy.net/~jwilton
> --
>=20
>=20
> //www.freelists.org/webpage/oracle-l
>=20
> --
> //www.freelists.org/webpage/oracle-l
>=20
--=20
Best regards,
Alex Gorbachev
--
//www.freelists.org/webpage/oracle-l
Other related posts:
