Re: Star transforms and global temp tables

Two lateral thoughts:
a) the star_transformation_enabled parameter has three

    values, TRUE, FALSE, and something like DISABLE_TEMP_TABLE     (if I've got the last one wrong, which I usually do) then the error     message will give you the correct value. Using the last option     MAY improve things in your case.

b) As with grouping sets and subquery factoring, the temp tables

    may be a special case of temp tables which have no impact on     the data dictionary - furthermore, I believe global temp tables should     only be written to disc when they overflow sort_area_size, so perhaps     you can bypass the cost of the issue by making more sort memory     available.

Which version of Oracle is this ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Mike Ault wrote in message
<37fab3ab.0205291055.4beb8d1f_at_posting.google.com>...


>In our explain plans for star transformations we are seeing the use of

>global temporary tables. Since GTTs are disk structures in the

>temporary tablespace this is slowing down our joins. The undoc

>parameter "_temp_tran_block_threshold" seems to setting the threshold

>for this to occur at 100 blocks. Anyone seen this parameter changed or

>modified to increase the threshold? With 5 free gigabytes of memory we

>would much rather do the work there then on disk...

>

>Mike Ault