Mark BurgessOracle 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.
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.
WAIT #18446741324872644744: nam='direct path read temp' ela= 252 file number=8200 first dba=3069961 block cnt=31 obj#=1999286 tim=163444784069
WAIT #18446741324872644744: nam='direct path read temp' ela= 76 file number=8200 first dba=3069992 block cnt=31 obj#=1999286 tim=163444784492
WAIT #18446741324872644744: nam='direct path read temp' ela= 145 file number=8200 first dba=3070023 block cnt=31 obj#=1999286 tim=163444784948
WAIT #18446741324872644744: nam='direct path read temp' ela= 296 file number=8200 first dba=3070054 block cnt=31 obj#=1999286 tim=163444785517
WAIT #18446741324872644744: nam='direct path read temp' ela= 148 file number=8200 first dba=3070089 block cnt=31 obj#=1999286 tim=163444785939
WAIT #18446741324872644744: nam='direct path read temp' ela= 146 file number=8200 first dba=3070120 block cnt=31 obj#=1999286 tim=163444786368
WAIT #18446741324872644744: nam='direct path read temp' ela= 163 file number=8200 first dba=3070151 block cnt=31 obj#=1999286 tim=163444787402
WAIT #18446741324872644744: nam='direct path read temp' ela= 134 file number=8200 first dba=3070182 block cnt=31 obj#=1999286 tim=163444787817
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.
