Re: are LIOs always relavent?

LIO's going up with partition iteration is possible, especially if your partitioning strategy is not appropriate. Of course, it depends on the nature of your queries, but the basic issue is that the number of table rows you acquire for a given input does not change, but the number of index partitions you may visit to find those rows may go up.

No change in CPU -
  You need to check the type of buffer activity.     Did 'consistent gets - examinations' increase as     the number of LIOs went up, and did the number     of 'consistent gets' that weren't 'examinations' go     down.

    Did the number of 'buffer is pinned count' change     significantly ?
.

    Is the row you do on the rows complex, and therefore     a large fraction of the CPU you use.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> First off. Has anyone seen a spike in LIO do to the partition list iterator
> row source and second off, if my LIOs are so much higher how come I do not see
> an increase in elapsed time or CPU? We tested this understress with up to 100
> threads. Does not appear to affect CPU or response time. Part of that may be
> because we have less waits with partition tables under stress. I will try a
> higher dynamic sampling. However, I am not sure it matters if CPU and elapsed
> time are not effected. I believe Mogens Noorgaard had a chapter in the oak
> table book about some LIOs are more costly than others, but we don't have the
> granualarity to tell yet?
>

--
http://www.freelists.org/webpage/oracle-l