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 11.2.0.1. 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.


Download my FREE Oracle Performance Diagnostic process to pinpoint your performance issues.

Reviewing the temp sort read and write operations in the extended SQL trace file we noticed that the sort read behaviour had a single block read followed by a single multiblock read – exactly as described by Jonathan Lewis.

During the course of this work there was a plan to upgrade the Oracle database to the latest patchset. Once the database upgrade to 11.2.0.2 was completed we revisited the behaviour of some of the test Oracle analytic SQL queries.

Oracle Database Changes

After upgrading to Oracle Database 11gR2  (patch release 11.2.0.2) and testing the analytic SQL we observed the following:

  • Oracle database direct reads from temp performed as multiblock sort reads (in the size of the _sort_multiblock_read_count parameter).
  • Improved Oracle database sort IO performance to temporary tablespace for queries that perform a large amount of sorting and use analytic windowing functions.

There also appears to be some significant improvements made with the PGA automatic memory management.

To obtain the optimal performance on 11.2.0.1 the following changes were made:

  • Disable automatic pga memory management.
  • Set the sort_area_size and hash_area_size manually.

Even setting the _pga_max_size and _smm_max_size parameters had no significant impact. With Oracle Database 11.2.0.2 using PGA automatic memory management we get the same performance as 11.2.0.1 with manual pga memory management.

There seems to have been a lot of improvement in Oracle Database 11.2.0.2 around this area of the database – great news for workloads that perform large sorts using Oracle SQL analytic functions. Another good reason to keep your Oracle databases patched up to current releases.

Performance improvements provided by Oracle database upgrades can significantly improve your users experience and reduce your indirect cost of poor Oracle performance.

Download my FREE Oracle Performance Diagnostic process to pinpoint your performance issues.

Mark Burgess has been helping organisations obtain the maximum value from their data management platforms for over 20 years. Mark is passionate about enabling secure, fast and reliable access to organisations data assets.

About the Author

Mark Burgess has been helping organisations obtain the maximum value from their data management platforms for over 20 years. Mark is passionate about enabling secure, fast and reliable access to organisations data assets.

One comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.