WHAT ARE RECURSIVE CALLS?
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.
You do NOT call the kitchen staff directly and tell them how to make your pizza , call the cashier to give him your credit card number, call the delivery man and tell him to pick up your pizza and bring it to your house.
Nope, you make one call, not three. The person you place your order with makes those “recursive” calls to the kitchen and delivery staff to make sure your order is complete.
The kitchen staff may make additional “recursive” calls (“Where are the mushrooms?”) as may the delivery man (“Where is that street located?”).
You don’t care about these details, you just want your pizza.
WHAT KINDS OF RECURSIVE CALLS DOES ORACLE MAKE?
As you can tell from our pizza example, Oracle will make a recursive call whenever it has to.
Some examples of when it “has to” include:
you want to insert data into a DMT, but all extents are full and news ones need to be allocated to hold your data
you’re doing something on a table that causes a trigger to fire (e.g. – don’t allow inserts if it’s Saturday)
data dictionary cache needs info about objects during parsing .
you have PL/SQL units that include SQL statements (like a stored procedure that inserts into a table, or a function that selects from a table)
TUNING RECURSIVE CALLS???
I hope you can see from this brief discussion that the concept of tuning recursive calls is not as easy as configuring the Recursive Program Interface (RPI).
Tuning depends on several factors, the two biggest being what are you trying to do and what version are you on?
Sometimes recursive calls indicate a problem, sometimes they don’t. Can you see why?
I hear and am asked about the notion that recursive calls should always be less than “real” calls. I ask “Why?”
If I have a db app that only accesses the db via stored procedures and each of my procedures issues an average of 10 DML statements within it, then why would a 10/1 ratio be bad?
But that’s not to say that analysing recursive calls is not an important tuning step. You need to know what your database is doing and why. Recursive calls could be a hidden problem.