Re: Global Temporary Table Scalability Problem

Mark C. Stock wrote:
>
> what about a hint of some sort?
>
> -- mcs
>
> "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
> |
> | 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
> |
> |

In v9 you can use the cardinality hint - but of course, if you're at v9, then you get dynamic sampling anyway.

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"