Oracle - Recursive call caused by Hard Parse

> Database > Oracle Database

1 - 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”.

3 - Example

Made with the Oracle Database - Sample Schemas :

[email protected]>ALTER system FLUSH shared_pool;
 
System altered
 
[email protected]>SET autotrace traceonly statistics;
[email protected]>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
 
[email protected]>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.

If the recursive call remain high the second time of execution, it's then not an hard parse recursive calls

Advertising