Memory Management

Memory Management

9i can now dynamically manage the sizing of many of its internal memory work areas. Set the new initialization parameter PGA_AGGREGATE_TARGET to a value indicating total memory for the PGA’s *AREA parameters, then you can set new parameter WORKAREA_SIZE_POLICY to AUTO. The default value for PGA_AGGREGATE_TARGET is 0 – you must set it to some other value first, prior to changing WORKAREA_SIZE_POLICY from MANUAL to AUTO, or else Oracle gives you an error. Both parameters are dynamic so you can set them via the ALTER SYSTEM command if desired.

9i allows you to dynamically re-size the Systems Global Area (SGA), without having to shutdown and restart Oracle. Be sure you have set the new initialization parameter SGA_MAX_SIZE to the maximum SGA size you’ll ever want before doing this.

You can get feedback from Oracle about the use of the buffer cache and advice on sizing the buffer cache by:

1. Set DB_CACHE_ADVICE to ON by:

ALTER SYSTEM SET DB_CACHE_ADVICE=ON;

2. Query performance view V$DB_CACHE_ADVICE for the results