Re: **Qu** : How to reduce RECURSIVE CALLS ?

vidya.shankar_at_firstdatacorp.com wrote:
>
> Dear Friends,
>
> Greetings.
>
> Please advise as to how to reduce the number of RECURSIVE CALLS
> in an Oracle (7.3.2.3) Database setup. This setup is on a
> Sequent Machine.
>
> Looking forward to your feedback.
>
> Yours Sincerely
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

-- 

Vidya,
	Recursive calls are calls that Oracle has to perform to the dictionary
complete the different phases of SQL statement processing.  A high
number of 
recursive calls may indicate a Shared Pool that is too small.  You might
want to 
investigate the hit ratios in your Library Cache(LC) and Dictionary
Cache(DC), and 
that should give you the fair idea of what is going on.  
	LC hit ratios should be above 90%(unless the application is generating
lots 
of dynamic sql) and DC hit ratios should be above 99%.  Ideally the
ratio of 
recursive calls to regular SQL calls should be less than 10%.  If the
application 
is generating too much of dynamic sql then the number of recursive calls
will be 
high, and consequently the cache hit ratios will be low.  The use of
bind variables 
will increase the probability of SQL statement reuse in the cache, there
by
reducing the number of parse calls(which in turn reduces the number of
recursive 
calls), but can pose other challenges, related with the optimization
plan that
the optimizer builds, when using the cost-based optimizer.

Regards,

Gaja.

Gaja K. Vaidyanatha		       	|	222 West Las Colinas Blvd., 
Principal Instructor, Core Technologies |	Suite 1000, Irving, TX 75039. 
Oracle Education, Oracle Corporation	|	E-mail:	gvaidyan_at_us.oracle.com 
Disclaimer : Opinions and views expressed are not that of Oracle Corp.