All About Temporary Tablespace Part II

Oracle sorting Basics

As we know there are different cases where oracle sorts data .Oracle session sorts the data in memory.If the amount of data being sorted is small enough, the entire sort will be completed in memory with no intermediate data written to disk.When Oracle needs to store data in a global temporary table or build a hash table for a hash join, Oracle also starts the operation in memory and completes the task without writing to disk if the amount of data involved is small enough.

If an operation uses up a threshold amount of memory, then Oracle breaks the operation into smaller ones that can each be performed in memory. Partial results are written to disk in a temporary tablespace. The threshold for how much memory may be used by any one session is controlled by instance parameters. If the  workarea_size_policy parameter is set to AUTO, then the pga_aggregate_target parameter indicates how much memory can be used collectively by all sessions for activities such as sorting and hashing. Oracle will automatically assess and decide how much of this memory any individual session should be allowed to use. If the workarea_size_policy parameter is set to MANUAL, then instance parameters such as sort_area_size, hash_area_size, and bitmap_merge_area_size dictate how much memory each session can use for these operations. Each database user has a temporary tablespace designated in their user definition(check through dba_users view) . Whenever a sort operation grows too large to be performed entirely in memory, Oracle will allocate space in the temporary tablespace designated for the user performing the operation. 

Temporary segments in temporary tablespaces which we will call “sort segments”— are owned by the SYS user, not the database user performing a sort operation. There typically is just one sort segment per temporary tablespace, because multiple sessions can share space in one sort segment. Users do not need to have quota on the temporary tablespace in order to perform sorts on disk. Temporary tablespaces can only hold sort segments. Oracle’s internal behavior is optimized for this fact. For example, writes to a sort segment do not generate redo or undo. Also, allocations of sort segment blocks to a specific session do not need to be recorded in the data dictionary or a file allocation bitmap. Why? Because data in a temporary tablespace does not need to persist beyond the life of the database session that created it.

One SQL statement can cause multiple sort operations, and one database session can have multiple SQL statements active at the same time—each potentially with multiple sorts to disk. When the results of a sort to disk are no longer needed, its blocks in the sort segment are marked as no longer in use and can be allocated to another sort operation.A sort operation will fail if a sort to disk needs more disk space and there are 

1.) No unused blocks in the sort segment,and
2.) No space available in the temporary tablespace for the sort segment to allocate an additional extent.

This will most likely cause the statement that prompted the sort to fail with the Oracle error, “ORA-1652: unable to extend temp segment.” This error message also gets logged in the alert log for the instance.It is important to note that not all ORA-1652 errors indicate temporary tablespace issues. For example, moving a table to a different tablespace with the ALTER TABLE…MOVE statement will cause an ORA-1652 error if the target tablespace does not have enough space for the table.

Temporary tablespaces will appear full after a while in a normally running database. Extents are not de-allocated after being used. Rather it would be managed internally and reused. This is normal and to be expected and is not an indication that we do not have any temporary space. If we are not encountering any issue/error related to TEMP then we don't need to worry about this.

There is no quick way or scientific approach to calculate the required TEMP tablespace size. The only way to estimate the required TEMP tablespace size is regressive testing.The information inside the temporay segment gets released, not the segment itself.


Click Here for Next Part III