Re: are LIOs always relavent?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Jul 2006 10:21:10 +0100
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.
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?