Oracle Sorting Parameters

Setting the parameter sort_direct_writes to TRUE, and thereby bypassing the need for the sort to contend for free blocks in the buffer cache will improve sorting performance by up to 50 percent. Of course, this is done at the expense of additional memory with the SGA. This movement towards segmenting the buffer into individual components can dramatically improve response times in Oracle.

Oracle tries to perform sort and hash operations in memory (PGA), but if a sort operation is too large to fit into memory, it uses the temporary tablespace to do the work. It is important to understand that even a single large sort operation has the potential to use up an entire temporary tablespace. Since all database sessions share the temporary tablespace, the session that runs the large sort operation could potentially result in other sessions receiving errors due to lack of room in that tablespace, or fail itself. Once the temporary tablespace fills up, all SQL statements that seek to use the temporary tablespace will fail with the

ORA-1652: unable to extend temp segment error.

New sessions may not be able to connect, and queries can sometimes hang and users may not be able to issue new queries. You try to find any blocking locks, but none exists. If the temporary tablespace fills up, transactions will not complete. If you look in the alert log, you will find that the temporary tablespace ran out of space. Operations that use an ORDER BY or GROUP BY clause frequently use the temporary tablespace to do their work. Large hash joins also need the temp space while they are execting. You must also remember that creating an index or rebuilding one also makes use of the temporary tablespace for sorting the index.

Oracle uses the PGA memory for performing the sort and hash operations. Thus, one of the first things you must do is to review the current value set for the PGA_AGGREGATE_TARGET initialization parameter and see if bumping it up will help if you happen to see a lot of I/O occurring in the temporary tablespace. Nevertheless, even a larger setting for the PGA_AGGREGATE_TARGET parameter does not guarantee that Oracle will perform a huge sort entirely in memory. Oracle allocates each session a certain amount of PGA memory, with the amount it allocates internally determined, based on the value of the PGA_AGGREGATE_TARGET parameter. Once a large operation uses its share of the PGA memory, Oracle will write intermediary results to disk in the temporary tablespace. These types of operations are called one-pass or multi-pass operations, and since they are performed on disk, they are much slower than an operation performed entirely in the PGA.

If your database is running out of space in the temporary tablespace, you must increase its size by adding a tempfile. Enabling autoextend for a temporary tablespace will also help prevent out of space errors. Since Oracle allocates space in a temporary tablespace that you have assigned for the user performing the sort operation, you can assign users that need to perform heavy sorting a temporary tablespace that's different from that used by the rest of the users, thus minimizing the effect of the heavy sorting activity on overall database performance. Note that unlike table or index segments, of which there are several for each object, a temporary tablespace has just one segment called the sort segment. All sessions share this sort segment. A single SQL statement can use multiple sort and hash operations. In addition, the same session can have multiple SQL statements executing simultaneously, with each statement using multiple sort and hash operations. Once a sort operation completes, the database immediately marks the blocks used by the operations as free and allocates them to another sort operation. The database adds extents to the sort segment as the sort operation gets larger, but if there is no more free space in the temporary tablespace to allocate additional extents, it issues the

ORA-1652:unable to extend temp segment error.