Re: Unexpected Logical I/O count

I may be wrong with version numbers, but I think 8.1.7.0 - 2 were versions where the root block of the index on the inner table of a nested loop was subject to a 'get' for every row in the driving table instead of a 'pin' for the database call.

I forget the bug number, but your statistics look about right for this to be the problem.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"Matt" <mccmx_at_hotmail.com> 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.)

>