Re: 9.0.2 64-bit on redhat 3.0r2 AMD64 (4-way) 17G RAM

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 31 Aug 2004 06:34:18 +1000
Message-ID: <41338f73$0$5288$afc38c87@news.optusnet.com.au>

ctcgag_at_hotmail.com wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>> >
>> > For a multi GB PGA_AGGREGATE_TARGET I suggest you to read Metalink note
>> > 147806.1 (and the related notes as well...) to have some information
>> > about the limits set by Oracle. Here an excerpt:
>> >
>> >> PGA_AGGREGATE_TARGET limits both the global PGA consumption and the
>> >> size
>> > of a
>> >> workarea i.e. the memory allocated to a single SQL operator is also
>> > limited
>> >> to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to
>> >> 30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of
>> > Parallelism).
>>
>> It's when I see documentation like that I understand why Sybrand posted
>> what he posted.
>>
>> Translation: PGA_AGGREGATE_TARGET sets a global total of memory for the
>> entire database which can be used by all users collectively as their
>> total combined PGA. No user will use less than 5% of the total, or 100MB,
>> whichever is the smaller (assuming s/he makes use of it at all);

>
> I thought it was that no user running serial queries will use *more* than
> 5% of the total, or 100MB, which is the smaller.

You're quite right, of course. The "min" bit in "min(5%,100MB) got my brain thinking about making things smaller...less rather than more... etc etc etc. I should have used the word "more" rather than "less" in that sentence. Of course. "No user will be able to use more than either 5% of the total, or 100MB, whichever it the smaller".

I think my main point was that a complete load of waffly garbage can be turned into a series of meaningful sentences with a bit of thought. Not that I had necessarily so transformed it, or had done so accurately! (But I should have been more careful, I grant you).

> i.e. even if no one else
> is using the system, you get no more than this amount.
>

>> and no
>> single user can have more than 30% of the total. If a user issues
>> parallel DML or a parallel query, then the 30% maximum is divided amongst
>> all the possible query slaves that user might spawn. The user, even with
>> a high degree of parallelism, cannot hog more than 30% of the total
>> available PGA resources.
>>
>> Something like that, anyway.

>
> So when my session "checks out" a lot of memory (to do a large sort, say),
> does it "check in" that memory when it is done with the sort (on the
> theory that it is not using and so it can by swapped out), or does it keep
> it for the remainder of the session (on the theory that the memory hasn't
> been returned to the OS until the session is over)?
>
> Xho

You're asking me?? You usually do a good job on those sorts of questions! The theory goes that when you've finished your sort, the memory you used can be pinched back off you and allocated to someone else who can make better use of it. Whether it is actually 'returned to the aggregate pool' immediately, or only when some other user calls on it, I have no idea. I would have expected it to be easier to code for immediate return.

The other part of the theory rather rules out the idea of returning it to the O/S. That's what _area_size parameters did, for sure. But PGA_AGG_TARGET grabs its allocation at startup, doles it out to users as needed, and never gives any back to the O/S.

All unchecked for a long while, incidentally.

Regards
HJR Received on Mon Aug 30 2004 - 15:34:18 CDT