Re: Undo header wait

Mike F wrote:
>
> Thanks for you reply. Actually the wait time is very small. Most of the
> time is zero.
> Do not forget, I am a junir DBA, I thought as long as there is a wait, we
> should be alarmed. Now I understand, the wait time, not the wait event
> itself, is more important.
>
> Thanks again
>
> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:1013967915.10296.0.nnrp-01.9e984b29_at_news.demon.co.uk...
> >
> > Can I just check my interpretation of the figures
> > you have given here. Every hour, at about 22
> > minutes past the hour, you get 10 to 20 waits
> > for undo segment headers. If this is correct then . . .
> >
> > 1) How long are these waits ? Presumably a
> > maximum of about 2/100 sec each - is the total
> > really significant as the total wait time per hour ?
> >
> > 2) Have you have checked for other wait events
> > to see of the total wait time elsewhere is perhaps
> > more significant than 40/100 sec per hour ?
> >
> > 3) Is it possible that you have arranged your system
> > so that checkpointing takes place at roughly hourly
> > intervals - if so is it possible that these waits are the
> > inevitable result of the fact that a process will have
> > to wait occasionally whilst DBWR writes the rollback
> > header to disc at a checkpoint ?
> >
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Now running 3-day intensive seminars
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> >
> > Mike F wrote in message ...
> > >After tried Howard Rogers's three suggestions, I still get the same
> > >amount of undo header waits, and it all happens at the same time, one
> > >hour difference.
> > >
> > >time event waits
> > >2-15-2002 02:21 undo header 8
> > >2-15-2002 02:22 undo header 6
> > >2-15-2002 03:22 undo header 10
> > >2-15-2002 03:24 undo header 9
> > >2-15-2002 04:23 undo header 12
> > >2-15-2002 04:26 undo header 8
> > >2-15-2002 05:25 undo header 4
> > >2-15-2002 05:26 undo header 6
> > >..........
> > >
> > >I came across the same problem form Ixora web, but they did not
> > >provide the solution, could somebody tell me what do they mean and how
> > >should we deal with the problem?
> > >
> > >Thanks
> > >
> > >Q: Even I have over 12 rollback segments and the max concurrent
> > >transaction is 4, I still get some undo header and undo block waits
> > > from V$WAITSTAT. Is it possible to increase the FREELIST or FREELIST
> > >GROUP for those rollback segments to alleviate this problem just like
> > >what we do for normal tables or data segments?
> > >
> > >A:There are no such tuning options for rollback segments. This is
> > >probably a problem with failing to retain undo blocks in cache for long
> > >enough after they have been modified.
> > >
> > >How should we keep the undo blocks in cache for long enough? increase
> > >db_block_buffer? But our hit ratio is very good. I am really confused
> > >here.
> > >
> > >Thanks
> > >
> > >
> > >
> > >
> > >
> > >--
> > >Sent by dbadba62 from hotmail piece from com
> > >This is a spam protected message. Please answer with reference header.
> > >Posted via http://www.usenet-replayer.com/cgi/content/new
> >
> >

Seeing as you've been to ixora, take a look at the response time breakdown script. Its an excellent way to decide what you should be looking at to improve performance

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."