Re: Temporary table use and the redo log

From: <codefragment_at_googlemail.com>
Date: Mon, 7 Sep 2009 12:56:24 -0700 (PDT)
Message-ID: <059ea964-7854-43be-843a-3cbce87916fb_at_c37g2000yqi.googlegroups.com>

On 7 Sep, 20:22, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 07.09.2009 19:57, Mladen Gogala wrote:
>
>
>
>
>
> > On Mon, 07 Sep 2009 09:23:31 -0700, codefragm..._at_googlemail.com wrote:
>
> >> Hi
> >>   I'm using a global (session) temporary table to break up a section
> >> of a stored procedure that is needed in a number of places. Always for
> >> use in a select.
> >>   If the database crashes, gets rolled back, anything, I don't care
> >> about the contents of that
> >> table. Especially -I don't want redo logs for it-. Is there any way I
> >> can accomplish this?
>
> >> From what I can see so far I should look into: - unrecoverable
> >> - insert /*+APPEND */ into temp_table select... - possibly using a table
> >> variable
>
> >> Nologging is on by default for a tempory table but you still get some
> >> logging
>
> >> Am I on the right track?
>
> >> thanks
>
> > The storage for temporary tables is allocated entirely from the temporary
> > tablespace. Blocks in a temporary tablespace are not proteced by redo so
> > nologging is on by default as you say. Even more than that, there is no
> > "current version" and "read consistent" version of the temporary blocks.
> > Temporary blocks are written from PGA to temporary tablespace in a way
> > very similar to what /*+ APPEND */ hint does. In other words, that hint is
> > for normal insert and has no visible effect on inserting into global
> > temporary tables. To verify that, you can run it with the event 10046,
> > level 12 and see that there are no visible differences in the trace file.
>
> But there is a bit of redo because of the undo:
>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...
>
> (Hopefully that link works...)
>
> Kind regards
>
>         robert
>
> --
> remember.guy do |as, often| as.you_can - without endhttp://blog.rubybestpractices.com/- Hide quoted text -
>
> - Show quoted text -

Its 'a little bit of undo' that seems to be my problem From the asktom site:

Inserting 500 rows,

generates

3,297,752 bytes of redo generated for "insert into perm "...

    66,488 bytes of redo generated for "insert into temp

So if you have a stored procedure that inserts 1000 rows when called and is called 1000 times a minute
thats 132M a minute (66,488/500*1000*1000), 191G a day. Bearing in mind this is temporary table I am
selecting from, I will never want to keep it.

How long should these logs persist from, what are common practices? Received on Mon Sep 07 2009 - 14:56:24 CDT