Re: flush shared pool problem

"excoder" <cys4_at_columbia.edu> wrote in message news:38E024D7.4DDF2A14_at_columbia.edu...
> There are couple things you can do. If DB access is written in Java
Servlets,
> you can check the java web server access log to see which servlet was the
last
> one to access. If you narrow it down to a particular servlet, then you
can put
> commenting mechanism to see where it carries you.
>
> Once you find a particular query, try tuning it with explain plan or
tkprof.
>
> I hope it helps.
>
> excoder
>
> steve perry wrote:
>
> > We're having to flush the shared pool several times a day because of
slow
> > response times and abnormal loads on the cpu(all of a sudden the we
start
> > using a full cpu and no sql is active). Once we flush it, things
improve.
> > The response time improves and the cpu drops back down to nothing.
> > Does anyone know how we can trap the sql that causing the problem? I'm
> > having a hard time understanding the cause and why Oracle has the
problem in
> > the first place.
> > Here's what I know so far:
> > The system is not being used right much now, so the the problem is
> > confusing...
> > sun 4500, solaris 2.6, oracle 8.1.5
> > the app is 80% queries and 20% updates/inserts
> > sga: block buffers 2gig, shared pool 500 meg (tot ram on sun 4500 is 6
gig)
> > the application is java based and does NOT use stored procedures
(problem)
> > the app was NOT using bind variables, but they've started changing this
> > we have cron scripts that connect every 2 minutes that issue sql against
the
> > db
> > I've pinned all triggers and code that I could
> >
> > I saw a bulletin that stated possibly reducing the size of the shared
pool
> > may help.
> >
> > Anymore suggestions?
> >

Too big a shared pool can indeed slow things down dramatically.

I recommend you visit Steve Adams' web site:

http://www.ixora.com.au

...assuming you haven't already done so.

Regards
HJR
> > Thanks,
> > Steve
>
Received on Fri Mar 31 2000 - 08:25:37 CST