Some performance considerations for temporary tablespaces:
- Always use temporary tablespaces instead of permanent content
tablespaces for sorting (no logging and uses one large sort segment to
reduce recursive SQL and ST space management enqueue contention).
- Ensure that you create your temporary tablespaces as locally managed
instead of dictionary managed (Use sort space bitmap instead of
sys.fet$ and sys.uet$ for allocating space).
- Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery time + other advantages as described above)
- Stripe your temporary tablespaces over multiple disks to alleviate
possible disk contention and to speed-up sorting operations (user
processes can read/write to it directly).
redo log file 的同一组内的不同member 要放在不同的磁盘上。
数据的datafile 和index 的datafile 最好放在不同的磁盘上。
如果是oltp system,请多添加几个redo log group.不减小checkpoint 的等待。