Re: session_cached_cursors and parsing and performance

You are probably getting high CPU usage and low SQL Area Get Hit Ratio because you are not using bind variables in your application. Open cursors is not this problem as long as it doesn't keep climbing. You could have 1,000 as long as it levels off on a per user basis.

High CPU usage is usually an indication of high reparsing because the sql statements are all different. e.g.
select * from employee where ssn='111-11-1111'; select * from employee where ssn='222-22-2222'; etc.
instead of a bind variable:
select * from employee where ssn=:hvSSN; where the value of the bind variable is changed.

This is quite common and covered inthe application developers guide. If you are running 8.1.7 then there is a parameter that may help this type of application behavior. I don't have my manual with me but it is in the section Oracle 8i Server Reference under init.ora parameters. It basicaly creates temp bind variables in some programatic way automatically so as long as the rest of the statement is the same then you won't get hard parses.

Jim

"Neil Cudd" <neil_at_cudd.demon.co.uk> wrote in message news:980508139.27453.0.nnrp-12.c2de6f3d_at_news.demon.co.uk...
> My second question today ...
>
> I have a poorly performing database (well the users complain anyway), in
 as
> much as the CPU is 0% idle and and the last time I checked the SQL Area
 Get
> Hit Ratio was below 30%. So I set session_cached cursors to 50 (from 0) to
> see if I can reduce parsing which I suspected was where the CPU was being
> hit.
>
> Now my question is around open cursors - How many open cursors would you
> expect a session to have at any one time ? Checking via enterprise
 manager,
> there are sessions with 100+ open cursors. This seems excessive to me, but
> I'd like a second opinion ....
>
> Any comments welcome,
>
> Neil Cudd.
>
>
Received on Fri Jan 26 2001 - 06:50:55 CST