| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Physics of the FILTER operation within SQL_PLANE. (aka advice to developers from a DBA)
Ah, finally I understand that you're looking for advice to give to your
developers, and as regards LIO simply pointing out that is it not an
inviolate metric.
First, you have to decide whether they should develop with a guess at the texture of the data on which they will be operating. Second, you have to decide whether you know the relative scarcity of various system resources. (In these two recent threads, you've seemed to be biased toward CPU being most scarce, and often that is the limiting factor. But not always.)
If your decision on the first question is that they should not consider the texture of the data, then you need to have them "think RULE" (but not use the old tweaks to invalidate use of indexes). Usually this is not the case, and even though relative amounts in some big tables will vary, you have a good idea that certain tables will be relatively modest in size "lookup" tables. What do I mean by "think RULE?" Get the old chart (either one of the two or three that were all wrong in some regard, but close enough) that shows how the RULE based optimizer ranks path choices. To a great extent, those are still valid, though of course the added methods such as hash and partitioning are entirely absent.
If your decision on the first question is that you have a pretty good idea
what the texture of the data will be, then you construct a sandbox and test
COST.
The sandbox can be abstract, as your example, or closer to the actual, and
should include your best guess at indexes.
Then as you've done, when you consider two iso-functional solutions to the
problem at hand, you need a metric to pick one. Unfortunately, unless one or
another system resource is so clearly the limited resource over all the
others (which means someone has constructed an unbalanced system
configuration),
you need to consider these relatively to the scarcity of the resource and
take into consideration whether the solution is likely to scale. Now
usually, the lower LIO of two well constructed iso-functional queries with
no extraneous computations will tend to be the cheaper solution in CPU as
well. When a pair of queries strays from this thumbrule, it is time to be
individually analytic about the situation and to consider whether there are
trade-offs in resource consumption. All else being equal, conserve the
resource that you expect to be the most precious in the system you are
building. Then you get to the question of things being somewhat leaky if CPU
is your key resource. If it is that close a call on a particular query,
you've probably already spent unjustified expense on over engineering the
solution. However, if this will be a central and frequently executed query
that is of such high value that only the very best solution will do, you'll
need to very carefully control the entire machine, set matching initial
conditions, just run that one query, and compare total system costs for just
that one query.
Finally, I will say this: The best solutions are usually the query structures that naturally model the question you are asking, devoid of all planning ahead for dirty tricks often used to "fix" broken high-cost queries that turn up in production. So when you're building a system, if you just ask the developers to do that, rather than trying many alternatives to see which performs best against selected metrics, you're likely to get good solutions. There are several texts out there that have examples of what tends to work well constrasted to what tends to Chew CPU, LIO, latches, and PIO(CCLLP). Usually you can look at initial efforts by new developers and detect patterns that will either tend to work well or tend to CCLLP. Fostering a common style is also a good thing. Then you take that initial solution and consider the logical process it supports. Does the resource consumption seem to be in line with the function? If so, you're done (unless this is that query where nothing less than the best possble will do.)
Good luck! It is a pleasure to see someone trying to inject reason into the development process. I spend far too much time fixing stuff that was based on an ill conceived notion of good SQL and then used as a template for whole systems.
mwf
PS: very nice "look at these first" laundry list by JL down below for running systems.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of J.Velikanovs_at_alise.lv
Sent: Wednesday, June 30, 2004 5:11 AM
To: oracle-l_at_freelists.org
Subject: Re: Physics of the FILTER operation within SQL_PLANE.
It is very cool!
Life not so easy and it is mean we will not stay without work ;)
As you pointed: we speaking about “trouble-shooting” of existing
production.
At the moment I am trying to figure out the best method for Developers, for system, which will be in production after some time. What will be your recommendation for developer? What is the best meted to figure out the best execution plan (SQL) for some business activity? As we know LIO not the best indicator.
Jurijs
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org
30.06.2004 11:42
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: Physics of the FILTER operation within
SQL_PLANE.
Your observation is absolutely true.
You don't even have to use two separate queries to demonstrate the point, all you have to do is change the sql*plus arraysize on a simple
select non-indexed_column
from table
where primary_key between X and Y
If you check v$sql, you will see that the LIO count goes up as the arraysize goes down.
Identifying statements with high LIO counts is a necessary, but not sufficient, tuning exercise.
Identifying statements with high PIO counts is a necessary, but not sufficient, tuning exercise.
Identifying statements with high CPU usage is a necessary, but not sufficient, tuning exercise.
Identifying statements that do a lot of sorting is a necessary, but not sufficient, tuning exercise.
Identifying statements with a high-latch impact is a necessary is another necessary, but not sufficient, tuning exercise in a system with a high degree of concurrency.
I could go on - but I'd be sure to miss a few.
It's usually relatively easy to spot the big hitters, whatever you do, and work out how to fix them. But the point does come where the ONLY possible way to do trouble-shooting is to breathe down the neck of someone who has a complaint, and watch what the system does as they go through the task that giving them grief. i.e. Cary's 10046 approach.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
One important conceptual personal conclusion I got from this investigation:
vvvvvvvvvvvvvvvv
BUFFERs gotten (LIO) executing query. ^^^^^^^^^^^^^^^^Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
 |
 |