Sga Auto sizing ! Good or Bad ?

Have you bumped accross SIMULATOR LRU LATCH or SIMULATOR HASH LATCH ?
I have. I'm sure you have too.
What is it ? Why does it happen ?

Let's start from the basics.

What is ASMM ? Auto Shared Memory Management ?
Sure, it manages Oracle SGA memory dynamically. All you have to do is specify an upper limit using

alter system set sga_target = scope=spfile;

(and reboot !)

and Alas ! Oracle automagically sizes the buffer cache, shared pool, streams pool and the java pool. No need to measure any sizes of what buffer cache or shared pool should be.

Right ?

Wrong !

Let's dig more....

SQL> select 
2      component,  
3      parameter,  
4      initial_size,  
5      target_size,  
6      final_size,  
7      status,  
8      to_char(start_time,'dd-mon hh24:mi:ss') start_time,
9      to_char(end_time,'dd-mon hh24:mi:ss') end_time 
10   from   
       v$sga_resize_ops 
10   where  
       start_time > sysdate -1/12 
11*  order by 
       start_time

COMPONENT            PARAMETER         INITIAL_SIZE   TARGET_SIZE  FINAL_SIZE   STATUS     START_TIME      END_TIME
---------            --------------    ------------   ----------- ----------    ------     --------------- -----------
DEFAULT buffer cache db_cache_size     1.5469E+10     1.5452E+10  1.5452E+10    COMPLETE   04-jun 17:54:40 04-jun 17:54:40

shared pool          shared_pool_size  3506438144     3523215360  3523215360    COMPLETE   04-jun 17:54:40 04-jun 17:54:40
SQL> 

What is this ?
This means that ASMM is asking to increase the shared pool area and taking away space from buffer cache and that could be a reason why you will see simulator lru latch and simulator hash latch waits.

All good ?

Here's what I found. Although shared pool has enough free space available, SGA resizing still takes place.

SQL> select *  
2    from   v$sgastat  
3    where  pool like 'shared pool'  
4    and    name like 'free%';

POOL         NAME                       BYTES
------------ -------------------------- ----------
shared pool  free memory                805474392

What is this ?
The means there is 800 Megs of free space in the shared pool and ASMM is still asking for more space.

Why is that ?

I have asked Oracle. They had a wierd answer. Library object hits could be benfitial with having a larger shared pool.

Really ?
EVEN WITH FREE SPACE AVAILABLE ???

Here's the latest !
"It is cheaper for Oracle to move granules from buffer cache to Shared Pool than to use the free memory already avaiable in Shared Pool" - Doesn't make sense, does it ?

Well, this above is TRUE. ASMM works on hit ratios and when it thinks Shared Pool could do better by re-sizing it, it so does it !

Moral: Don't rely too much on the AUTOMATIC bells and whistles that Oracle provides.

Study them first !