Re: Which background process regulates amount of pga memory per session in 10g ? Thanks, rm.

"Mladen Gogala" <gogala_at_sbcglobal.net> wrote in message news:pan.2005.09.05.08.42.43.881437_at_sbcglobal.net...
> On Mon, 05 Sep 2005 08:28:11 +0000, Jonathan Lewis wrote:
>
>>
>> I believe that it is CKPT that deals with co-coordinating
>> PGA memory across sessions.
>
> Now, that's is an interesting choice. Do you have any particular reason to
> believe that? Hans Forbrich has proven to me that there might not be any
> coordinator, just an area in SGA, so what makes you think that there is
> a coordinator and, if there is, why would you chose CKPT? CKPT was
> normally in charge of updating control files and datafile headers during
> the checkpoint processing.
>
>>
>> Individual sessions display their current workareas
>> in v$sql_workarea_active, and processes display
>> their PGA usage in v$process. These figures allow the
>> background process to calculate values for V$pgastat,
>> updating it every three seconds.
>>
>> One of the critical values in v$pgastat is the global memory
>> bound (which starts life at _smm_max_size) that restricts
>> the size of a single workarea in a session - and this is the
>> figure that a session checks to restrict its memory demand
>> at various stages in the lifetime of a workarea operation.
>
> --
> http://www.mgogala.com
>

Firstly, there's this paper which decribes the sort of thing that was designed into the system. Written by the people I assume were the architects:

    http://www.cs.ust.hk/vldb2002/VLDB2002-proceedings/papers/S29P03.pdf

    A quote:
    "The global memory manager is a background daemon     which indirectly determines the size of each active work     area by publishing a "memory bound" at a regular interval,     generally every three seconds."

It doesn't mention CKPT, of course, but I think it is reasonable to assume that the daemon is a real daemon (rather than code that every session operates - which is sometimes how 'daemons' end up being implemented).

But you can infer the existence of a co-ordinator because v$pgastat gets updated every three seconds. (Of course, 3 seconds is a popular background timeout - so this doesn't entirely limit the suspects).

If you then run:

    select indx, description
    from x$messages
    where dest = 'CKPT'
    ;

you find this one:

    105 SQL Memory Management Calculation

It doesn't prove the point, but it does make it look like a good candidate.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005