Re: Global Temporary Table

lalydba_at_free.fr (Laly) wrote in message news:<6f8684ef.0409072331.21188c4d_at_posting.google.com>...
> Now we recreated our temp tablespace as a DMT with tempfiles.
>
> Here is my table :
> create global temporary table test (
> x number,
> y number,
> z char(100)
> )
> on commit preserve rows;
>
> And my procedure :
>
> create or replace procedure p_test as
> l_start number;
> l_end number;
> begin
> l_start := dbms_utility.get_time;
> for i in 1..10000
> loop
> if i mod 100 = 0 then
> l_end := dbms_utility.get_time;
> dbms_output.put_line(i || '=>' || round((l_end-l_start)/100) || ' sec');
> l_start := l_end;
> end if;
>
> delete test;
>
> for j in 1..20
> loop
> insert into test(x)
> values (j);
> end loop;
>
> update test
> set y = x,
> z = '1';
>
> end loop;
> end;
>
> The output is :
> 100=>2 sec
> ...
> 1000=>3 sec
> ...
> 1400=>4 sec
> ...
> 1800=>5 sec
> ...
> 2600=>6 sec
> ...
> 3100=>7 sec
> ...
> 3800=>8 sec
> ...
> 4000=>9 sec
> ...
> 4800=>10 sec
> ...
> 5500=>11 sec
> ...
> 9900=>17 sec
> 10000=>18 sec
>
> My thought is that if space was reused, the GTT would stay in a reasonable size
> instead of spending a lot of time doing the full table scan.
>
> I have to say that if we commit inside the loop every 500 step, we don't have
> this performance issue.
>
> Can somebody reproduce this problem and explain what is happening here, please ?

Maybe I'm missing something because my brain is fried, but don't we have:

2 seconds to do 100
1 second to do 800
1 second to do 400
1 second to do 400
1 second to do 800
1 second to do 500
1 second to do 700
1 second to do 200
1 second to do 800
1 second to do 700
1 second to do 200
6 seconds to do 4400, or over 700.

Getting rid of the first and last lines still gives over 500/second average.

I don't see any pattern of increasing slowdowns as posted, the 200's might be wait-for-checkpointing or wait-for-DMT-segment-creation or something. Get OEM to give you a chart of the waits.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20040908/news_1b8invesco.html