Re: Global Temporary Table performance with inserts.

Repeat your no-append tests,
but start a transaction by doing
a small update some where else
before doing the insert. Then:

Check v$transaction.used_urec and v$mystat (your rows from v$sesstat) for statistics "redo size" and "redo entries" before and after doing the main insert. I think you will find that Oracle generates one undo record (urec) per row for the temp table, but one undo record per block for the normal table - hence the significant difference in time.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Frank" <fbortel_at_nescape.net> wrote in message
news:bqsr30$75p$1_at_news2.tilbu1.nb.home.nl...


> Jonathan Lewis wrote:

>

> > There are a couple of oddities with GTTs,

> > array inserts, and volume of undo/redo

> > generated; but I had thought they appeared

> > only in version 9.

> >

> > Are you doing array inserts, and if so, are you

> > doing with the /*+ append */ hint ?

> >

> >

> There is definitely is speed difference:

> This is 9.2.0.4:

> SQL>  create global temporary table t1 on commit preserve rows

>    as select * from all_objects where 1=2

> Table created.

> SQL> create table t2 as select * from all_objects where 1=2;

> Table created.

>

> SQL> set timing on

> SQL> insert into t1 select * from all_objects;

>

> 28904 rows created.

>

> Elapsed: 00:00:02.05

> SQL> insert into t2 select * from all_objects;

>

> 28905 rows created.

>

> Elapsed: 00:00:01.08

>

> T1 (GTT) is about twice as slow as t2.

> Now for the append hint:

> SQL> insert /*+append */ into t1 select * from all_objects;

>

> 28905 rows created.

>

> Elapsed: 00:00:01.06

>

> Same as t2 (regular table)

> -- 

> Regards, Frank van Bortel

>