-
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.
-
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;
-
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.
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Thanx
Sam
Life is a journey, not a destination!
-
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 ?
--------------------------
The Time has come ....
-
--------------------------
The Time has come ....
-
-
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 ?
--------------------------
The Time has come ....
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|