Re: Tuning attempt

Marc-

With all due respect, what does pin hit ratio have to do with anything at this point? If you think you have a performance problem, you need to consider this approach:

1. find the worst bottleneck
2. relieve it
3. go to 1, repeat until done

What does Statspack say regarding the worst bottleneck? Look at the top five wait events and go from there. You might well be I/O bound or CPU bound and not really have a problem with the server at all.

Optimal shared pool size is a function of your particular platform, instance and workload and nothing more. If you actually have a library cache or shared pool problem, it will be clear enough and you can drill down logically rather than using TOAD to stab in the dark. Lots of techniques exist to deal with various shared pool and lib cache problems, such as those suggested by Niall. But you need to be dealing with an actual, known problem if you want to see measurable improvements.

And ignore the nonsense about enlarging the shared pool until you get "x" hit ratio. This is nonsense of the TUSC/Niemec variety. Hogwash.

Post your Statspack report here and I am sure some qualified contributors will take a look at it.

• ricky

Marc Eggenberger wrote:
>
> Hi there.
>
> I play a bit with statspack and other tools to see what could be
> improved with my databases.
> I also tried TOAD and it says that my library cache pin hit ratio is
> 89.8674 and this is bad and thats because my shared pool area is too
> small ...
>
> my shared_pool_size is currently at 98765516
>
> I dont have any idea if this value is "high" or not. I know that you
> cant really say if its right if you dont know the application.
> But for a oracle 8.1.7 running on a W2k cluster (2x 1GHz, 3GB RAM) and
> the application beeing more a OLTP with about 60 sessions. The queries
> are more or less simple selects with joins over 5-10 tables and inserts
> (its a assessment tool).
>
> What are the range of values for shared_pool_size one should have in
> those db / application ranges?
> more like 10-20MB, 100MB or even more to 500MB?
>
> --
> mfg
> Marc Eggenberger
Received on Tue Apr 08 2003 - 10:58:55 CDT