Tag: Recursive Calls in oracle

you will find Recursive Call in AWR at most , and you have to understand what we mean in this , why we use it , Simple way to discuss this . its below .

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)

performing DDL

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.

Thank you
osama mustafa