Re: Automatic Memory Management not allocating SGA resources - ??

On Feb 16, 12:35 pm, "BD" <robert.d..._at_gmail.com> wrote:
> 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

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.