10G Temporary Tablespace Groups

Temporary Tablespaces
When administrators discuss transient data they focus their conversation on either temporary tables or sort operations. Temporary tables differ from their permanent counterparts in that they are only available for the duration of the user's transaction or session.

But data generated by sort operations is by far the most common topic of discussion. Administrators new to Oracle quickly learn that efficient management of sort data is critical to achieving and maintaining good query performance.

Sort data is generated explicitly when the user executes a statement that contains an ORDER BY or a GROUP by and implicitly generated by the database for joins, bitmap merges, index creation operations, etc..

Sorting in Memory
The key to fast sort operations is to sort as much data as possible in memory. As I used to tell my students in my Oracle Admin I classes, "memory fast (both hands raised high)- disk slow (both hands pointing to the floor)." Silly, but it worked. Depending on the release, Oracle provides a different set of parameters to allocate memory for sort operations.

In releases previous to Oracle9i, DBAs were required to manually set the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAMP_MERGE_SIZE initialization parameters to control the amount of memory allocated to sort operations. The SORT_AREA_SIZE parameter allocated memory specifically for sort operations generated by user SQL statements.

It is important to note that SORT_AREA_SIZE defined the amount of memory allocated to sort operations for each individual user. It was not shared amongst all users. User processes requiring sort work areas did not immediately consume all of the memory allocated to them by the SORT_AREA_SIZE parameter. They allocated chunks of memory until they reached the total amount of memory allocated to them by SORT_AREA_SIZE. If the user's sort work areas consumed all of the memory allocated to them by SORT_AREA_SIZE, they began writing sort data to the temporary tablespace that was assigned to them.

We know that each platform has a finite amount of system resources (disk, memory and CPU) available. The problem with defining memory areas at a per user level was that the total amount memory allocated to sort operations at any one time was very dynamic by nature. The more users that required sort work areas, the more total memory Oracle consumed. That's the one thing I like about Oracle, it'll consume every system resource it can if you let it. That's what makes tuning Oracle exciting, I think.

Back to the discussion… A large SORT_AREA_SIZE parameter setting that worked wonderfully when only a few users were sorting often impacted the database server's performance when dozens of users were sorting. As a result, DBAs were often very conservative when setting the SORT_AREA_SIZE parameter. Better to let users sort to disk than lock up an entire box.

In Oracle9i, Oracle introduced two new parameters called PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY to automate and improve the process of allocating memory for sort work operations.

PGA_AGGREGATE_TARGET defines a global pool of memory that all users performing sort operations share. Both PGA_AGGREGATE_TARGET and WORK_AREA_SIZE_PARAMETER must both be set to activate automatic management of sort work areas.

WORK_AREA_SIZE_POLICY can be set to 'MANUAL' or 'AUTO'. Setting it to 'MANUAL' disables the automatic adjustment of sort areas while 'AUTO' enables the automatic adjustment of sort work areas in memory. The DBA then must set the various parameters that allocate sort areas manually.

When the WORK_AREA_SIZE_PARAMETER is set to 'AUTO', Oracle will automatically allocate and adjust memory used for sorting to ensure that each user's sort operations are fully optimized without exceeding the value defined by PGA_AGGREGATE_TARGET.

The two key words in that last sentence were "automatically adjust". Oracle will adjust the amount of memory allocated to each, individual user based on the amount of total memory available in the global pool defined by PGA_AGGREGATE_TARGET. The more memory that is available, the more memory the individual user gets.

Oracle's Metalink website has numerous articles on the monitoring and administration of both manual and automatic sort work areas. If you are interested in furthering your education on how to ensure that your database environment is configured optimally for sorting, start with the Oracle documentation for your release and then move on to Metalink.

Sorting to Disk
When your user populations exhaust the amount of memory available for sorting, Oracle will begin to write sort data to disk. Oracle introduced temporary tablespaces in 7.3, but true temporary tablespaces (created using the CREATE TABLESPACE….TEMPFILE command) were introduced in Oracle8i.

The TEMPORARY keyword in the CREATE TABLESPACE command tells Oracle to create a tablespace that manages segments differently than those stored in permanent tablespaces. When you create an object in a permanent tablespace, it allocates a segment and extents within that segment. A temporary tablespace contains a single segment that all users share. Although multiple transactions share the same sort segment in a temporary tablespace, they do not share extents. The first sort operation allocates the sort segment. Future sort operations do not incur the additional overhead required by segment allocation, which improves performance.

The temporary tablespace should be created as locally managed and with a uniform size extent allocation. You'll see an example of the syntax in the demo contained at the end of this blog.

The first time I created a true temporary tablespace that used the TEMPFILE parameter, I immediately went to V$DATAFILE and DBA_DATA_FILES to view the information the data dictionary provided on them. I said to myself, "Hey, this database has a bug already - where's the information on my datafile?" I should have read the new features manual first, I think. It took me a while to figure out that tempfile information is recorded in v$TEMPFILE and DBA_TEMP_FILES. If you are looking for information on tempfiles, those are the views that you'll need to access.

If you are looking for information on sort operations, Oracle provides the V$SORT_SEGMENT and V$TEMPSEG_USAGE (Oracle 9.2 and above) views to provide that information. You'll need to remember that if your sorts are contained entirely in memory, you won't see any entries in these views. The sorts must overflow to disk. You can also run this statement to see how much data is being sorted in memory vs disk:

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('sorts (memory)', 'sorts (disk)');

You assign temporary tablespaces to accounts during their creation. You can also use the ALTER USER DDL statement to change an account's temporary tablespace. One of the problems in releases prior to Oracle9i was that if administrators did not explicitly assign a temporary tablespace during account creation, Oracle assigned the System tablespace as the user's temporary tablespace. This oversight allowed user processes to create sort work areas in the System tablespace, which impacted database performance and had a tendency to fragment the tablespace.

In Oracle9i, administrators could create a default temporary tablespace at the database level. Accounts that were not explicitly assigned a temporary tablespace during creation were assigned the default temporary tablespace defined at the database level.

Temporary Tablespace Groups
Oracle10G allows administrators to create temporary tablespace groups, which can consist of one, or more, temporary tablespaces. There is no upper limit on the number of temporary tablespaces that can be a member of a tablespace group.

Tablespace groups allow administrators to provide a large pool of disk storage to users that sort large volumes of data. With data warehouses continuing to increase in size, it is easily understandable why this amount of disk space is often required.

It is interesting to note that there is no CREATE TEMPORARY TABLESPACE GROUP DDL statement. The group is implicitly created when the first tablespace is assigned to it. Administrators have the option of assigning a temporary tablespace to a group during tablespace creation or by altering the tablespace at a later time. The group name cannot match an existing group or tablespace name.

Oracle provides the DBA_TABLESPACE_GROUPS view to provide information on tablespace groups. In addition, temporary tablespace groups can also be assigned as the database's default temporary tablespace.

A temporary tablespace can be a member of a group, which is assigned to users and also be assigned as a single temporary tablespace to other users.

The benefits that tablespace groups provide are:

• A single user that is assigned to a tablespace group is able to use multiple tablespaces in different sessions.
• Child sessions in a single parallel operation are able to use multiple tablespaces.
• Enables multiple tablespaces to be defined as the default temporary tablespace at the database level. Oracle will assign users to the tablespaces in the group in a round-robin fashion.

DEMO
Let's finish the blog with a brief demo.

I hope you enjoyed this blog on temporary tablespaces and temporary tablespace groups. Thanks for reading,

Chris Foot