RelationalDBDesignRelationalDBDesign


Create Database   «Prev 

Placing Temporary | Rollback Tablespace Files

Placing temporary and rollback tablespace files

It's especially critical to place the temporary tablespace files and the rollback tablespace files on drives of their own if that is at all possible. These files sustain a heavy I/O load, and you want to distribute a database's I/O load over as many disks as possible.
Take the temporary tablespace, for example. The primary use of the temporary tablespace is to sort data. If you are executing a large query against a table and you used an ORDER BY clause to sort that data, Oracle will be reading table data from the datafiles, and writing it to the temporary tablespace. If both your temporary tablespace and your table tablespace are on the same drive, you will lose a lot of performance as the drive has to take turns first doing one thing and then another.
Rollback segments sustain a heavy load during update transactions. Every time you issue a SQL statement that changes data, the original version of that data is written out to a rollback segment. Again, for performance reasons, you don't want rollback segments on the same drives as your datafiles. That load should be spread out over as many drives as possible.
Placement of the USERS and TOOLS tablespaces may be less important. On many of the databases that I manage, these are very lightly used, if they are used at all. In that case, it doesn't bother me to put them both on the same disk.