Re: Strange behaviour of Cramer query

From: HansPeter <hans-peter.sloot_at_atosorigin.com>
Date: Fri, 26 Jun 2009 00:54:51 -0700 (PDT)
Message-ID: <5819a289-a6b5-471c-a8ef-4c7b184fafc3_at_j9g2000vbp.googlegroups.com>



Hello,

A lot has been said sofar.

To make things clearer.
The statement I execute has 2 bind variables. Every execution uses the same bind variables.
The big difference in wait events is

  Event waited on                             Times   Max. Wait  Total
Waite
  direct path write temp                      53363
0.18         14.53
  direct path read temp                      298016        0.61
607.8

The direct path events are not present in the quick case.

But the main problem is why are these present in execution N but not in execution N-1.
Some people suggest that this is due to caching. But that does not make sence because the query is also slow after a quick execution a second before.
Furthermore the caching does not apply to temp segments.

It just makes no sense that the same execution plan with the same bind variable and the same rows that are yielded suddenly shows 9 million processed rows for certain row source operations and next time 0 rows and the next time 9 million rows again.

Looks like a bug to me.

regards HansP

On Jun 25, 4:33 pm, 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.freehostia.com
Received on Fri Jun 26 2009 - 02:54:51 CDT

Original text of this message