Objectives

After completing this lesson, you should be able to

do the following:

Identify the SQL operations that require sorting

Ensure that sorting is done in memory where possible

Reduce the number of I/Os required for the sort runs

Allocate temporary space appropriately

Operations Requiring Sorting

Index creation

Parallel insert operation involving index maintenance

ORDER BY or GROUP BY clauses

DISTINCT values selection

UNION, INTERSECT, or MINUS operators

Sort-merge joins

ANALYZE command execution

Sort Process

Sort Area and Parameters

The sort space is in:

Sort Area and Parameters

Each parallel query server needs SORT_AREA_SIZE.

Two sets of servers can write at once, so:

Calculate SORT_AREA_SIZE × 2 × degree of parallelism.

Add SORT_AREA_RETAINED_SIZE × degree of parallelism × number of sorts above two.

Sort Process and Temporary Space

A temporary tablespace is created with the command CREATE TABLESPACE ... DATAFILE ... TEMPORARY

Temporary Space Segment

Is created by the first sort operation

Extends as demands are made on it

Comprises extents, which can be used by different sort operations

Is described in the SGA in the sort extent pool (SEP)

Tuning Sort Operations

Avoid sort operations whenever possible

Reduce swapping and paging by ensuring that sorting is done in memory where possible

Reduce space allocation calls: allocate temporary space appropriately

Avoiding Sort Operations

Avoid sort operations whenever possible by:

Using NOSORT to create indexes

Using UNION ALL instead of UNION

Using index access for table joins

Creating indexes on columns referenced in the ORDER BY clause

Selecting the columns for analysis

Using ESTIMATE rather than COMPUTE for large objects

Diagnostic Tools

V$SYSSTAT

Diagnostics and Guidelines

The ratio of disk sorts to memory sorts should be less than 5%.

Increase the size of SORT_AREA_SIZE if the ratio is greater than 5%.

Monitoring Temporary Tablespaces

Temporary Tablespace Configuration

Summary

In this lesson, you should have learned how to:

Avoid sort operations

Size SORT_AREA_SIZE for sorting in memory

Size SORT_MULTIBLOCK_READ_COUNT to reduce the number of I/Os

Configure TEMPORARY tablespaces


		After completing this lesson, you should be able to
		do the following:
		Identify the SQL operations that require sorting
		Ensure that sorting is done in memory where possible
		Reduce the number of I/Os required for the sort runs
		Allocate temporary space appropriately


		Index creation
		Parallel insert operation involving index maintenance
		ORDER BY or GROUP BY clauses
		DISTINCT values selection
		UNION, INTERSECT, or MINUS operators
		Sort-merge joins
		ANALYZE command execution


	Each parallel query server needs SORT_AREA_SIZE.
	Two sets of servers can write at once, so:
		Calculate SORT_AREA_SIZE × 2 × degree of parallelism.
		Add SORT_AREA_RETAINED_SIZE × degree of parallelism × number of sorts above two.


	A temporary tablespace is created with the command CREATE TABLESPACE ... DATAFILE ... TEMPORARY


		Is created by the first sort operation
		Extends as demands are made on it
		Comprises extents, which can be used by different sort operations
		Is described in the SGA in the sort extent pool (SEP)


		Avoid sort operations whenever possible
		Reduce swapping and paging by ensuring that sorting is done in memory where possible
		Reduce space allocation calls: allocate temporary space appropriately


		Avoid sort operations whenever possible by:
		Using NOSORT to create indexes
		Using UNION ALL instead of UNION
		Using index access for table joins
		Creating indexes on columns referenced in the ORDER BY clause
		Selecting the columns for analysis
		Using ESTIMATE rather than COMPUTE for large objects


		The ratio of disk sorts to memory sorts should be less than 5%.
		Increase the size of SORT_AREA_SIZE if the ratio is greater than 5%.


		In this lesson, you should have learned how to:
		Avoid sort operations
		Size SORT_AREA_SIZE for sorting in memory
		Size SORT_MULTIBLOCK_READ_COUNT to reduce the number of I/Os
		Configure TEMPORARY tablespaces