Re: Should you still tune queries by LIOs?

There's no substitute for runtime as the ultimate target for minimization. I certainly agree that CPU and LIOs do not necessarily correspond. There's always the well-known exception that table-scan LIO tends to be more expensive than index-driven LIO, because table-scan LIO reads all the rows in a block, where index-driven LIO works only on a subset of the block. A less-well-known (but important) exception is that LIO in a large index range scan can be exceptionally expensive per-LIO. Say, for example, you have a serial_number column, and serial numbers always begin with a couple of digits, but have all combinations of character-type and case after that. A user interface might drive a case-insensitive search with a condition such as (replacing bind variables with constants for clarity):

WHERE Serial_Number LIKE '02%' AND UPPER(Serial_Number) = '02QSCRT123PK'

Setting aside, for now, the fact that a function index on UPPER(Serial_Number) solves the problem, or that it might have been better to require all serial numbers to use all-same-case, a simple index on Serial_Number would deliver a range scan on the LIKE '02%' condition, which might hit 10% or more of the rows in the index, depending on the nature of the serial number ranges. Before going to the table, though, Oracle would check the UPPER() condition, and discard all rows except one. Since the index will be relatively compact this will require fairly few LIOs (in the 1000s, perhaps, but probably not 100s of 1000s). However, most of those LIOs hit every entry in the index leaf block concerned, so they require much more CPU per LIO than the usual LIO requires. I've often found queries like this among the top CPU and runtime consumers, even though they fail to show up on the usual v$sqlarea-driven analyses of top sources of LIO, and do very little physical I/O, because the index blocks are well-cached.

It is certainly true that latches relating to the LIO, particularly LIO to super-hot blocks, can themselves be a bottleneck, and can be a reason to avoid an execution plan that hits super-hot blocks too often, but I find that this is relatively uncommon, *especially* if you've tuned the highest-cumulative-runtime SQL (which *tends* to produce most of your logical I/O, anyway). In other words, I wouldn't sweat the latch-bottleneck potential of a high-LIO SQL statement if that statement is not also high cumulative runtime.

(Cumulative-runtime = runtime-per-execution*executions-per-week)

If a high-LIO statement ever *is* a major part of a latch bottleneck, it'll automatically end up high-cumulative-runtime from the latch bottleneck, alone, if for no other reason, and a reduction in LIO will almost have to show up as a runtime improvement for that query.

All that said, you still need to have some sort of execution-runtime model in your head to figure out which execution plan, out of potentially billions of options, you actually want, and the ideal model certainly does take account of LIOs, as well as looking at physical I/O, and special CPU costs relating to unusually expensive types of logical I/O. I describe in my book a quite simple model based on the number of rows touched, with a heuristic that tends to especially avoid rows in larger (less-well-cached) tables, that approximates a full-featured runtime model far better than you might expect, and has the advantage of being simple enough for practical manual use. A good model based on all these runtime costs is certainly what the CBO attempts, and any manual method must also use a good model if it is to beat the CBO at its own game. None of that means you should actually *use* a new execution plan favored by your own model, though, if you find that the measured runtime does not meet your model's expectations, and actually runs longer than a known alternative, including, especially, the alternative that the CBO finds for you.

Thanks,

Dan Tow
650-858-1557
www.singingsql.com

Quoting ryan_gaffuri_at_comcast.net:

> I believe its Mogens chapter in the Tales of the Oak Table book where he said
> he found with 10g that LIOs and CPU usage do not necessarily correspend. He
> argues that tuning queries should be explicitly based on elapsed time.
> My understanding of LIOs is that every LIO is a buffer cache latch get, so
> even if you do not use up more CPU you are incurring serialization and under
> concurrency can cause performance problems. I have seen queries go from
> 20,000 LIOs down to 300 with a very small performance improvement. Is it
> worth it to spend the time to do this?
> BTW, its a very good book. The chapter by Dave Ensor on the history of Oracle
> is one of the best chapters you can find anywhere. I hope he writes more now
> that he is retired.

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/