Re: PGA usage

You could explain the query and see how the database (probably) is processing this query, and use a 10046/8 trace to see what the session is doing on execution time.

PGA memory allocated for sort or hash is limited and will go to the temporary tablespace.

In the ORA-4030 error message is displayed what kind of allocation is done.

frits

On 7/10/07, Yavor Ivanov <Yavor_Ivanov_at_stemo.bg> wrote:
>
>
> Hello, frits
>
> Yes, the /3GB switch is set. And I am using the folowing query to
> identify who is eating the PGA:
> select sysdate q_time, s.sid, n.name, s.value
> from v$statname n, v$sesstat s
> where n.statistic# = s.statistic#
> and n.name in ('session pga memory')
> order by 4 desc, 2;
>
> The report is just one nasty query, which joins lots of tables and
> calls some very basic pl/sql functions about a gazillion times - no arrays,
> no cursors, nothing special. Just big sorts and lots of hash joins in the
> plan, and basically everything you can think of when you think about
> nightmare execution plans. And no hints, by the way.
> But, even if it will be much slower, I just can't understand why
> is all this happening in memory and not in TEMP. This leads to whole
> database instability - any session can get ora-4030 during the report, when
> the memory reaches 3 GB.
>
> --
> Regards,
> Yavor Ivanov
> Senior Database Expert
> Stemo Ltd
>
> On Tue, 10 Jul 2007 17:14:57 +0300, Frits Hoogland <
> frits.hoogland_at_gmail.com> wrote:
>
> > Yavor,
> >
> > I assume the /3GB switch is set (otherwise the memory available in
> userspace
> > is limited to 2GB)
> >
> > The PGA memory can be monitored per session using v$sesstat. Can you
> > elaborate on what the report is actually doing? (I suspect a pl/sql
> table is
> > populated, which is kept in the PGA and doesn't get to the assigned
> > temporary tablespace like sort data or hash data.)
> >
> > AFAIK, the amount of PGA memory which can be allocated by a session is
> only
> > limited to operating system imposed limits.
> >
> > frits
> >
> > On 7/10/07, Yavor Ivanov <Yavor_Ivanov_at_stemo.bg> wrote:
> >>
> >> Hello, gurus
> >>
> >> I have some problems with Oracle 10.2.0.3 on Windows 2003
> 32-bit.
> >> We have PGA_AGGREGATE_TARGET set to 350 MB and a SGA of 2200 MB. Some
> of the
> >> sessions are shared server ones (which take memory from the large
> pool), and
> >> others are dedicated. Sometimes, when some big-fat report is running,
> the
> >> actual PGA allocated grows up to 550 MB and more. Then we are getting
> >> ora-4030, because we hit the 3 GB limit of 32 bit windows.
> >> In this cases I can see that one dedicated connection (the one
> >> running the big report) has allocated up to 280 MB PGA (this is, one
> >> session!). We are not using parallel things, if it matters at all.
> >> I know PGA_AGGREGATE_TARGET is just a target, not a limit. But
> I
> >> wonder is there any way to limit how much PGA can a single session
> consume.
> >> I thought there is a rule stating no session can eat more than 10% than
> the
> >> total target, but this proved to be wrong.
> >>
> >> --
> >> Regards,
> >> Yavor Ivanov
> >> Senior Database Expert
> >> Stemo Ltd
> >> --
> >> http://www.freelists.org/webpage/oracle-l
> >>
> >>
> >>
> >
>
>

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