Re: Creating Histograms
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 20:40:49 +0100
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
----- Original Message -----
From: <ryan.gaffuri@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, July 21, 2004 8:25 PM
Subject: RE: Creating Histograms
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.
----------------------------------------------------------------
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:
