Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
Date: Thu, 10 Sep 2009 00:37:47 -0700 (PDT)
Message-ID: <158ce563-5158-43ea-aa01-a8cdfe9daf23_at_q7g2000yqi.googlegroups.com>
On 10 sep, 02:12, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> Yes, there is a skew in the data - it should be more densely packed at
> each end of the range.
>
Unless I made a mistake building the test data, that is not the case: not at each end, but only at *the* end of the range. If I remember right, 4K records for id 1, 1M for id 10000.
> The first execution completed in 5 minutes and almost 5 seconds. The
> second execution completed in 19.39 seconds with a nearly identical
> value for physical block reads.
>
So your physical reads are anything but (the second time round). On my poor PC, 2Gb of memory, 500Mb SGA of which only 200Mb for data, the physical reads mostly are physical indeed.
Why would my system have such trouble fetching 2.5M records for ids 1 to 400, and perform much better fetching 2.8M records for ids 9991 to 10000? The FTS, of course, doesn't care which ids it has to fetch. But for the IS, it seems to matter a lot.
Kind regards,
Gerard Received on Thu Sep 10 2009 - 02:37:47 CDT