Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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>...Received on Thu May 30 2002 - 09:35:32 CDT
>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.
>