About
Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called :
- recursive calls
- or recursive SQL statements.
For example :
- Oracle - Recursive call caused by Hard Parse : Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.
- Oracle Database - Recursive call caused by space requests : if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically.
Don't blindly try to get this to zero, because it is not possible or practical all of the time. If you see hundreds or thousands of recursive calls, check it out. sql trace will help greatly.
If recursive calls occur while the sql trace facility is enabled, then tkprof produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file.
Articles Related
What are recursive call ?
In Oracle, a recursive call is a call, for example, that must be completed before the user's SQL can be completed.
Say you wanted to order a pizza for delivery.
You make one call, place your order, and wait for your pizza.
Some recursive call will appear that you do NOT ask as :
- to call the kitchen staff directly in order to make your pizza ,
- to call the delivery man and tell him to pick up your pizza and bring it to your house.
You make one call, not three. The person you place your order with makes those “recursive” calls to make sure your order is complete.
Besides, some “recursive” calls occur :
- by the kitchen staff : “Where are the mushrooms?”
- by the delivery man : “Where is that street located?”
A recursive call can be seen as a sub-process in a complete series to make your order.
Definition for “recursive call” in the VSESSTAT and VSYSSTAT views
Number of recursive calls generated at :
- the user level
- and system level (to retrieve metadata, permission, quota, …).
Besides, Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.