DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: TEMP Tablespace

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    By the way, are you using automatic memory management (pga_aggregate_target)?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Might be worth opening a tar on it -- at least you'd learn whether this is actually expected behaviour
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jul 2003
    Posts
    323
    What is your:
    db_block_size & db_file_multiblock_read_count ?

  8. #8
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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
  •  


Click Here to Expand Forum to Full Width