Previous | Table of Contents | Next

Page 787

Your first best strategy is to avoid unnecessary sorts when possible. However, when this cannot be done, which is more often the case, sorting needs to be tuned so that the sorts perform optimally. Sorting can take place fully in memory, and that is the desired case. However, it is more likely to spill over to disk sorting, especially with large tables, which can be extremely time-consuming despite even the best physical design of a database.

Your second best strategy is to sort in memory as much as possible and sort on disk only when absolutely necessary. Of course, this implies allocating sufficient temporary disk space (in effect, the TEMP tablespace), and separating this space physically from the rest of the Oracle datafiles, rollback segments, and redo logs.

What Triggers Sorts?

The CREATE INDEX statement obviously requires a sort operation on the index key to enable the building of the B*Tree structure. ALTER INDEX ... REBUILD likewise requires the same sort. However, you may choose to sort the data at the operating system level and then create the index with the NOSORT option. This doesn't usually buy you anything, unless you happen to already have a sorted copy of the data, because you are only trading RDBMS sorting for OS sorting, which isn't much of a trade. Other options include using a fast, third-party sorting utility, such as SyncSort, or using Oracle's Parallel Query Option (PQO) to use SQL*Loader and load the data in parallel, unsorted.

ORDER BY and GROUP BY usually require sorts. However, an ORDER BY on an indexed column will use the already sorted index in most circumstances. To verify this without the need for execution, use EXPLAIN PLAN as previously discussed in Chapter 30, "Application Tuning." The DISTINCT qualifier must use a sorting technique (again, unless it is used on a column already with a unique index) to eliminate duplicate column values. Likewise, a UNION must eliminate duplicate rows. (However, a UNION ALL, by definition, allows duplicate rows; because it doesn't eliminate duplicates, it doesn't require sorting. If primary keys are enforced on the two UNION-able tables, there won't be any duplicates to start with, so the UNION ALL is a recommended substitute for the UNION operation.) INTERSECT and MINUS require some duplicate elimination, though experience nowhere near the burden of a UNION operation. Similarly, IN and NOT IN may require sorting, especially if they are in support of nested subqueries. A join operation will require sorts of whatever tables do not already have existing indexes on the join key. The more usual situation, though, is for tables to be joined on primary keys (already having unique indexes), thereby negating the need for sorting any of the tables. The following list sums up the SQL commands or operators that may trigger sorts:

Page 788

Parameters for Sorts

The two primary init.ora parameters affecting sort operations are

If a sort operation requires more than SORT_AREA_RETAINED_SIZE for an in-memory sort, it attempts to perform the sort within SORT_AREA_SIZE for an external disk sort, allocating a temporary segment in the process. If the sort operation requires further memory, it splits the sort burden into multiple sort runs and allocates multiple temporary segments for that purpose. The server process sorts one segment at a time and returns the merger of the sorted segments as the result. These memory allocations are not stored in the SGA shared pool, except when using MTS. Instead, they are part of the UGA. If you are using MTS, they are part of the SGA shared pool because the UGA is relocated there anyway.

Using EXPLAIN PLAN (see Chapters 29 and 30), you can see that many SQL statements may require multiple sorts within their execution plans. The sort that is currently executing is known as the active sort. A join sort is a sort in support of a join operation. Any active sort requires SORT_AREA_SIZE. Any join sort requires SORT_AREA_RETAINED_SIZE. These settings hold true only for the dedicated server. For PQO, each parallel query server requires SORT_AREA_SIZE. However, two sets of parallel servers can be working at once. So, for PQO, set the following values:

For PQO, the optimal value is 1MB. Higher values haven't yielded better performance. In general, set SORT_AREA_SIZE = SORT_AREA_RETAINED_SIZE, except for MTS, which requires some special considerations.

TIP
For the Multithreaded Server, set SORT_AREA_RETAINED_SIZE much smaller than SORT_AREA_SIZE. As a guideline, you may set the following:

SORT_AREA_RETAINED_SIZE = (SORT_AREA_SIZE / the number of expected concurrent sorts), but not less than 1/10 (SORT_AREA_SIZE).

Temporary (sort) segments must be created when a sort cannot take place fully in memory. That is, as discussed, when the sort operation's memory requirements exceed the setting of SORT_AREA_RETAINED_SIZE, it then requires the allocation of a temporary segment and attempts to work within SORT_AREA_SIZE. A true temporary tablespace (versions greater than or equal to 7.3) segment cannot contain any permanent objects and consists solely of a single sort segment. Temporary tablespaces are created with the CREATE or ALTER TABLESPACE <tablespace_name> TEMPORARY ... syntax. Again, these temporary tablespaces are made up of one segment, created initially by the first sort requiring it. This segment grows in extents as sort concurrency and operation sizes increase.

Page 789

Be careful how you set the INITIAL and NEXT extent parameters. A useful guide may be to set INITIAL = NEXT = (<max size as prescribed by datafile or disk> / <number of expected concurrent sorts>). The number of expected concurrent sorts can be calculated roughly as equal to 2 times the number of concurrent queries. This is a case when you don't want one large extent sized just below the datafile size, which would normally be a good recommendation for general use (permanent) tablespaces, as you will see later in Chapter 32, "Tuning I/O." Also, set INITIAL = NEXT = some multiple of SORT_AREA_SIZE, plus at least one block for the overhead of the extent header because you wouldn't want any single sort requiring more than one extent. At the same time, you can afford to have a few sorts stored in the same extents, due to the random nature of concurrent access. Set PCTINCREASE to 0 because you don't want any surprises, such as increasingly large NEXT extents. Besides, because concurrency again plays a factor here, having equal-sized extents is a fair approach, barring actual sizing techniques, and it works well with random size requirements (no single sort need is too far from the average).

In the SGA, a memory structure known as the Sort Extent Pool (SEP) includes the extents that make up the single sort segments belonging to the temporary tablespaces. When sort space is requested by a process, this pool offers free extents (those that have been allocated and used by an earlier running process, and are now free but not deallocated) to be reused, much like the capability of reusing buffers in the database buffer cache. Furthermore, the V$SORT_SEGMENT contains information such as number of users, extents, and blocks using the temporary sort segments. You can use this to determine efficiency (hits) and help size your extents properly.

TIP
Remember to redefine users' default tablespaces. Use the ALTER USER syntax or Enterprise Manager to do it. TEMP has traditionally been the main temporary tablespace and has often been used to store permanent (processing) objects. However, create as many temporary tablespaces as you might need (such as three or four), and allocate them to different user groups according to varying processing requirements. This can help eliminate contention among users and Oracle itself. As an example, reserve TEMP for Oracle. Then create and assign TEMP2, TEMP3, and so forth to belong to various user or process groups.

Oracle offers the capability of having sorts bypass the database buffer cache. This is called sort direct writes. Of course, you still need SORT_AREA_SIZE bytes, but each sort operation can have its own memory buffers and write them directly to disk. The number of buffers is set by the init.ora parameter SORT_WRITE_BUFFERS (2_8), and the number of buffers is set by SORT_WRITE_BUFFER_SIZE (32KB to 64KB). Each regular (serial) sort operation requires a Sort Direct Writes Buffer of

(SORT_WRITE_BUFFERS x SORT_WRITE_BUFFER_SIZE) + SORT_AREA_SIZE

For PQO, each (parallel) sort requires

((SORT_WRITE_BUFFERS x SORT_WRITE_BUFFER_SIZE) + SORT_AREA_SIZE) x 2 x
degree of parallelism

Previous | Table of Contents | Next