RE: Physics of the FILTER operation within SQL_PLANE. (aka advice to developers from a DBA)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Jun 2004 09:19:43 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of J.Velikanovs@xxxxxxxx
Sent: Wednesday, June 30, 2004 5:11 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
30.06.2004 11:42
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
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
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
----- Original Message -----
From: <J.Velikanovs@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 29, 2004 10:45 AM
Subject: Re: Physics of the FILTER operation within SQL_PLANE.
One important conceptual personal conclusion I got from this
investigation:
vvvvvvvvvvvvvvvv
We can't compare two SQL executions plans (for one query) efficiency by
BUFFERs gotten (LIO) executing query.
^^^^^^^^^^^^^^^^
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » RE: Physics of the FILTER operation within SQL_PLANE. (aka advice to developers from a DBA)
