Re: Temporary table use and the redo log
Date: Sun, 13 Sep 2009 12:15:44 +0100
"Randolf Geist" <mahrah_at_web.de> wrote in message
On Sep 11, 7:05 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> But there are various bugs in different versions of Oracle that
> cause problems. Most versions of 9i, for example, lose the
> bulk processing optimisation of insert as select;
interesting, can you elaborate a bit about the "bulk processing optimisation" bug in 9i? Is this specific to Global Temporary Tables? What does "lose" the bulk processing optimisation mean? Fall back to conventional DML or reduced performance comparable to single row/PL/ SQL bulk processing, similar to the DML error logging effect?
Oracle related stuff blog:
create global temporary table t1 (
constraint t1_pk primary key (n1,n2)
on commit preserve rows
insert into t1
Running on 18.104.22.168 (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 22.214.171.124, 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 126.96.36.199 (so my comment about "most" versions of 9i was wrong) and 10.1.0.4. (The optimisation was in 8i).
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Sun Sep 13 2009 - 06:15:44 CDT