Re: Unexpected Logical I/O count

mccmx_at_hotmail.com (Matt) wrote in message news:<cfee5bcf.0410052313.4255fb4c_at_posting.google.com>...
> 8.1.7.2.1 EE on HP-UX 11.
>
> I have a SQL statement which I suspect is taking longer than it
> should.
>
> It executes for 4/5 seconds and visits over 500,000 logical blocks.
>
> I copied the tables and view definition into an 8.1.7.4.1 database on
> NT and found that the same SQL statement executed in 2 seconds and
> only visited half the number of blocks despite using the same plan and
> using the same block size and all other init.ora parameters (I
> actually used the same init.ora file).
>
> I've checked the number of blocks in all of the objects (tables and
> indexes) in the 2 databases and they are almost exactly the same.
>
> Anyone have any idea why this is the case. My first instinct was that
> the extra block visits were for read consistency but a count from
> v$transaction releaved that there were no active transactions at the
> time of the queries. Also the 500,000+ block count is consistently
> repeateable.
>
> What could account for the extra bock visits when the segments are
> made up of the same number of rows and blocks and the optimizer choses
> exactly the same plan..? (The elapsed time is almost totally CPU
> related - i.e. no waits.)

Migrated rows? Deleted rows and high watermark? Screwy indexes from deletes?

How does your app delete rows? Perhaps it is reusing segment space reclaimed in the hp version and has to hunt around in the actual file for the blocks, but everything is sorted out in the W. How do your pios compare? A more obscure possibility is multiblock readahead is scooping up fewer of the about-to-be-wanted blocks on the hp.

jg

--
@home.com is bogus.
http://osdir.com/Article1722.phtml