Oracle - Recursive Calls

Card Puncher Data Processing

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 :

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.

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.

Reference





Discover More
Card Puncher Data Processing
Oracle - Recursive call caused by Hard Parse

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...
Card Puncher Data Processing
Oracle - Recursive call caused by PL/SQL function

can also occur when you are calling a PL/SQL function from a SQL statement and this function : executes many SQL statement itself or refers to built-in functions such as USER A possible solution...
Card Puncher Data Processing
Oracle - Recursive calls caused by data modification (triggers, function based index, )

When you are doing data modification, you can fired many side effects as : trigger function-based index ... which include also other SQL statement and improve then the recursive calls metrics....
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - Recursive call caused by space requests

Recursive calls can be performed to satisfy requests for space due to : disks sort a large modification to a table that require it to extend This is generally not a problem with locally managed tablespace...
Card Puncher Data Processing
Oracle Database - Session Statistics (V$SESSTAT)

The second part of the autotrace output are a part of the session statistics. V recursive_calls : Number of SQL...



Share this page:
Follow us:
Task Runner