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

"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. 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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB