Re: Temporary table use and the redo log

From: Randolf Geist <mahrah_at_web.de>
Date: Mon, 14 Sep 2009 04:29:31 -0700 (PDT)
Message-ID: <fc6e8e2f-c4f9-423f-a3ce-7afb365f4eae_at_r33g2000vbp.googlegroups.com>

On Sep 13, 1:15 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> Running on 9.2.0.8 (where the bug is fixed) the insert reports about
> 38 redo records and 32KB of redo size on a global temporary table
> (compared with about 65 and 100KB on a normal table). This is due
> to the way that Oracle can pack multiple changes to a single block
> into a single undo record and redo change vector).
>
> On 9.2.0.4, this test reported 2,000 redo records and about 350KB
> or redo generated because that packing optimisation was missing.
> According to my notes, this bug was fixed in 9.2.0.6 (so my comment
> about "most" versions of 9i was wrong) and 10.1.0.4.  (The optimisation
> was in 8i).

Jonathan,

thanks for the explanation. Your test case seems to refer to conventional DML, does this also apply to direct-path inserts? The test case would generate undo and redo for the primary key index, so I could imagine that it applies there, too, but I'm not sure.

I don't have a pre-9.2.0.8 version at hand (and no 10.1 either), so cannot test myself at present.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Mon Sep 14 2009 - 06:29:31 CDT