Re: workarea_size_policy=auto and performance efficiency [was: Re:

Hi!

> From what I've been able to determine about this functionality,
"efficient"
> merely means "space-efficient", not "performance-efficient" (i.e. Fewer
> cycles? Smarter cycles?). Is this correct? Does anyone know of anything
> in WORKAREA_SIZE_POLICY=AUTO which improves performance over
> WORKAREA_SIZE_POLICY=MANUAL?
Yeah, my understanding is as well, that the performance improvements of automatic work area sizing policy over manual one is that when you don't have enough spare memory, Oracle is just able to calculate the best amount of work area memory under current circumstances - keeping operations running optimally, while not grabbing too much memory from others. There are columns ESTIMATED_OPTIMAL_SIZE and ESTIMATED_ONEPASS_SIZE in V$SQL_WORKAREA, thus when doing a sort/hash/etc operation, server process knows whether it would be reasonable to allocate few more megs of memory for an operation or wouldn't it help much. Optimal or workarea size estimation can be done either using CBO statistics or actual execution statistics if previous execution statistics for given SQL are still in library cache. So, using 10M for sort area instead of 8M isn't that helpful if it doesn't reduce number of passes in sort, thus it might be reasonable to leave this extra 2M for another smaller operation, which could benefit more from that.

I set PGA_AGGREGATE_TARGET to 2,5G in my home computer, but only about 3-4MB of memory was used for my large sort for example. There are parameters _smm_max_size and _smm_min_size for setting boundaries for automatic

>
> Please correct me if I'm wrong, but I think the algorithm for
> WORKAREA_SIZE_POLICY=AUTO can be characterized something like:
[snip]

> Whereas in WORKAREA_SIZE_POLICY=MANUAL, it goes:
>
> [server process]: I'd like to malloc some private heap/data memory
> use in sorting, hashing, bitmap operations, or whatever.
> I'd like 100Mb, so that's what I'll allocate...

Manual allocation is probably much simpler and more lightweight operation, just note that Oracle is allocating sort area dynamically, in sizes of standard database block size or it's multiples.

> I mean, other than anthropomorphizing the whole thing, is this the general
> gist of it? Obviously, since the "instance" isn't a process and I'm not
> aware of another background process dedicated to this kind of thing, I'd
say
> that it is a tally kept someplace in the SGA that is latched and updated
by
> each server process in kind, but I thought the idea of a dialogue more
> amusing... :-)

I guess there actually is no "dialogue" between processes when allocating work area, it's just the server process reading memory usage from SGA, calculating best allocation size based on memory usage and work area estimate, allocating memory and writing updated usage information back. There are some latches called SQL memory manager something, they might be the ones for keeping track workarea usage. Note that according to concepts guide, automatic work area policy works only for dedicated server connections, shared servers will still use old *_area_size parameters since their work areas are mostly stored in SGA anyway (with few exceptions like retained sort area etc). So this might have been the catch in the OCP exam..

>
> If this is the case, then if I have a server which is not constrained for
> memory, then why should I be concerned about space-efficiency?

I think space efficiency isn't that much of a problem in OLTP environment with lot's of extra memory. Then you just set your _areas to few megs and they probably won't ever be fully utilized. But in OLTP/OLAP mix or OLAP you can't just set everyones work area maximums to 500MB and hope for the best. Playing around with logon triggers & session level work areas gets complicated as well..

>
> I tend to visit 2-3 different companies/organizations per week on a
regular
> basis, and while I do find plenty of under-sized servers laboring under
> over-sized Oracle instances, I just as often find over-sized servers with
> acres of RAM, in which I'm certain entire DIMMs have never felt a volt of
> electricity. Typical example is a customer I started at two weeks ago,
with

DRAMs always have volts of electricity, hundreds of millions of times in a second ;-)

> 12 CPUs and 24 Gb of RAM and 30Gb of swap space, whose database instance
is
> demanding about 4 Gb of virtual memory, primarily due to
> PGA_AGGREGATE_TARGET being set to 1.5Gb. There's typically 20Gb of
> untouched RAM on this thing!

Yeah, I totally agree with you, a lot of companies have the spare money for buying a server 10x more powerful they need, instead of spending tiny amount of money tuning their databases/applications or doing an evaluation on their resource needs.

> Of course, in this situation I could recommend that PGA_AGGREGATE_TARGET
be
> resized to 16-20Gb (as indicated in sizing advice in docs), but how would
> this functionality help performance in contrast to just generously setting
> SORT_AREA_SIZE et al?

Put their buffer cache to 20G, you'll get an award for reaching almost 100% buffer cache hit ratio ;-)

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).