SELECT statement in combination of ORDER BY or GROUP BY clause performs SORT operation which we all accept from the concepts of oracle architecture. Sort area in MEMORY is one of the areas of PGA in dedicated server architecture or will be part of SGA in shared server architecture. We all believe that sort in memory happens first then sort in disk. When I went through the statistics of a SELECT query which doesn’t have ORDER BY or GROUP BY clause, it performs SORT in memory. Let us understand why.
Server used in this blog has:
- Restarted recently – Clean SHARED POOL and BUFFER CACHE
- Dedicated server architecture
- Let us run a simple 1st SELECT query in the instance without ORDER BY or GROUP BY clauses.
2. It clearly shows us that there were 7 sorts in the memory. This is in the sort area of PGA.
3. Let us now re-run the same query and look at the statistics.
4. Statistics clearly explains that there were NO sorts in the memory now.
5. Let us flush the BUFFER CACHE now and re-run the same query.
6. Still NO SORTS in the memory.
7. Let us flush SHARED_POOL and re-run the query.
8. Aaah!! Now you can make out the difference. We observe SORTS in the memory. This gives us a hint that these SORTS are the result of HARD PARSING.
Why this SORT?
- After passing through ‘Syntax Check’, ‘Semantic Check’ and ‘Shared pool Check’ if the instance doesn’t find the hash value of SQL in library cache then QUERY undergoes hard parsing.
- Now OPTIMIZER generates best execution plan by considering cardinality, CPU, Memory, Indexes etc… with the help of ‘STATISTICS’ wherever required.
- STATISTICS are also data blocks ready by the optimizer in to the memory from data dictionary and perform a SORT to analyze. Optimizer generates multiple execution plan in this process to pick the best one because of which we see SORT in memory.
- If a query undergoes HARD parsing, we can find sorts in memory.
- It is applicable to all SQL statements.
- These sorts will not be so huge that it reaches temporary tablespace.
- Impact of this is considerable and one of the reasons why query execution is slower when it undergoes HARD parsing.