Oracle Analytic SQL Sort
Recently I have been optimising the Oracle analytic SQL sort operations for a number of queries that make use of the ROLLUP analytic function. The test system was Oracle Database 18.104.22.168. Reviewing the extended SQL trace files to understand exactly where the time for the query was being spent we observed the same type of problem documented in Analytic Agony.
Tracing the sort operations performed by the analytic SQL we noticed the following:
- Many small sort operations that were completing in memory using less than 80mb sort_area_size.
- A few very large sort operations that were writing out to temporary tablespace of approximately 8GB in size.