-
TEMP Tablespace
Hi,
I'm working on a DWH project usign Oracle 9.2 on Sun Solaris.
Our temp tablespace is often used because our queries do a lot of huge joins and sorts/group by. I noticed that it often happens that from our 16 temp datafiles (16GB each, extent size of temp tbs is 16MB) only 2 are used at the same time. However if I use temp datafiles of only 512 MB with an extentsize of 128MB the IO load is much beter spread over all files. When large queries are executed the perfomance increase is about 30%-50%
So my question is: do you have any idear why Oracle is not spreading the IO by itself over the 16 temp datafiles? Why do I have to force oracle to do that by specifing small temp datafiles with large extens?
Thanks for any feedback
Mike
-
If it only needs to use one or two datafiles, it will.
By making the files smaller and extent sizes bigger you are making Oracle extend itself into the other datafiles quicker
-
You are using LMT's, aren't you?
I would think that a smaller extent size would be beneficial, even down to 4Mb -- the extents ought to be getting allocated in a round-robin fashion on each datafile. Is this not the case?
-
By the way, are you using automatic memory management (pga_aggregate_target)?
-
Thanks for your feedback,
Yes I'm using automatic memory management and yes I'm using LMT.
I would think that a smaller extent size would be beneficial, even down to 4Mb -- the extents ought to be getting allocated in a round-robin fashion on each datafile. Is this not the case?
I was also thinking so but it seems that in my case it's not doing that when having somthing like 20 session using the temp tbs only 2 files (out of 17) are used. Also it seems that the OS dosen't allow all session to write at the same time to a same temp datafile. By reducing the temp files size and increasing the extensize I force Oracle to use much more datafiles. With a tempfile size of 512MB and an extent size of 128MB I got a maximum of 4 sessions using the same temp datafile. Doing that I have been able to increase the response time of several large query by 30%-50% (eg. from 50 minutes down to 35 minutes).
I dont understand why Oracle doesnt spread the IO ,or extent allocation, by it's own over all datafiles.
Last edited by mike9; 03-16-2004 at 03:59 PM.
-
Might be worth opening a tar on it -- at least you'd learn whether this is actually expected behaviour
-
What is your:
db_block_size & db_file_multiblock_read_count ?
-
I read in the docs that this is normal behaviour when using LMT's with uniform extent size. With data dictionary ts's, Oracle tries to fill them up 'round-robin'-style.
I think Oracle wants to fill up a datafile before it looks for another one with free space. Expanding datafiles and/or searching for datafiles containing free space is "time consuming". (just my thoughts)
HTH,
Erik
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
Originally posted by efrijters
I read in the docs that this is normal behaviour when using LMT's with uniform extent size. With data dictionary ts's, Oracle tries to fill them up 'round-robin'-style.
Have you got a reference for that? I believe that it's the other way around.
-
I'll try to locate the document...
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|