Re: Global Temporary Table

On 7 Sep 2004 05:33:19 -0700, lalydba_at_free.fr (Laly) wrote:

>Hello,
>
>
>We are running on Oracle 8.1.7.4 on Unix (AIX 4.3.3.0)
>
>I'm facing a problem with a global temporary table (on commit preserve
>rows) : it is going through the loop :
>- delete all
>- insert
>- update
>
>At the beginning of the procedure everything is going fine. I can see
>how the process is going on in v$session.
>
>After half an hour, the speed begins to decrease a lot and there are
>lot of "db file scattered read" events pointing to my temporary table.
>It is true that there are lots of FTS on this temporary table, but its
>content is very small (about 20 rows at each step of the loop)
>
>Our temporary tablespace is PERMANENT.
>
>Now the process is nearly stucked.
>
>What can we do ? What can be the cause of this problem ?
>
>
>Regards,
>
>Laly.

Hi Laly,

Maybe there are only 20 rows to process, but what if these rows are distributed over e.g.100Mb of blocks due to inefficient space usage when many rows are deleted from your temp. table ?

The high amount of physical reads suggests something like this, but please provide the code and a proces description for further analysis.

FTS is the only option with global temp. tables, as indexing is not possible.

• Kenneth Koenraadt

. Received on Tue Sep 07 2004 - 12:19:20 CDT