Re: Strange behaviour of Cramer query
Date: Mon, 29 Jun 2009 11:43:42 -0700 (PDT)
On Jun 25, 7:33 am, Mladen Gogala <mla..._at_bogus.email.com> wrote:
> On Wed, 24 Jun 2009 09:35:40 -0700, joel garry wrote:
> > Seehttp://jonathanlewis.wordpress.com/2006/12/17/buffer-sorts/
> > 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. http://forums.oracle.com/forums/thread.jspa?threadID=919476
I'm wondering if there are some things that should be more clearly stated in the plan regarding pga operations.
-- _at_home.com is bogus. http://www.newscientist.com/article/mg20227135.700-cash-machines-hacked-to-spew-out-card-details.htmlReceived on Mon Jun 29 2009 - 13:43:42 CDT