Re: Strange behaviour of Cramer query

From: Mladen Gogala <mladen_at_bogus.email.com>
Date: Thu, 25 Jun 2009 14:33:42 +0000 (UTC)
Message-ID: <h201s6$f8p$4_at_solani.org>

On Wed, 24 Jun 2009 09:35:40 -0700, joel garry wrote:

> See http://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.freehostia.com

Received on Thu Jun 25 2009 - 09:33:42 CDT