Re: flush shared pool problem

From: <markp7832_at_my-deja.com>
Date: Thu, 09 Mar 2000 21:16:42 GMT
Message-ID: <8a94bl$4tg$1@nnrp1.deja.com>

In article <qgMx4.5407$OV.747149_at_typhoon.austin.rr.com>, "steve perry" <sperry3_at_satx.rr.com> 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
>

I think we could use more information like What is:

1- shared pool size?
2- library cache hit ratio?
3- dictionary cache hit ratio?
4- are you using MTS which puts UGA in shared pool? If so what is

sort_area_size?

5- how much free memory
6- pin hit ratio's from v$library_cache
7- reserved size if any
8- redo latch hit ratio

These are some things to consider.
--
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 09 2000 - 15:16:42 CST