(This is my article which will be published in the upcoming SOUG Newsletter (April 2006))
This article deals with the changes of the implementation of the Shared Pool and what you should take into consideration when upgrading instances to Oracle 10g.
In prior releases of Oracle the value of the SHARED_POOL_SIZE parameter did not represent the total amount of memory allocated by the shared pool at startup. It just represented the largest part of the shared pool memory.
Starting with Oracle Database 10g Rel. 1, if you are not using Automatic Shared Memory Management (ASMM), the value specified at startup time for SHARED_POOL_SIZE does represent the total memory allocated by the shared pool.
Now the shared pool memory calculated at startup time is exactly the same as the value of SHARED_POOL_SIZE after rounding by the granule size (add 1 byte to 80mb, and you’ll find that we allocate 84, 88, or 96mb – depending on platform.) Thank you T.K. for pointing this out to me!
In former releases the total allocated shared pool memory was calculated by the value of SHARED_POOL_SIZE plus a certain additional overhead allocated at startup time, let’s call this the Internal SGA (ISGA).
So before Oracle 10g the TOTAL SGA was:
FIXED_SGA + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFER + DB_CACHE_SIZE +SHARED_POOL_SIZE+OVERHEAD (ISGA).
You could see the difference between the parameter value of SHARED_POOL_SIZE and the real memory allocated by the shared pool with the following query:
SYS@ora9iR2> SHOW PARAMETER SHARED_POOL_SIZE
NAME TYPE VALUE
——————— ————- ————
Shared_pool_size big integer 83886080
SYS@ora9iR2> SELECT SUM(BYTES)
FROM v$sgastat
WHERE pool =’shared pool’;
SUM(BYTES)
——————
100663296
This is a difference of exactly 16MB;
Since with Oracle 10g the ISGA overhead is included in the parameter, we must find a formula to calculate how much memory we really have available there for Library Cache and Rowcache.
If we assume that the amount of overhead is still the same as it was in legacy releases the effective memory available after starting up would be, in our case, 16MB less in Oracle 10g than it was in Oracle 9i.
You can query the value of the ISGA overhead in Oracle 10g from the view v$sgainfo;
SYS@orcl10gR2> SELECT NAME, BYTES
FROM v$sgainfo;
NAME BYTES
———- ———-
Fixed SGA Size 1218992
Redo Buffers 2973696
Buffer Cache Size 180355072
Shared Pool Size 92274688
Large Pool Size 4194304
Java Pool Size 4194304
Streams Pool Size 0
Granule Size 4194304
Maximum SGA Size 285212672
Startup overhead in Shared Pool 37748736
Free SGA Memory Available 0
The upgrade utilities in 10g ($ORACLE_HOME/rdbms/admin/utlu101i.sql in 10gR1 and $ORACLE_HOME/rdbms/admin/utlu102i.sql in 10gR2 use the values of the pre-upgrade instance and take a number of other values into account in order to recommend a new minimum value for the post-upgrade instance, such as the values of SESSIONS and CPU_COUNT. Looking at the procedures these scripts execute we can find the formula for the calculation of the new values:
(This is an excerpt from the utlu102i.sql script)
– On a large database, the minimum of 144M may not be enough for shared pool size,
we have to factor in the number of CPU,
the number of session,
and some new added features.
So here is the formula: — Recommended minimum share_pool_size = — mp(sp_idx).minvalue — + — (Num_of_CPU * 2MB) + — (Num_of_sessions * 17408) + — (10% of the old shared_pool_size for overhead)…
If in 10g you startup an instance using Manual Shared Memory Management (MSMM) with a too small value for the SHARED POOL this will cause an:
Error: ORA-00371 (ORA-371)
Text: not enough shared pool memory, should be at least %s bytes
Cause: Init.ora parameter shared_pool_size is too small
Action: Increase the parameter value
In releases prior to 10g the error messages for ORA-00371 would have looked slightly different:
in Oracle 9i=>
Error: ORA-00371 (ORA-371)
Text: not enough shared pool memory
Cause: Init.ora parameter shared_pool_size is too small
Action: Increase the parameter value Oracle 8i and older =>
Error: ORA 371
Text: no free buffer handles available
Cause: The value of the initialization parameter DB_HANDLES is too small.
Action: Shut down the instance, increase the value of DB_HANDLES in the
initialization parameter file, and then restart the instance.
Under normal circumstances, let DB_HANDLES take its default value by omitting the parameter from the initialization parameter file.
The Default Value for SHARED_POOL_SIZE in Oracle 10g with MSMM is platform specific:
(32 MB for 32 bit platforms,
64 MB for 64 bit platform) rounded up to next granule.
With ASMM (SGA_TARGET > 0) the default SHARED_POOL_SIZE value is 0 (Oracle adjusts the value internally) and the current size is stored in the parameter __SHARED_POOL_SIZE (double underscore!) in the spfile if you use any. And this value will be used for the next startup. This is another good reason to use an spfile instead of the ASCII pfile.
If you use Automatic Storage Management (ASM) then additional structures must be added to the shared pool to store extent maps for storage management activities:
You can calculate the amount of this additionally needed memory by computing aggregates of current database structures like datafiles, controlfiles, logfiles and tempfiles.
SELECT SUM(bytes) / (1024*1024*1024)
FROM v$datafile;
SELECT SUM(bytes) / (1024*1024*1024)
FROM v$logfile a, v$log b
WHERE a.group#=b.group#;
SELECT SUM(bytes) / (1024*1024*1024)
FROM v$tempfile
WHERE status=’ONLINE’;
These values would have to be added to the shared pool memory
as well as another portion which is depending on the redundancy level (number of mirrors) of the asm disk groups used by the instance:
This would be:
(1MB of additional shared pool for every 100GB of disk space) + additionally 2MB for external redundancy (no mirroring),
(1MB of additional shared pool for every 50GB of disk space) + additionally 4MB for normal redundancy (two mirrors)
and
(1MB of additional shared pool for every 33GB of disk space) + additionally 6MB for high redundancy (three mirrors)
All these considerations only have to be made for a migration to Oracle10g and only if you do not use Automatic Shared Memory Management.
There is no such problem if you allow Oracle to auto tune the five dynamically tunable Caches in the SGA, which are:
DB_CACHE_SIZE
SHARED_POOL_SIZE
JAVA_POOL_SIZE
LARGE_POOL_SIZE
STREAMS_POOL_SIZE (this is a new cache introduced with Oracle 10gR1 and was made auto tunable in 10g Release 2)
This new feature is not enabled by default and can be changed dynamically.
Posted by Lutz Hartmann 
