Re: Star transforms and global temp tables

Yes, we have considered the DISABLE_TEMP_TABLE setting but haven't tested it in production yet.

In my testing it seems that GTTs behave like you have indicated, but I will need some hard information before I will convince them they need really large sort area sizes.

What is your feeling about running temp as a RAW in a data warehouse? We have an Oracle consultant pushing this. Would it be a good idea to have a striped RAW volume with the stripe at about 2-4 times db_file_multiblock_read_count times db_block_size? (for us this about 256K to 512K). This will be a six way stripe on a SYMETTRIX EMC array.

Mike
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1022752877.13963.0.nnrp-13.9e984b29_at_news.demon.co.uk>...
> 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
Received on Thu May 30 2002 - 09:07:22 CDT