Re: Global temporary tables performance

Known bug (at least I think is't known, I've mentioned here a couple of times). if you do a snapshot of your session stats before and after each insert, you will find that the GTT insert it generating a redo entry per ROW inserted, whilst the ordinary table generates a redo entry per BLOCK changed.

Still present in 9.2.0.4
Fixed in 10g.

-- 
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


Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG -  v$ and x$
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


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


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


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


"Aviv" <ronensh_at_hotmail.com> wrote in message
news:f44c6b66.0401270757.3ce65cf6_at_posting.google.com...


> Hello,

>

> I'm trying to use a global temporary tables with a bulk insert, and

> the performance for the temporary table is about 5 times LESS than

> those of a real table - shouldent it be the opposite way?

>

> Here is my example:

>

> -- Create real table and a temporary one

> create table realTable( id number(15) );

> create global temporary table tmpTable(id number(15)) ON COMMIT DELETE

> ROWS;

>

> -- Insert 50000 records with bulk operation

> -- into the rea table and the temporary one, and getting the time it

> took

> declare

> tableSize constant number := 50000;

> type ty_numTable is varray (50000) of number(15);

> numTable ty_numTable := ty_numTable();

> st pls_integer;

> en pls_integer;

> begin

> -- Fill up a data array

> numTable.extend( tableSize );

> for i in 1 .. tableSize loop

> numTable(i) := i;

> end loop;

> --

> -- Inserting data to the real table

> st := dbms_utility.get_time();

> forall i in numTable.first .. numTable.last

> insert into realTable values (numTable(i));

> en := dbms_utility.get_Time();

> dbms_output.put_line( en-st );  -- Time taken for the insert

> --

> -- Inserting data to the temporary table

> st := dbms_utility.get_time();

> forall i in numTable.first .. numTable.last

> insert into tmpTable values (numTable(i));

> en := dbms_utility.get_Time();

> dbms_output.put_line( en-st );  -- Time taken for the insert

> end;

>

> The output is:

> 11

> 57

>

>

> Do you have any idea?

>

> TIA.

>    Ronen S.