Re: Strange behaviour of Cramer query

From: joel garry <>
Date: Mon, 29 Jun 2009 11:43:42 -0700 (PDT)
Message-ID: <>

On Jun 25, 7:33 am, Mladen Gogala <> wrote:
> On Wed, 24 Jun 2009 09:35:40 -0700, joel garry wrote:
> > See
> > My crystal ball tells me it must be something like housekeeping in the
> > area where it keeps the temp results of the buffer-sorta-sort, perhaps a
> > bug.  Do you see lots of memory usage increase from the OS side every
> > time you run this repeatedly?  Or maybe since it is in the same session,
> > Oracle thinks (correctly or not) it will be needing the previously
> > buffered data again, and so hangs onto it or even cartesian joins it.
> > What was it exactly the stored procedure did?
> > jg
> Joel, just to clarify, buffer sort is not actually a sort. What happens is
> that if Oracle predicts that the block will be re-used by the same query
> again, it will be copied to PGA. In other words, it's the buffers that are
> sorted, not the data. Buffer sort is an internal optimization, not a data
> access method. I am not really sure why is it even shown in the plans.
> --

Note that in the Oracle forums, he stated "I discovered that increasing the pga_aggregate_target makes the phenomenon to disapper." Jonathan's explanation there is quite illuminating.

I'm wondering if there are some things that should be more clearly stated in the plan regarding pga operations.


-- is bogus.
Received on Mon Jun 29 2009 - 13:43:42 CDT

Original text of this message