| 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: Creating Histograms
Jonathan, would you agree that it's fair to say that, "Cost is =
irrelevant
for virtually every purpose other than comparing it with other costs?"
I know that it may be possible to find some factor f for which (response time) =3D f * cost, but I think you and Tom would both say that cost is predominantly of value as a measure that allows CBO to RANK the expected performance of competing query execution plans. Yes?
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San =
Francisco
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Wednesday, July 21, 2004 2:41 PM
To: oracle-l_at_freelists.org
Subject: Re: Creating Histograms
Note in-line
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 July 20th
Cost is irrelavent. Ignore it. Its strictly internal to Oracle. You =
cannot
compare costs of two different plans. This is all over asktom.
The primary stats to look at are:(though other stats have uses).
[JL] This is one of the very few points where I disagree with Tom,
[JL] In priniciple the cost of a query "ought" to be directly =
correlated
[JL] to the run-time of a query. There are various reasons why theory
[JL] and practice do not agree - but when this is the case, you need to
[JL] work out if
[JL] a) You have found a bug in the CBO
[JL] b) You have some bad statistics
[JL] c) You have a situation that the CBO cannot recognise.
[JL] Addressing the cause of the CBO's failure will give you a generic
[JL] solution to many problems. Ignoring the error on a specific SQL
[JL] statement will lead you to tweak lots of statements without why a
[JL] particular fix works in some cases but not in others.
consistent gets (logical IO) -- 99.99% of the time the query with the =
lower
value is better. Every once in a while I found that a 30% increase in
logical IOs when doing a faster full scan results in a 30% or more =
reduction
in response times. This is on large batch processes only and do not have =
to
stand up under concurrency. I do not have a repeatable case and no idea
why...
Cardinality/Rows -- this one is more important in OLTPs, since you want =
to
primary reduce 'rows' at each operation of your query. The earlier in =
the
plan you can weed out unnecessary rows, the better the performance.
Bytes -- this is typically more useful with full table scans, hash =
joins,
and sort merges, since you are reading all the bytes in the block.
Methodology with this one is the same as Cardinality/Rows. You will use =
this
when doing batch processes and reporting. Less often with OLTPs.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jul 21 2004 - 18:34:29 CDT
 |
 |