Why SELECT performs SORT without ORDER BY/GROUP BY ?

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.

Considerations:

Server used in this blog has:

1. Restarted recently – Clean SHARED POOL and BUFFER CACHE
2. Dedicated server architecture

Demonstration:

1. Let us run a simple 1st SELECT query in the instance without ORDER BY or GROUP BY clauses.

SQL> set autot TRACE STAT

SQL> select * from scott.t1;

11 rows selected.

Statistics

———————————————————-

40  recursive calls

0  db block gets

59  consistent gets

12  physical reads

0  redo size

607  bytes sent via SQL*Net to client

419  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

7  sorts (memory)

0  sorts (disk)

11  rows processed

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.

SQL> select * from scott.t1;

11 rows selected.

Statistics

———————————————————-

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

607  bytes sent via SQL*Net to client

419  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

11  rows processed

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.

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from scott.t1;

11 rows selected.

Statistics

———————————————————-

0  recursive calls

0  db block gets

7  consistent gets

6  physical reads

0  redo size

607  bytes sent via SQL*Net to client

419  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

11  rows processed

6.  Still NO SORTS in the memory.

7.  Let us flush SHARED_POOL and re-run the query.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from scott.t1;

11 rows selected.

Statistics

———————————————————-

93  recursive calls

0  db block gets

204  consistent gets

27  physical reads

0  redo size

607  bytes sent via SQL*Net to client

419  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

14  sorts (memory)

0  sorts (disk)

11  rows processed

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.

Conclusion:

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

==============================================================================