Automatic Memory Management not allocating SGA resources - ??
BD
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
BD
> I'm confused about Automatic Memory Management.
Follow-up - further discussion suggests that the issue is not that the
cache is full, but that it's empty. Very few bind variables are being
used, and it's likely that this is all about tuning the SQL for CBO
versus RBO.
Back to my Jonathan Lewis book...
joel garry
Well, what kinds of queries is your DSS doing? If they are using the
same sql over and over, a cache miss rate would be bad. But if they
are doing lots of different queries, the cache misses would be
meaningless. So what you *should* expect is based on what is actually
happening, and with a DSS that may mean any ratio-tuning would be even
more worthless than usual. Also, if you are seeing library-cache
related waits, that could mean a shared pool too _large_ in some
circumstances, see v$shared_pool_advice. I don't know what spotlight
does, but its view of the library cache may just be wrong for DSS.
I think what you need to do is lots of tracing and closely observe v
$sql_shared_cursor to see if the actual sql being used is slightly
repetitive or non-repetitive, see if you are doing lots of unnecesary
hard-parsing (review the Library Cache Concepts in the performance
manual), also by checking reloads and invalidations in v
$librarycache. For example, if the DSS queries are using literals
where they should be using bind variables, that would be... well,
repugnant. And it is definitely something to watch for on code from
RBO days, the more "finely tuned" things were, the more likely they
now aim firearms at phalanges. (If you can't change the code for
whatever reason, only then should you mess with cursor_sharing).
jg
--
@home.com is bogus.
The latest "science standards" for Kansas public schools are the fifth
for the state in eight years. They'd be better off with the Wizard of
Oz.
bdbafh
If your system has an "Amazon-like workload" you might want to check
into patch '10.2.0.3 patch 1' for MS win 32.
The patchset 10.2.0.3 would have to first be applied to the
oracle_home of interest.
-bdbafh