Taking a break from the tuning trio-logy, this time I wanted to share my views on addressing something in Oracle database, that I have come across multiple times in my professional career. And every time I came across this question I always had the same answer. In-fact, when one is trying to enhance or troubleshoot performance in the database realm, they have only 2 options:
- Increase available hardware resources
- configure and maintain the database as per the best practice for the business
Looking at the options available, the 1st option is always tempting one because it is easy. But it may not be affordable always, and in some point of time can backfire as well. That is why going about the 2nd option should always be the 1st line of action to be taken. In the same context, this time I wanted to talk about managing the Temporary Tablespace in Oracle as per the best practice.
So, what is this temporary tablespace exactly? To be simple, A temporary tablespace contains transient data that persists only for the duration of the session. Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. Hmmmmmmm…….is released only at shutdown, so this would mean that the temporary tablespace keeps growing. And if it keeps growing then we might run out of space if we do not configure the tablespace correctly. For example, while creating the temporary tablespace, if the clause tempfile is not used then a datafile is created for the use of temporary tablespace which is not a good way to start the database configuration. So even though the tablespace is called temporary tablespace, because of the presence of a datafile it would actually be a permanent tablespace marked as temporary.
So how does one configure a temporary tablespace for optimum performance? Lets look at some best practices that can be followed:
- Use locally managed temporary tablespace
Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database. Locally managed temporary tablespaces are uniform extent tablespaces
- Guidelines for choosing extent size while creating the temporary tablespace
For DSS, OLAP applications involving huge sorts, hash joins or when large temporary LOBs are predominant then choose between 1M-10M
When,Global temporary tables are predominant and amount of data loaded is small or when the application is predominantly OLTP then choose between 64K or multiple
- For a RAC, use single temp tablespace for entire database
- Create temporary tablespace group
One or more temp tablespace can be assigned to a tablespace group, and a user or a database can be assigned to an entire group
- Place the tempfile for the temp tablespace on a separate disk device other than the root partition and the disk where our buisness data resides.
- Specify a TEMPORARY TABLESPACE for each user (schema). If one is not specified, Oracle defaults to SYSTEM, which is almost guaranteed to have a negative impact on performance.
- Finally, V$TEMPSEG_USAGE can be used to monitor space usage and workload distribution, which gives us a clear picture of how the temporary tablespace is being used and we can pinpoint the culprit queries that are extensively using the temp tablespace. This way, we can tune those queries and enhance database performance
And that was a shorthand on how to mange the temporary tablespace……..permanently. Again, these are just guidelines to be followed. And before implementing it in the production environment, it is always best to test the configuration as per one’s business needs and then roll-out into the production.