DOAG sent my article after me

The DOAG News has sent a copy of the latest issue
with my articel after me to my hotel in
California. It is the Greman version of my article about the Shared Pool Size implementation in Oracle 10g.
Thank you very much for this first class service!
And greetings from Redwood Shores to Berlin.

connect lutz hartmann as sysdba

How to calculate the minimum size of the SHARED POOL in ORACLE 10g

(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.

connect lutz hartmann as sysdba

Why restore before recover? The new Oracle 10g Backup Strategy

Delivering Oracle 10g R2 Administration Workshop II we had a closer look at the new recommended backup strategy for Oracle.

With Oracle 10g R2 we can recover datafile copies like we recover the real datafiles.
This gives us the oportunity to recover the entire database without having to restore it from backup first.
Which of course saves very valuable time in case of a disaster.

Here is how you could do it:

- use a “flash recovery area” to store everything needed for recovering, such as:
- an image copy of the entire database
- one incremental backup of the database
- the archived redo logs which have been created since the last incremental backup

The flash recovery area is a location on disk which you specify by the parameter db_recovery_file_dest. It can be changed dynamically without having to restart.
With the also dynamically changeable parameter db_recovery_file_dest_size you can limit the amount of disk space you want to use for this area.

Once you use it rman will create backupserts and image copies of datafiles there by default.

- create an image copy of the entire database:

RMAN > BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY DATABASE;

This will first search for a parent image copy to recover. If there is none yet, it will create one.
If there is an image copy of the database already, it will create an incremental level 1 backup.

- after you have create the first incremental backup for your image copy you can update these image copies of the datafiles by applying the incremental backup to them.

RMAN > RECOVER COPY OF DATABASE;

This is what you do every night.
Then you will have a copy of the database which is always
at least at the level of last night.

In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:

RMAN > SWITCH DATABASE TO COPY;
You can only do this in MOUNT.
- Now you are already where you were last night and you only have to recover what has changed since last incremental update without having to restore the database at first.

This is a very handy way to perform recovery.
We were able to switch datafiles to copies before 10g already, but could not incrementally update them.

connect lutz hartmann as sysdba