Re: session_cached_cursors and parsing and performance

Neil Cudd wrote:
>
> Thanks Jim.
> Unfortunately, I have no sway over the application and it's not likely that
> it's going to get changed on my recommendation. Your suggestion on 8.1.7
> intrigues me, though. Thanks for the tip.
>
> Neil.
>
> Jim Kennedy wrote in message ...
> >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.
> >>
> >>
> >
> >

cursor_sharing = force

However, there have been numerous postings - bugs mainly but some good ones - on the use of this parameter

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"