Shared pool

Quote:

---

Originally posted by sunkutup
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

---

In your case, with the information I have, with 40,000 buffers I would choose the size of the shared pool from the interval 128M-256M.

Quote:

---

Originally posted by sunkutup
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?

---

Run these 2 queries first:

select (sum(pins)-sum(reloads))/sum(pins) as "LIBRARY CACHE HIT RATIO" from v$librarycache;

select (sum(gets)-sum(misses))*100/sum(gets) as "LATCH HIT RATIO (> 99 OK)" from v$latch;

Quote:

---

Originally posted by julian

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??

---

No' I ment above, that is 8.1.6 or 8.1.7 or 9.0.*. In 8.1.5 and below there were only 11 free lists for shared pool space chunks, and with oversized shared pool in those earlyier releases the free lists for the two lowest size ranges usually got very long over time. So because system must scan those long free lists it must keep latch for a much longer time and in very active system witrh high concurency this is the most common latch contention problem.

In 8.1.6 and above there is 255 shared pool free lists (with many free lists devoted for smaller, more problematic chunks), so the problem with latch contention caused by oversized shared pool has gone with these nower releases.

Here is just an improved form of the query what Julian had provided. The query what Julian had provided would miss lead you with the total size for the large pool and java pool sizes.

Code:

---


  select s.pool "Pool name", to_number(p.value) "Total pool size",
    (s.bytes/1024/1024) "Free Mega Bytes", round((s.bytes/p.value)*100,1) "% Free"
    from v$parameter p, v$sgastat s
    where s.name = 'free memory'
    and p.name = replace(s.pool,' ','_')||'_size';

---

Sam

SELECT SUM(sharable_mem)
FROM v$sqlarea
WHERE executions > 5
/

Output : 171942631


SELECT SUM(250 * users_opening)
FROM v$sqlarea
/

Output : 5304375


SELECT SUM(sharable_mem)
FROM v$db_object_cache
WHERE type = 'PACKAGE'
or type = 'PACKAGE BODY'
or type = 'FUNCTION'
or type = 'PROCEDURE'
/

Output : 269774


SELECT 250 * value bytes_per_user
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'opened cursors current'
AND s.sid = 15
/

Output : 125250


sum all is your shared pol usage

sum of all this = 177642030

My shared_pool_size = 400000000

and on checking free space i found that 29.5% of space is free.

Lib cache HitRatio = 99%
Latch HR=99%

So what doi u suggest now ?

No Suggestions ????

suggest what?

I mean r the results satisfactory !!!

as all were saying that free sould be not more than 10%, butin my case it is showing 29.5 %.

what is the overhead/problems due to oversize of SGA ?

Quote:

---

Originally posted by vijay8282
I mean r the results satisfactory !!!

as all were saying that free sould be not more than 10%, butin my case it is showing 29.5 %.

what is the overhead/problems due to oversize of SGA ?

---

You can have 90% free in your shared pool and won't get any performance hit at all caused by this - if your database is relatively quiet. Only on very active database with many concurent active proceses when those processes begin to compete for internal resources you could experience severe shared pool latch contention.

Please read the doc: http://oradoc.photo.net/ora816/serve...9_mem.htm#1161
To better undestand how the shared pool works and how to tune it.

Sam