|
-
1.) You can and should assign every user's default temporary tablespace to a specific tablespace used only for temporary segments, tables, sorts, etc. If you do not do this for every user, sort operations from will occur in the system tablespace. Fragmentation is really a problem when mixing temporary and permanent objects in the same tablespace. Otherwise fragmentation should not be a concern for temporary tablespaces, as I hope will become clear below.
2.) Calling a tablespace temporary does not restrict it from holding permanent objects. You must specifiy the tablespace type as either temporary or permanent when creating or altering the tablespace. The default is permanent.
SELECT CONTENTS FROM DBA_TABLESPACES; to determine what type your temp tablespace is. You can not convert from perm. to temp. if it already contains any permanent segment.
3.) If it ain't already of type temporary, move all permanent objects elswhere and
ALTER TABLESPACE tablespace_name TEMPORARY;
4.) It's best if INITIAL=NEXT and PCT_INCREASE=0 for the tablespace. Drop and recreate if necessary. This relates to the limited duration of temporary objects. Once their usefulness is ended, temporary objects are dropped and the extents allocated to them are returned to the tablespace for later use by other sorts or temporary operations. They are not deallocated. The next time any operation goes looking for space it will find an extent ready and waiting.
5.) Set extent sizes to a value in the 2 to the n series. Also make sure they're an integer multiple of DB_FILE_MULTIBLOCK_READ_COUNT.
5.) Set SORT_AREA_SIZE such that an integer multiple of this value will fit neatly into one extent plus one block for the segment header.
6.) Forget fragmentation in temporary tablespace. The above actions should help considerably.
John Doyle
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
|