Re: Global Temporary Table

From: Laly <lalydba_at_free.fr>
Date: 8 Sep 2004 00:31:55 -0700
Message-ID: <6f8684ef.0409072331.21188c4d@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 ?

Best regards,

Laly. Received on Wed Sep 08 2004 - 02:31:55 CDT