In this module we carefully explored all of the causes and remedies for internal Oracle sorting.
Now you should be able to:
- Describe how Oracle performs sorts
- Identify the parameters that govern sorting operations
- Identify sorts in SQL statements
- Monitor for disk sorts
- Enable direct sorting in Oracle
- Allocate and size dedicated TEMP tablespace
- Monitor temporary segments in the TEMP tablespace
- Remove a sort from an SQL query
Here are some terms from this module that may be new to you:
- sorting: The process of re-sequencing result sets from Oracle queries.
- in-memory sorts: Sorts that are performed very quickly in the memory allocated to sort_area_size.
- disk sorts: Occurs when sorts exceed sort_area_size and Oracle will sort the result set in the TEMP tablespace.
- SMON: The Oracle system monitor process.
- index hint: A directive made in an SQL statement to force the use of an index when servicing a query.
In the next module, you will begin to learn about how the Oracle locking scheme impacts database performance.