what about a hint of some sort?
"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
|
|
Received on Fri Feb 06 2004 - 12:09:54 CST
