Does hard parsing lead to sort in memory???
I was running a simple sql select query to fetch some details from a table.I generated an execution plan for the query to check for optimizers plan for the query processing
I observe some interesting details on the execution plan results
SQL> select * from t2 where id between 10000 and 20000;
20002 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10102 | 256K| 11723 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 10102 | 256K| 11723 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=20000 AND "ID">=10000)
Statistics
----------------------------------------------------------
47 recursive calls
0 db block gets
44387 consistent gets
43008 physical reads
0 redo size
455310 bytes sent via SQL*Net to client
15214 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
3 sorts (memory) <--------
0 sorts (disk)
20002 rows processed
I can observe 3 sorts in PGA when i ran the query for the first time.
I was checking for the reason behind the sorts without order by or group by clause
When i ran the query for the second time,there was no sorting on memory!! and there are no recursive calls which means the datablocks are read from buffer cache directly!!
SQL> /
20002 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10102 | 256K| 11723 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 10102 | 256K| 11723 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=20000 AND "ID">=10000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44341 consistent gets
43007 physical reads
0 redo size
455310 bytes sent via SQL*Net to client
15214 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory) <-----
0 sorts (disk)
20002 rows processed
This concludes that whenever a new sql run on a database,the optimizer searches for the previous hash value of the query.If hash value for the query doesnot exist on the library cache,the optimizer has to hard parse the query with new hash value.