Re: flush shared pool problem

From: <markp7832_at_my-deja.com>
Date: 2000/03/09
Message-ID: <8a94bl$4tg$1@nnrp1.deja.com>#1/1

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 - 00:00:00 CST