Re: pga settings useage oracle 10.2.0.4 enterprise on windows 2003
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 7 Sep 2011 05:41:40 -0700 (PDT)
Message-ID: <61f1b8a1-3d06-4ac6-8ce3-49c0de8da6a9_at_q8g2000yqe.googlegroups.com>
On Sep 6, 11:19 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> I would like to manually control the amount of pga my session uses or
> is allowed to use
>
> / from 0 to 500m
>
> Please could anyone on advise on parameters that I need to manipulate
>
> cheers
> Chris B
>
> PS I want to do this so that I can force use of temp so that I can
> compare results on a fair basis between runs / a bit like alter system
> flush buffer_cache effect on sga;
Date: Wed, 7 Sep 2011 05:41:40 -0700 (PDT)
Message-ID: <61f1b8a1-3d06-4ac6-8ce3-49c0de8da6a9_at_q8g2000yqe.googlegroups.com>
On Sep 6, 11:19 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> I would like to manually control the amount of pga my session uses or
> is allowed to use
>
> / from 0 to 500m
>
> Please could anyone on advise on parameters that I need to manipulate
>
> cheers
> Chris B
>
> PS I want to do this so that I can force use of temp so that I can
> compare results on a fair basis between runs / a bit like alter system
> flush buffer_cache effect on sga;
Even though you can influence the amount of PGA a session is initially given via various settings such as sort_area_size and hash_area_size the fact of the matter is that it is the code being executed that determines how much PGA a session will need and there are no public parameters that allow you to limit this. If the code uses pl/sql collections and types and loads data into the collections then Oracle will keep taking memory to hold the data up to any Oracle and OS platform limitations that exist.
On Windows Oracle sessions run as threads within the Oracle executiable space.
HTH -- Mark D Powell -- Received on Wed Sep 07 2011 - 07:41:40 CDT