Re: Very high 'log file sync' wait time with no 'log file parallel write' wait time

joel garry wrote:
> DA Morgan wrote:
> > joel garry wrote:
> >
> > > Well, I don't know about the OP, but on one system, if I increased undo
> > > to where a script tells me it "should be," I would have to quadruple
> > > the tablespace from 10G to 40G. Now, that system
> > > has two days online full backups available, so that means an additional
> > > 90G.
> > >
> > > jg
> > > --
> > > @home.com is bogus.
> > > http://geeksaresexy.blogspot.com/2006/01/freeze-your-hard-drive-to-recover-data.html
> >
> > Only if your backup is brain-dead. Aren't you using RMAN?
>
> I'm using 9i RMAN. What about it? It takes my 10G undo file (which
> just now is using under 700M) and two other 2G files (which are using
> about 650M) and puts them into 1 11G piece. I get about 70%
> compression when I compress the pieces to an off-SAN device.
>
> >
> > And just in case someone interprets that answer as meaning you should do
> > what the v$ tells you too ... are there any 1555s or other issues?
>
> If I don't kill off leftover sessions nightly, yes. undo retention is
> 10 hours.
>
> jg
> --
> @home.com is bogus.
> "...that's not how class-action litigation is supposed to work."
> http://www.signonsandiego.com/uniontrib/20061205/news_1b5lerach.html

Your point is taken regarding "cheap", but I have found that the DBA often buys into problems that they shouldn't such as having to work around backup related disk shortages. RMAN is a great tool, but even it is limited when the database grows to sufficient size. "Cheap in this instance involves high commit rate and related log sync waits debugging vs increasing undo and getting the data loaded and then possibly resizing undo after it completes. I suggest it would be cheaper or optimal to simple increase undo in this situation. A 9 million row insert( not knowing the actual row size) at 200 bytes per row comes out to under 2gb. So, if there are indexes, that would bump up undo, but still, let's gestimate at 5gb more undo for this insert. Is it worth the time in this situation? Your situation/undo mileage may vary. Received on Tue Dec 05 2006 - 14:42:00 CST