Re: Global Temporary Table performance with inserts.

From: Frank <fbortel_at_nescape.net>
Date: Sat, 06 Dec 2003 18:05:52 +0100
Message-ID: <bqt1p5$mg2$1@news3.tilbu1.nb.home.nl>

Daniel Morgan wrote:

> Jonathan Lewis wrote:
>

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

>
>
> As I recall the original post was about 100 rows and when I run
> with 100 rows I find no detectable difference.
>
> SQL> insert into t1
> 2 select * from all_objects
> 3 where rownum < 101;
>
> 100 rows created.
>
> Elapsed: 00:00:00.04
> SQL> insert into t2
> 2 select * from all_objects
> 3 where rownum < 101;
>
> 100 rows created.
>
> Elapsed: 00:00:00.04
> SQL>
>
> That is after the first run. The first time I run it I see a difference.
> All subsequent tests yield the same result.
>

So taken up with the example, I missed making a point:

- The append hint makes the difference going away.
- 100 rows is so little, you would have to test with more.
- the difference is not 50 times (although tested with 9.2);

   in 8.1.7.4.1, with 100 rows, I had:
SQL> insert into t2 select * from all_objects where rownum < 101; 100 rows created.
Elapsed: 00:00:00.00

SQL> insert /*+append */ into t1 select * from all_objects where rownum <101;

100 rows created.
Elapsed: 00:00:00.00

Proves both you and Jonathan are right
(and the OP, as 50 * 00:00 is 00:00 ;-) )

-- 
Regards, Frank van Bortel

Received on Sat Dec 06 2003 - 11:05:52 CST