-
I understand that temp tablespaces are used for large queries, sorting, etc. Once statement is executed or an error occurs the tablespace should free itself.
Does it mean that I'll see different proportions free/used space in my temp tablespace all the time since it is for changing and dynamic or once it reaches its highest point it thinks it is that much full regardless whether it is participating in performing any operations or not.
I personally believe it is the second option but don't understand why.
Thank you.
-
http://www.dbasupport.com/forums/sho...?threadid=6771
http://www.dbasupport.com/forums/sho...?threadid=5600
If, Your TEMP tablespace has PCTINCREASE set to zero, it will not be released as SMON doesn't coalesce the space.
[Edited by sreddy on 03-08-2001 at 05:42 PM]
-
Mary,
There are a couple of possible answers to your question, depending on some settings.
1. If your TEMP tablespace is of type TEMPORARY, you'll allways see its used space as the maximum number of extents used since instance startup
2. If you TEMP tablespace is of type PERMAMNENT and your PCTINCREASE on the tablespace is set to 0 (as it allways should be!), you'll se the same as in 1.), as automatic coalescing wil not be executed (and it should *not* be on temp tablespace)
3. If your TEMP tablespace is of type PERMAMNENT and your PCTINCREASE on the tablespace is set to something different than 0 (as it never should be!), SMON will coalesce it all the time and you will see different amount of used space in it. When there will be no sorting on the system for a longer period of time you'll see no used space in temp tablespace at all.
If your system is set as described in option 3.) then it's time for you to recreate your temporary tablespace more wisely. Depending on the oracle version you are runing not all options might be available, but tem tablespace should be configured with one of the the following characteristics, in descending order:
1. type=TEMPORARY, extent_management = LOCALLY
2. type =TEMPORARY, extent_management = DICTIONARY
3. type =PERMANENT, extent_management = DICTIONARY
No matter which type you choose, PCTINCREASE should always be 0!
-
Thank you Jurij,
May I ask you to explain this paragraph please:
'If your TEMP tablespace is of type PERMAMNENT and your PCTINCREASE on the tablespace is set to something different than 0 (as it never should be!), SMON will coalesce it all the time and you will see different amount of used space in it. When there will be no sorting on the system for a longer period of time you'll see no used space in temp tablespace at all. '
I mistakenly though coalescing is a good thing even for a temp tablespace because it'll combaine free extents into large chunks and it is a good thing.
THANK YOU.
-
OK, I'll try to explain, especialy as I've noted your confusion on this on the other thread you've started. Mind you, English is not my native language, so I know my writing can be confusing sometimes ;).
Suppose you have temp tablespace, comprising of a single 100M datafile. You define INITIAL=NEXT=1M, PCTINCREASE=0 on that tablespace, so that all extents will be of same size.
Initially you have a totaly empty tablespace. When the first sort occures that is to big to fit into SORT_AREA_SIZE, system have to write some sorting information to the temp tablespace. To do that, it have to allocate an extent. As the TS is still empty, it will allocate one, meaning that it will take 1M of the free space and "format" it as an extent. This new extent allocation is quite expensive operation, particullary if the extents are managed in dictionary. Now if the sorting is realy huge, 1 extent will not be enough, so it will have to allocate another 1M extent from the remaining 99M of "unformated" free space. This might go on and on, let's say 40 extents have to be allocated until the sort is done. Remember the system has to perform that expensive extent allocation 40 times!
When the sort is over, there is no more usefull data in those 40 extents, so they are available for other sorts. If you look at tablespace, you'll se 60M of free ("unformated") space, and 40 empty extents that are "formated" and available for immediate use. When next disk sort ocurs, the sistem will first look if there is any empty (but allready existent) extent available. It will grab the first one it finds, not waisting time in alocating new extents from the remaining 60Megs free space. The new sort will use all 40 existent empty extents to perform a sort. Only if there is no more available free extents it will allocate (expensive!) new ones from the remaining 60Megs of free space. So this second sort will perform much better than the first one, as it haven't had to do that expensive allocation 40 times. Any subsequent sort will use those allocated extents if they are not occupied by other simultaneous sorts.
Now suppose you decide to coalesce the tablespace. The first sort that comes arround after that will have to go through expencive extent allocation process again.
So the optimal state for temporary tablespace is when it is fully "fragmented" with equaly sized extents, leaving no free space at all. All those extents are just sitting there, prepared for system to grab them and use them when neccesary, not waisting time in finding free space chunk to allocate a new extent. This same mechanism is more or less the same for rollback tablespace.
So if you want your temp tablespace to be in best shape all the time you have to perform a huge sort to allocate all possible extents right after the database is restarted. Although I personaly doubt it is worth the trouble, it will fill up by itself eventualy...
HTH,
-
THANK YOU JURIJ VERY MUCH.
