pat98's always & forever

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6
This problem can occur on any platform.

Symptoms
- Getting ORA-4031 that is reported in the shared pool.
- Using auto SGA.
- Shared pool and SGA are set to appropriate values.
- Opening the ORA-4031 trace file, you can see a lot of space allocated to "Free Space" field.
- Running the following queries will return values less than 10

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '_shared_pool_reserved_pct';

Parameter                      Session Value          Instance Value
------------------------------------------------------------------------------

_shared_pool_reserved_pct         5                        5

Cause
The issue is Shared Pool fragmentation. This fragmentation is caused because the shared pool reserved size is set to less than 10% of the shared pool size (5% in the above example). It is always recommended that shared pool reserved size is 10% of the shared pool size to avoid fragmentation.
Modifying the shared pool in Auto-SGA is done only using a hidden parameter as below.
Solution
SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile
or set it in init.ora
"_shared_pool_reserved_pct"=10

And restart the instance.