Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning attempt
Marc Eggenberger <marc.eggenberger_at_itc.alstom.com> schreef in berichtnieuws
MPG.18fcb5bed6bd1c86989690_at_localhost...
| 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
Visit www.oraperf.com and upload the statspack output. You will get instant
advice.
The size of the shared pool depends a lot of how much stored PL/SQL the
application uses. If you only have tables, no triggers, hardly views, no
packages, procedures, functions than it can be smaller than with.
100Mb shared pool for 60 concurrent users looks like a good shot when there
is no PL/SQL.
Does the application use bind variables, so cursors can be re-used? Or is
each statement build with literal values, making each statement unique. In
that case more shared pool is needed.
Don't oversize you shared pool (so please ignore the advice to make it as
large as you can). It can introduce serious problems, like latch contention
on the shared pool latch and/or the library cache latch.
Look at www.ixora.com.au where you can find some scripts to measure on the
shared pool.
If you are running MTS specify large_pool. This will isolate the memory that
the shared servers use in the shared pool in a separate pool, reducing
fragmentation.
Received on Tue Apr 08 2003 - 14:36:39 CDT