Re: Strange behaviour of Cramer query

From: joel garry <joel-garry_at_home.com>
Date: Mon, 29 Jun 2009 11:43:42 -0700 (PDT)
Message-ID: <21e6ef5d-5845-4372-ad56-93078ad5919b_at_c9g2000yqm.googlegroups.com>



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.
>
> --http://mgogala.frtia.com

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.

jg

--
_at_home.com is bogus.
http://www.newscientist.com/article/mg20227135.700-cash-machines-hacked-to-spew-out-card-details.html
Received on Mon Jun 29 2009 - 13:43:42 CDT

Original text of this message