Using Temp tablespace Groups

I wanted to share the experience on using temp tablespace groups that I proposed on our last staff meeting.

Abstract:

Test advantages of using temporary tablespace group.

Steps performed for testing:

• Created three temporary tablespaces each of 8GB with AUTOEXTEND OFF and assigned them to a single tablespace group. Now the tablespace group is of total size 8X3=24GB

• Created a session and ran a SQL query which will use temp tablespaces. Also making sure this query will need space more than the size of one tablespace (i.e.) 8GB. I used a order by on a big table.

• Created multiple sessions from a single database user to use temporary tablespace group.

• Created multiple sessions of different users to use temporary tablespace group.

Result Obtained:

• Single SQL query could not span multiple temporary tablespaces for sorting. Temporary segments do not span tablespaces. Although many blogs says it will and I did not find that happen.

• Different sessions of same user used different tablespaces in the group at the same time. (i.e.) Each session with its own SQL query runs on different tablespaces.

• Different user sessions used different tablespaces in the group.

Conclusion:

• Multiple default temporary tablespace will ease maintenance (i.e.) in the event of single temp tablespace gets corrupted, we can just remove that from the tablespace group and drop the same and create a new temp tablespace and assign the same in tablespace group.

• Huge temp waits for read and writes can be minimized.

• The I/O gets distributed when the having multiple tempfiles.

• For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.