Re: Global Temporary Table Scalability Problem

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c00i76$334$1_at_titan.btinternet.com>...
> 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
>

<snip>
>
> "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

Jonathan,

its kind of a "me too" response, but we've had to do the same thing, whereby we have a session setting as to whether to use a permanent table and compute stats on it (8.1.7 with a private synonym in front of the obscure table name) vs. use a GTT (9.2 with optimizer_dynamic_sampling=2).

I don't have any examples in front of me, but I'll put some together for next week at RMOUG.

Paul Received on Fri Feb 06 2004 - 17:13:53 CST