Oracle - Recursive call caused by Hard Parse

About

Hard Parse are made when insufficient informations are present in its cache (shared pool, …) as :

• object being accessed,
• permissions,
• …

When Oracle retrieve this information, it made some “recursive calls”.

Articles Related

Example

Made with the Oracle Database - Sample Schemas :

sh@orcl>alter system flush shared_pool;

System altered

sh@orcl>set autotrace traceonly statistics;
sh@orcl>select * from customers;

55500 rows selected.


Statistics
----------------------------------------------------------
       1019  recursive calls
          0  db block gets
       5292  consistent gets
       1458  physical reads
          0  redo size
    6428049  bytes sent via SQL*Net to client
      41085  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
      55500  rows processed

sh@orcl>select * from customers;

55500 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5059  consistent gets
          0  physical reads
          0  redo size
    6428049  bytes sent via SQL*Net to client
      41085  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      55500  rows processed

As you can see the recursive call go to zero and the physical reads as well.