Automatic Shared Memory Management

Starting with Oracle 10G R1 there is a new parameter sga_target which determines whether you want Oracle to manage the sizes of various pools within the SGA or whether you want to manually manage memory allocated to Oracle.
To use this feature the value of STATISTICS_LEVEL should either be set to TYPICAL or ALL.

But this feature is not without its fair share of issues especially on large and intensive databases.

The reason for this is two fold.
The following bugs exist

4587117 (P) LARGE_POOL_SIZE AND JAVA_POOL_SIZE DO NOT SHRINK IN ASMM Fixed in 11.0
4466399 (U)
4472338 (U)
(duplicate of the above 4466399)
4507532 (U)
(turned out to be a bad diagnosis, but the bug is real)
4920199 (U)

Secondly the default behaviour is that if the shared pool is stressed and requires more memory it is removed from the buffer cache. But the reverse is not true. However this default behaviour can be changed by setting _memory_broker_shrink_heaps=30
The default value for the above parameter is 0

The shrinking of the Java pool depends on the value of _memory_broker_shrink_java_heaps. The default value is 900.

You can set the following event to trace memory management. The MMON process trace file will update statistics related to the different pools every 5 mins.

SQL> alter system set "_memory_management_tracing"=31;

System altered.

The default value for the above parameter is 0

Shrinking segments

Shrinking of a segment makes unused space available to other segments in the tablespace and may improve the performance of queries and DML operations.

The function VERIFY_SHRINK_CANDIDATE in the DBMS_SPACE is an undocumented function which informs us about the size of the segment upto which we can shrink.

declare
res boolean;
begin
res := dbms_space.verify_shrink_candidate('SCOTT','EMP','TABLE',1032192);
if (res)
then dbms_output.put_line('Can shrink to this size');
end if;
end;
/

Can shrink to this size

PL/SQL procedure successfully completed.



declare
res boolean;
begin
res := dbms_space.verify_shrink_candidate('SCOTT','EMP','TABLE',1032191);
if (res)
then dbms_output.put_line('Can shrink to this size');
end if;
end;
/

PL/SQL procedure successfully completed.

In the above example we can shrink a segment to 1032192 bytes.