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.

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: