This summary describes what happens when a sort takes place, and how to optimize this process.(metalink note: 102339.1)
- What SQL operation require data to be sored?
- Index Creation
- Order By or Group BY clauses
- Distinct values of Select statement
- Union, Intersect or Minus operations
- Sort-Merge joins
- Analyze command execution
- SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE default to the same value, so all memory needed by previous sorts, will be available for next sort operations.
- Disk sort is required if additional memory is large than value of SORT_AREA_SIZE
- Storing temporary segments in a permanent tablespace is NOT good because
- Permanent tablespace can become very fragmented.
- SMON wil take some time to drop temporary segments (Performance degradation)
- Multiple transactions can share the same sort segment, however, they cannot share the same extent.
- More Guidelines
- Informatino about temporary segements
- Description of view v$sort_segment
- check with v$session & v$sort_usage
- if the new extent is 6 or more blocks, Oracle adds an extra block to the request to reduce internal fragmentation.
Popularity: 2% [?]
