Re: Shrinking PGA of snp processes
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: zhu chao <zhuchao@xxxxxxxxx>
- Date: Fri, 13 May 2005 11:12:38 -0400
Oopsy .. right, snp processes where in 8 :)
Yes, starting from 9i, if you use automatic pga, pga memory is
released back to OS as needed, without exiting the process.
Under certain OSes (solaris for example) it's allocated from
"anonymous" memory, while manual pga is allocated from "heap" memory.
Not sure on the details behind heap vs anon, i just know that auto-pga
can be released on demand.
And with auto-pga in 9i, you can use up to 4 gb (actually 3.5 gb, but
that's kind-a of a bug/limitation) of workarea for single sort. You
can also use multiple areas (say 2 hashesh and 1 sort) and use huge
amounts of memory for bulk/dump queries thus 1 process can use much
more memory, and release it back as needed.
On 5/12/05, zhu chao <zhuchao@xxxxxxxxx> wrote:
> He is using oracle 8i, where there is no PGA target:).
>=20
> PGA memory never shrink and release back to OS after it is
> allocated.Unless the process exit/killed.
>=20
> I don't know what is the major difference in oracle 9i/10g, it made,
> to lelt oracle release the memory and can be used by other session.
>=20
>=20
> On 5/13/05, Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx> wrote:
> > The reason why PGA memory never releases is because you are well under
> > your pga target.
> >
> > There's is no need to schedule a "release" of this memory, unless your
> > target is innapropriate.
> >
> > On 5/5/05, Boivin, Patrice J <BoivinP@xxxxxxxxxxxxxxxxx> wrote:
> > > Thanks.
> > >=3D20
> > > I can't do this randomly or at set intervals during the night though.
> > >=3D20
> > > Patrice.
> > >=3D20
> > > -----Original Message-----
> > > From: zhu chao [mailto:zhuchao@xxxxxxxxx]
> > > Sent: May 5, 2005 12:05 PM
> > > To: BoivinP@xxxxxxxxxxxxxxxxx
> > > Cc: Oracle-L (oracle-l@xxxxxxxxxxxxx)
> > > Subject: Re: Shrinking PGA of snp processes
> > >=3D20
> > > alter system set job_queue_processes=3D3D0;
> > > alter system set job_queue_processes=3D3DX;
> > >=3D20
> > > On 5/5/05, Boivin, Patrice J <BoivinP@xxxxxxxxxxxxxxxxx> wrote:
> > > > I noticed some of the snp processes take a LOT of memory, probably =
thei=3D
> > r
> > > PGA
> > > > grows to accommodate the largest nightly jobs.
> > > >
> > > > Looks like the PGA never shrinks back down, is there a way to force
> > > > processes to release PGA memory once they're done using it (in 8i -=
- we=3D
> > 're
> > > > upgrading soon)?
> > > >
> > > > Other than setting a lower sort_area_size.
> > > >
> > > > Patrice.
> > > > --
> > > > //www.freelists.org/webpage/oracle-l
> > > >
> > >=3D20
> > > --
> > > Regards
> > > Zhu Chao
> > > www.cnoug.org
> > > --
> > > //www.freelists.org/webpage/oracle-l
> > >=3D20
> >
> > --=3D20
> > Christo Kutrovsky
> > Database/System Administrator
> > The Pythian Group
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>=20
> --
> Regards
> Zhu Chao
> www.cnoug.org
>=20
--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
//www.freelists.org/webpage/oracle-l
Other related posts:
