Re: Shrinking PGA of snp processes

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Fri, 13 May 2005 17:09:51 -0400
Message-ID: <52a152eb05051314093d625aeb@mail.gmail.com>

I am alraedy using _smm_max_size and _pga_max_size to increase the sizes. smm is 5 gb, _pga_max_size (per process limitation) is 8 gb,

Using manual, you can only go to 2gb-1 byte. And we still want the benefit of automatic memory manamgement, if too many big queries run at the same time.

And you get much better "eyes" to see what's using sort/hash memory and how much with automatic pga.

I cover the technique in:

http://www.pythian.com/documents/Working_with_Automatic_PGA.ppt

Let me know if you have any comments/criticism to add.

On 5/13/05, Tanel P=F5der <tanel.poder.003_at_mail.ee> wrote:
> Hi Christo,

>=20
> What's the _smm_max_size value for your session? It states the max workar=
ea
> size for a serial session in kB (and defaults to 5% of
> pga_aggregate_target).

>=20

> But if you want so big PGA memory areas, it might be worth using manual
> workarea for those sessions anyway...

>=20

> Tanel.

>=20

> ----- Original Message -----
> From: "Christo Kutrovsky" <kutrovsky.oracle_at_gmail.com>
> To: <tanel.poder.003_at_mail.ee>
> Cc: "ORACLE-L" <oracle-l_at_freelists.org>
> Sent: Friday, May 13, 2005 9:19 PM
> Subject: Re: Shrinking PGA of snp processes

>=20


> > Tanel,

> >

> > I've noticed that the growth of memory is done in steps. And those

> > steps become progressivelly large, until it can no longer allocate

> > memory.

> >

> > Even on 64 bit systems, a single workarea is limited to 4 gb. And

> > because of this step allocations, allocation actually stops at arround

> > 3.5 gb (instead of just under 4gb).

> >

> > Have you found any way to control the step, or at least the maximum

> > step during step allocation ?

> >

> > I wonder when they will start to support the Linux hugetlb pool for pga

> > mem=3D

> > ory.

> >

> > I've noticed that it will never de-allocate the memory, if you are

> > under your pga_aggregate_target. But once you start using it in
> > another process, it will de-allocate it.

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 13 2005 - 17:14:24 CDT