Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Creating Histograms

RE: Creating Histograms

From: Cary Millsap <>
Date: Wed, 21 Jul 2004 18:36:18 -0500
Message-ID: <004601c46f7b$8609db70$e6ea8640@CVMLAP02>

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.
* 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 for schedule details...

-----Original Message-----
From: =

On Behalf Of Jonathan Lewis
Sent: Wednesday, July 21, 2004 2:41 PM
Subject: Re: Creating Histograms

Note in-line


Jonathan Lewis The Co-operative Oracle Users' FAQ 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 =
[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.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Wed Jul 21 2004 - 18:34:29 CDT

Original text of this message