Re: Star transforms and global temp tables

If you are going to write to TEMP in a big way, then raw is probably a good idea - on the other hand if you do hammer an EMC with lots of TEMP writes, then the side effects on the rest of the system may be severe. I'm not too sure that I would restrict it to just six spindles either - but that's something that I shouldn't really say without knowing the whole I/O strategy for the system.

db_file_multiblock_read_count applies to scattered reads, not direct reads, so is likely to be irrelevant for TEMP writes. I haven't actually checked which of the (many) multiblock I/O parameters that gets used for this variant of writing to TEMP, perhaps it is the
_db_file_direct_io_count which defaults to 1MB, I think you need to consider typical effects of hashing and sorting, as well as degrees of parallelism before trying to decide a stripe size - especially if you can't be sure that temp table activity is going to go to disc anyway.

One other thought, by the way, you might want to look at the undocumented hint cache_temp_table. This might be an alternative mechanism to stop large dimensions from being turned into temporary tables - or at least not being written to disc if they are.

--
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.0205300607.c677a7b_at_posting.google.com>...


>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.

>