Re: PGA

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote:

> Glance and ps include the shared memory segments, as has been, as far
> as I know, been posted here many times.
> And this time I'm just too tired to reach for my leadpipe.

Just hang on a tad Sybrand. A man of ps says of the sz column displayed via the -l switch:

sz The size in *physical pages* of the *core image* of

    the process, including text, data, and stack     space.

Where does it say that this include shared memory?

Glance's RSS is (private mem)+(shared mem/refcount). That I know.

Both ps and Glance gave a similar amount of memory used when taking shared memory into consideration (e.g. UGA of process). Oracle gave a completely different one - and just for a *single* specific shared server process.

Glance also showed a 100% swap utilisation without *any* swapping happening.

I send a SIGTERM to the shared server process and that solved the problem. Swap utilisation dropped back to normal and Glance and Oracle and ps agreed on the approx memory used by the new shared server Oracle started in the place of the old one.

Thus this is not an issue of slightly different memory reports, but totally conflicting memory reports - even within Glance itself that showed full swap utilisation while memory utilisation was below 90%, physical memory was available, and no swapping (or eve page errors) reported.

But all that aside. My actual question was : how do you limit the growth of the PGA when that shared process deals with dynamic PL/SQL arrays that can grow to 100's of MB in size?

BTW - have you also noticed the dispatcher behaving weirdly at times? On occassion I have seen the dispatcher tying up a single CPU by polling like crazy (with all shared servers showing idle and all user connections closed).

--
Billy