Re: Redo killer

mmoore_gmp_at_yahoo.com (Mike Moore) wrote in message news:<43c454d1.0410270543.4fd952c0_at_posting.google.com>...
> HansF <news.hans_at_telus.net> wrote in message news:<hRcfd.360$df2.164_at_edtnps89>...
> > Mike Moore wrote:
> >
> > > Part of our billing process creates over 1 million inserts into a
> > > table which kills our redo logs. Then when the process is complete
> > > the data is extracted and never referenced again. We truncate the
> > > table each night. In other words the data is semi-temporary.
> > >
> > > I'd change the table definition to a temporary table, except that if
> > > the program crashes it needs to pick up where it left off and not
> > > start all over again. There has got to be another way to handle this
> > > process, but I'm at a loss for a solution. Any ideas would be greatly
> > > appreciated!!!
> >
> > Is your problem the redo size, redo volume or redo 'speed'?
> >
> > OS, Oracle version, disk layout info and info like redo log sizes and switch
> > interval could be useful.
> >
> > /Hans
>
> Some details: 9i with RAC on Tru64 Unix, disk and RAM are scaled for
> so are not a problem for now.
>
> We're switching 8-12 times per hours on redo logs that are 100 MB a
> piece when this process is running. There is a similar process that
> also hammers the logs. So when these jobs are running I'm getting a
> couple gig of redo for data that has a useful life of a few hours.
> This is just stupid! With the exception of when these couple of batch
> process run the logs switch once every couple hours.
>
> I hate to add redo for bad code. Plus, it hurts my MTTR numbers.
>
> Thoughts? Suggestions? Comments? Solutions?

Maybe some ideas: http://asktom.oracle.com/pls/ask/f?p=4950:8:13329187183744121996::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330,  (found by searching asktom.oracle.com for "batch updates restart")

If you are going to restart, you either have to record what you've done or flag what you've done.

Since you are 9i, you could carefully read the requirements for nologging, then do your thing and take a backup right after. If you aren't using standy db's, anyways. I do such a thing with a CTAS for a nightly load, works great. Fortunately, I don't have to worry about recovering those tablespaces, since I can recreate faster than I could recover. Makes for some interesting scenarious with the other things I do need to recover, but it turns out they are small so I can just put the big things into read-only and backup the rest. The net effect is a couple of automatic procedures that take 1/2 hour versus many hours and many Gigs. Multi-master would be better, though well beyond the requirements, maybe someday...

jg

--
@home.com is bogus.
"How many of us have sat there for minutes going 'when is the stupid
thing going to run' and until we exit sqlplus it does nothing :)" -
tkyte