Automatic Memory Management not allocating SGA resources - ??

I'm confused about Automatic Memory Management.

Running 10.2.0.2 on Windows 2003 Enterprise SP2. /PAE enabled on the OS.

We are migrating a DSS database from 9.2.0.6 on AIX 5.2.0.2. It had been finely tuned (but used RBO).

We are staging an updated database on 10g.

The database was staged via DBCA, and then schemas were imported from exports done on the 9i database.

The SGA had been just under 700MB.

The new database's max_sga is just under 1.5GB, but the sga_target is currently about 900MB.

I am using Quest Spotlight on Oracle to quickly monitor memory usage as processes are run against the database.

I am seeing alerts about the SQL Library Cache miss rate reaching 100%.

>From what I can see, this region of the SGA is never getting larger
than about 6MB. And yet, the same monitoring tool shows about 50MB of free memory currently in the SGA.

I would have thought that automatic memory management would respond more quickly than this - if the SQL Library Cache and the SQL area are too small for the load being placed on them, resources would be allocated from the free memory in the SGA.

But this does not appear to be happening.

As well, when we increased the SGA size recently, all the added memory went to the buffer cache; the shared pool was not increased at all.

I had considered increasing the size of the shared pool manually, but with about 30% of it free (if my metrics are correct) what's the point? It looks like ASMM is simply not increasing the size of the caches - and when I'm seeing 100% miss ratios, and no change in ASMM memory allocation, I'm confused.

ASMM is enabled - SGA target is set, and both db_block_buffers and shared_pool_size are set to 0.

I wonder if someone might comment on what I *should* expect here, as to allocation of resources within an SGA? Should the response not be 'as-needed'? Or perhaps I'm just barking up the wrong tree...

For what it's worth - on the 9i system, the SQL library cache is about twice as large as I'm seeing on the 10g database, and the miss rate is about 25%.

Thanks for all comments...

BD Received on Fri Feb 16 2007 - 14:35:07 CST