Temporary tablespace guidelines
Posted by Pavan DBA on March 21, 2013
I thought to share some points a DBA need to follow while configuring temp tablespaces
1. when ever we create a user along with default permanent tablespace, it is beneficial to specify temporary tablespace also. otherwise, oracle will use that permanent tablespace for any sort operations performed by user and this will be costlier
2. it would be better to create multiple temporary tablespaces for multiple users. for example, create 2 temporary tablespaces and assign them to 10 users so that 5 users can use one temp tablespace (numbers are just for understanding)
3. always have tempfiles spread across multiple disks
4. If you are specifying storage parameters, please make sure INITIAL and NEXT values are same. Maintaining uniform extent allocation is always better for temp tablespaces
5. when using storage parameters, please don’t specify INITIAL and NEXT values too small. This will make oracle to create hundreds of small temp segments and SMON will take quite long time (sometimes may be hours of time also) to clean up those segments.
6. If possible, try to load data into table in ascending order of indexed column. If you store data like that, you can use NOSORT clause while creating index and this will improve index creation performance.