Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Cool Unanswered: Oracle 81 temporary database size problem

    Hello all
    I hope you guys can help me. I'm kinda confused. I'm NOT into databases much, but I found myself stuck headdown in a crisis situation.
    There are some sites with Oracle 81 db running weblogic on windows 2k platform. A few days back, the application showed signs of abnormalities. Upon initila investigation, I noticed a 55 GB temp01.dbp file in the database folder. Its usual size is around 450 - 550 Mbs.
    Can anyone help me look into this? I'd like to know WHY and HOW such a huge size file is created in oracle.
    I'm a newbie, so plz be gentle
    Regards
    Saj

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    issue "alter tablespace temp default storage pctincrease 0);" which will free all unused extends (a workaround ...)
    then

    "alter database datafile 'filespec of temp device' resize 550MB " or the size you want

    you need the right permissions/grants to do that ...

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: Oracle 81 temporary database size problem

    Originally posted by sajeel
    Hello all
    I hope you guys can help me. I'm kinda confused. I'm NOT into databases much, but I found myself stuck headdown in a crisis situation.
    There are some sites with Oracle 81 db running weblogic on windows 2k platform. A few days back, the application showed signs of abnormalities. Upon initila investigation, I noticed a 55 GB temp01.dbp file in the database folder. Its usual size is around 450 - 550 Mbs.
    Can anyone help me look into this? I'd like to know WHY and HOW such a huge size file is created in oracle.
    I'm a newbie, so plz be gentle
    Regards
    Saj
    Temp tablespace(s) is(are) used for
    - when there's a big sort that doesn't fit in memory (ORDER BY, GROUP BY ..)
    - when you create/rebuild an index (that requires sorting of course)
    - for GLOBAL TEMPORARY tables
    - many other things

    You have probably a temp file which is in autextend mode, with no maxsize specified.

    What are the "abnormalities" ?

  4. #4
    Join Date
    Oct 2003
    Posts
    3
    thanks a lot for all the help
    Umm...just to be on the safe side, how do I find the settings for t he temp tablespace currently running on my servers? I wouldnt like to do anything that I cant revert :P

    And by abnormalities, I mean that the application would just kinda hang when I ask it to search something for me.

    Another query, is it OK to just delete the temp01.dbf file if it grows too large? I know it sounds silly....but if this is the cause (which Im not too sure yet), wouldnt removing it solve the problem?

    My other hunch is, that someone ran a stupid query that made the oracle go in a loop or something...thereby giving strange results. You buy it?

    Any help would be highly appreciated.
    Regards
    Saj.

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by sajeel
    thanks a lot for all the help


    And by abnormalities, I mean that the application would just kinda hang when I ask it to search something for me.

    My other hunch is, that someone ran a stupid query that made the oracle go in a loop or something...thereby giving strange results. You buy it?

    Any help would be highly appreciated.
    Regards
    Saj.
    A SQL statement never goes in a loop, it may use a lot of resources (like temporary segment) instead; anyway some gigas seems too high. PL/SQL may go in a loop, yes.

    A loop may be the cause, I would say.

    But perhaps ... have you changed the SORT_AREA_SIZE parameter instance-wide recently, or any other system parameter ?

    And, extending a datafile is a relatively expensive operation, since Oracle has to "format" it, so it may use a lot of disk and cpu resources.

    My advice - get rid of autoextend - size the datafiles to a reasonable fixed amount; that way the session that goes in a loop gets an error, without causing troubles to everyone.
    If you need more space, you can add another datafile to the temp tablespace later - we do it all the time.

    In production, we have autoextend on nowhere.

    HTH
    Al

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It doesnt matter if you have autoextend on, add another datafile or resize a datafile they all have to format the new area.

    We have autoextend on on all our production instances since
    1) it is better to have a slow system for a short period of time than lose customer data due to a datafile being full.
    2) I cannot accurately forecast growth always so rather than get it wrong, autoextend will cope with it automatically with the proviso that MAXSIZE must be set to sensible limit.
    3) datafiles should be sensibly sized in the first place so autoextend should only occur when your forecasted growth is wrong i.e. so your systems isnt burdened by constantly autoextending datafiles.
    4) With autoextend on your datafiles will be sized close to the optimal amount i.e. with minimal wasted space, which is important if you dont have loads of diskspace available.
    5) It reduces the burden of monitoring tablespaces/diskspace BUT it doesnt mean you can just forget about it.

    Alan

  7. #7
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by AlanP
    It doesnt matter if you have autoextend on, add another datafile or resize a datafile they all have to format the new area.

    We have autoextend on on all our production instances since
    (snip)

    Alan
    Absolutely correct - just another approach; autoextend is there for some reason ...

    In reality we (better, my customer) have some monitoring software, partially automatic and partially attended by people in a 24x7 big room, that, among other things, signal when a tablespace is going to be full. Then we add the datafile in off-peak hours, AFTER having confirmed that it is needed by the normal growth and not by some program anomaly.

    Personally, i prefer to get an error if i have done things wrongly, rather than having a big datafile that I have to shrink back later - but again, it's just a different way to operate. IMHO there's not a "right" and "good" way, only a way that fulfills your (unique) requirements with the (unique) resources you have available.

    al

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I always seem to get user anomolies : ) and my datafiles only ever fill up at 3AM when I'm oncall : (

    Alan

  9. #9
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by AlanP

    my datafiles only ever fill up at 3AM when I'm oncall : (

    Alan
    That's because they love you and would not be happy if another DBA took care of them ;-)

  10. #10
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by alberto.dellera
    That's because they love you and would not be happy if another DBA took care of them ;-)
    Hello
    Thanks for all you guys trying to help me out here
    Um...I'm attaching a screenshot of something you (i already do) would find very interesting. This is another server at another location. The used temp size is in MBs and the space taken up is in GBs

    Is there a ...er...no-risky, non-destructive way to correct this? And could this be the cause why an application may not perform searches (sleeps during it) and the oracle instance service takes forever to stop thru the service manager in windows 2000.
    I understand that the service stopping uses shutdown immediate or normal... and the server takes about er... 1 hour to shut down the service (I noticed this today). I guess there are pending transactions in the queue or something. How do I go on about this thingy?
    Thank you in advance.

    Regards
    Saj
    Attached Thumbnails Attached Thumbnails tablespace_stats_pri-server_edited.jpg  

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Theres nothing wrong with your tempfile, it looks like the TEMP datafile is about 9GB but only about 30M is actually used. Seeing as it is mostly empty do an alter database datafile '<name of file>' resize <size you require> if you want to make it smaller.

    I would watch your other tablespaces though as they are very full. Either resize them of put autoextend on with sensible max limits unless they are read only.

    Alan

  12. #12
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    teporary not local ?

    I've noticed that your temp tablespace is dictionary-managed and not locally-managed: that's strange, since LMT were invented especially for temp tablespaces.

    That is what i would correct first thing - if dictionary, any allocation of a new extent goes though a special kind of recursive transaction called Space Transaction, and there is only one active at a time - so if more than one user asks for a new extent, a waiting line is formed. Besides, searching the space for the new extent into the data dictionary is very expensive, so even if only one user asks for a new extent, it may wait a long time.

    If local, the extents are managed through a bitmap located locally in the tablespace itself - very very fast, and no ST at all.

    The suggested size for the uniform extent is equal to SORT_AREA_SIZE plus some overhead (the size of a couple of blocks, but i'm not sure).

    Besides - what's your SORT_AREA_SIZE setting ? If it's too low, you will sort to disk AND request a lot of extents in temp - both killing your ORDER BY or GROUP BY queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •