Re: Should you still tune queries by LIOs?
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: ryan_gaffuri@xxxxxxxxxxx
- Date: Tue, 7 Sep 2004 11:24:11 -0500
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@xxxxxxxxxxx:
> 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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
