Oracle Analytic SQL Sort Performance Improvements

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.

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.

Oracle Flashback Query

Oracle Flashback Query

The next requirement that I found a very good use for Oracle Flashback Query came about as part of a tuning exercise.

I was working through an issue where an application would use a table to temporarily store data before presenting the data to the client program. What was happening was a classic case of incorrect statistics on the table segment resulting in incorrect SQL execution plans.

Even by using the automatic statistics collection features in Oracle Database 10g this problem would not go away. Basically the application performed the following across multiple sessions:

  1. Populate table with data.
  2. Query data to present to user.
  3. Delete data from table.

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.