Aman -- Thanks for the question regarding "shared [pool fragementation....", version 10.2.01

You Asked

Hi sir,
(1) In 10gR2 Performance Tuning guide(OU) it is mentioned that in 10gr2 ,the internal fragmentation of shared pool is more or less gone.I want to know what is that interal fragmentation that is removed from 10gr2?And how we can actualy see this happening in the database functionality?
2) It is recommended by oracle that while upgrading database version X to 10g, shared pool must get additional 20/30mb from the source database size coz the internal overhead is now a part of the shared pool.I want to know what is the internal overhead that has now a patr of shared pool of 10g for which the additional memory is recommended in the upgraded database?
Thanks and regards,
Aman....

and we said...

1) as the database versions go up, things change - algorithms change, things change.

I don't know what they were specifically referring to there, what their thoughts were but...

This "internal fragmentation" (I still want someone to define fragmentation for me some day, no one can) was going away in version 8.0 - when they added the large pool (to move memory that was allocated in large chunks and not LRU managed out of the shared pool)...

and when they added the streams pool - to move that out of the shared pool...

and when they added multiple subpools in the single shared pool in 9i - when the shared pool is large and you have many cpus

and when they.... (eg: the list is long and esoteric)

2) coz? what is this "coz" thing?

Here is an excerpt from my last book on the shared pool between 9i and 10g. Basically, in 9i - the init.ora parameter shared pool size was the starting point, we would SIZE IT UP from there. In 10g, we use that to size it - period, we do not add to it. So, using the 9i init.ora against 10g would result in a smaller overall shared pool being allocated:

<quote>

One last comment about the shared pool and the parameter SHARED_POOL_SIZE. In Oracle9i and before, there is no direct relationship between the outcome of the query

ops$tkyte@ORA9IR2> select sum(bytes) from v$sgastat where pool = 'shared pool';
 
SUM(BYTES)
----------
 100663296

and the SHARED_POOL_SIZE parameter

ops$tkyte@ORA9IR2> show parameter shared_pool_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 83886080

other than the fact that the SUM(BYTES) FROM V$SGASTAT will always be larger than the SHARED_POOL_SIZE. The shared pool holds many other structures that are outside the scope of the corresponding parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor. For example, the parameter CONTROL_FILES contributes 264 bytes per file to the ¿miscellaneous¿ section of the shared pool. It is unfortunate that the ¿shared pool¿ in V$SGASTAT and the parameter SHARED_POOL_SIZE are named as they are, since the parameter contributes to the size of the shared pool, but it is not the only contributor.
In Oracle 10g and above, however, you should see a one-to-one correspondence between the two, assuming you are using manual SGA memory management (i.e., you have set the shared_pool_size parameter yourself):

ops$tkyte@ORA10G> select sum(bytes)/1024/1024 mbytes 
  2  from v$sgastat where pool = 'shared pool';
 
    MBYTES
----------
       128
 
ops$tkyte@ORA10G> show parameter shared_pool_size;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 128M

This is a relatively important change as you go from Oracle9i and before to 10g. In Oracle 10g, the SHARED_POOL_SIZE parameter controls the size of the shared pool, whereas in Oracle9i and before, it was just the largest contributor to the shared pool. You would want to review your 9i and before actual shared pool size (based on V$SGASTAT) and use that figure to set your SHARED_POOL_SIZE parameter in Oracle 10g and above. The various other components that used to add to the size of the shared pool now expect that memory to have been allocated for them by you.

</quote>

Reviews

Fragmentation   December 10, 2007 - 4am Central time zone Bookmark | Bottom | Top Reviewer: Richard from UK This MetaLink Note says that unavailable contiguous memory leads to issues, which I take to mean issues when the Shared Pool is fragmented: https://metalink.oracle.com/metalink/plsql/f?p=130:14:1716762349582275449::::p14_database_id,p14_doc id,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,146599.1,1,1,1,helvetica

---

Write a Review