RSS Feed

Method R Discussion Group

How do you explain  the Hotsos focus on LIO's versus Oracle's focus on total Cost? For example, OEM does not include LIO's in their plan tuning section.  They only include cost stats.

Logical IOs represent the work actually done when processing a statement.  Cost is a guess as to how much work will be done.  The benefit of costing is that costing can be done without running the statement. However if the statistics are off or the parameters used by the optimizer are not optimal, this can give costs that don’t match up to reality.  It’s not uncommon to see a plan with a higher cost actually run faster (and use fewer LIOs) then a plan a lower cost.

The benefit of using LIOs is that you are looking at the work that really got done.  A plan with lower LIOs will likely run faster everything and will also scale better as you add more users or data to the system. 

Cost based tuning is like just looking at a map to figure out how long a trip will take, but you don’t know if those roads are still useable or if new roads have been built.  Looking at LIOs is like actually taking the trip.  Your estimates based on the map may or may not hold up as you travel.