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

   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.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

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?


Other related posts: