Shared pool

Library Cache

Gets 258431013
Misses 116198
Sleeps While Wait 64323
Imm. gets Number 1077488
Imm. Misses No 372

Other parameters related to lib cache don't show much stats

???

Quote:

---

Originally posted by vijay8282
Statistics

RAM 2 GB

Hit Ratio 96 - 97 % (Observed over a period of time)

Now ?

---

You have a bit high values but if you are not swapping, then all's fine.

How to check for that.. Swapping

I mean which parameters.

When I check thru SAP it shows swapping heavily...
but how to check on Oracle side.


???

Quote:

---

Originally posted by vijay8282

How to check for that.. Swapping

I mean which parameters.

When I check thru SAP it shows swapping heavily...
but how to check on Oracle side.


???

---

On UNIX level type VMSTAT 5 or TOP. TOP will show you the SWAP in MB, it should be very low. With VMSTAT 5, check the value in the FREE column (ignore the first row), it should be some 100M at least.

Quote:

---

Originally posted by sunkutup
Shared Pool Size Free Bytes Percent Free
---------------- ---------------- ------------
29,000,000 2,878,876 9.92715862

Can someone please assist me with this problem. i am made to believe that the shared pool is small.

---

No, your shared pool is not too small, *it is too big* (if those figures were gotten when your database was fully warmed up)!

Although usually various ratios and "rules of thumbs" are totaly meaningless, the rule of thumb for shared pool size is that if you have more of 5% free space in your shared pool when your database is in stable state, then your shared pool is too big. Prior to 8.1.6 you could pay serious penalty if your shared pool was oversized because you might be hit by the severe latch contention (this is what pando is refeering to), but this is not an isue any more with 8.1.6 and above, where instead of 11 free lists for various shared pool chunk sizes they introduced 255 free lists.

Here is what Steve Adams says about how much free space there should be in a shared pool:
http://www.ixora.com.au/q+a/0008/31012939.htm

5 or 10 even 20%. If you have the memory it is OK to overisize it a bit than to make it too small. With this query

select to_number(p.value) "Total_all", s.pool "Pool",
s.bytes "Free_bytes", round(( s.bytes / p.value ) * 100,1) "Free"
from v$parameter p,
v$sgastat s
where p.name = 'shared_pool_size'
and s.name = 'free memory'

you can follow the free memory.

Hi Julian

my shared pool is set to 29,000,000, the database buffers 40,000 and the block size is 2k. These setting were done by someone how was here before me.

I have followed your explanation. The pl/sql (funtions and packages) is 1

Will it help if I leave the buffer to 40,000 and the adjust the shared pool to the percentages of the buffers.

Please advise

Quote:

---

Originally posted by julian
5 or 10 even 20%. If you have the memory it is OK to overisize it a bit than to make it too small.

---

As I've said, this is true only if you are useing 8.1.6 or above.

Based on the fact that original poster's query returned only one row I'm assuming that he is not using 8i, so in his case it is not OK to oversize shared pool! Memory vastage is not important here, the latch contention is! In earlier releases it is often much more efficient to have a little undersized shared pool than to have it sigtnificantly oversized, as long as you are not getting any ORA-4031. Small amount of occasional object reloads in a bit too small shared pool will not influence your performance nearly as much as latch contention due to oversized shared pool.

Hi Julian

I got these results. According to your explanation I am supposed to have atleast 5% or above.

Check this

SQL> select to_number(p.value) "Total_all", s.pool "Pool",
2 s.bytes "Free_bytes", round(( s.bytes / p.value ) * 100,1) "Free"
3 from v$parameter p,
4 v$sgastat s
5 where p.name = 'shared_pool_size'
6 and s.name = 'free memory'
7
SQL> /

Total_all Pool Free_bytes Free
---------- ----------- ---------- ----------
29000000 shared pool 1369484 4.7


What do you think?

Quote:

---

5 or 10 even 20%. If you have the memory it is OK to overisize it a bit than to make it too small.
As I've said, this is true only if you are useing 8.1.6 or above.

---

Did you mean below??