Re: Creating Histograms

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 21 Jul 2004 20:40:49 +0100
Message-ID: <00ec01c46f5a$a3afe550$7102a8c0@Primary>

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_at_comcast.net> To: <oracle-l_at_freelists.org> 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_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 - 14:42:01 CDT