Re: flush shared pool problem

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?
>
> Thanks,
> Steve
Received on Mon Mar 27 2000 - 21:19:51 CST