Re: Global Temporary Table Scalability Problem

Queries that mix GTTs with permanent tables are prone to producing bad execution plans because GTTs (8.1.7 version) don't have
stats, so Oracle uses some silly defaults. This may be relevant in your case.

One workaround, since you have a fairly static size, is to use the dbms_stats package to create stats on the GTT definition.

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


"william milbratz" <milbratz_at_hotmail.com> wrote in message
news:cee3515e.0402060834.7df3e1e_at_posting.google.com...


> Hi,

>

> We have a complex stored procedure that runs slowly under large

> volumes of data.

>

> After profiling and testing, I found that the use of a single global

> temporary table (i.e. one w/ only 5 records) caused the problem and

> that if I replaced the temp table with a permanent table, the results

> improved dramatically. (20-50x).

>

> Some more info:

> the procedure in question receives as one of its params a

> comma-delimited-list of "principal Ids". This list is usually very

> short (1-6 entries). The procedure parses the list and populates a

> 1field/1key temp table. The procedure then uses that table in a

> complicated query to return the records "which the user has access

> to".

>

> When I came across the fact that the global temp tables performed so

> poorly, I tried using a permanent table to store the "principal IDs"

> (i.e. same key structure).  The query ran 50x quicker .  (This was a

> hack. I'd actually need a way to store these values temporary 'per

> session')

>

> Two questions:

> 1) why these counterintuitive results? Why are global temporary tables

> so slow? under these conditions? Why do they perform differently from

> permanent tables?

>

> 2) is there any way to improve the performance of global temporary

> tables for these circumstances?

>

> thanks,

>

> bill milbratz