Oracle-L: RE: Fwd: Histograms on VARCHAR2 columns ?

From: Nelson, Allan <anelson_at_midf.com>
Date: Fri, 14 Feb 2003 11:40:37 -0800
Message-ID: <F001.0054E171.20030214114037@fatcity.com>

You can see that with a full table scan because of multi-block reads. Generally, assuming blocking reads and multiblock reads, cpu can be low as this op is not cpu intensive. With the first plan you've got read the index, 3 blocks average (head, leaf, and branch) then go get the db block. More cpu here as you have to examine all those blocks and compute a new DBA for each read.

Allan

-----Original Message-----
Sent: Friday, February 14, 2003 11:44 AM To: Multiple recipients of list ORACLE-L

OK, I'm reading thru Wolfgang Breitling's "Fallacies of the Cost Based Optimizer" from the link below. Of course, I've got questions!

The data presented on pages 5 and 6 just don't jive. How many systems can do a query with twice as much physical I/O in less than half the elapsed time and CPU time of another query? From my experience, IO just doesn't work that way, unless the data was cached at the OS or IO subsystem, which would make the test invalid.

Comments???

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI

USA -----Original Message-----
Sent: Thursday, February 13, 2003 4:30 PM To: Multiple recipients of list ORACLE-L

I like white papers. It looks like I'm going to learn something new again!

I'll set aside some time tomorrow to peruse it (I have to go drink beer now). Thanks, Allan! :)

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI

USA -----Original Message-----
Sent: Thursday, February 13, 2003 3:54 PM To: Multiple recipients of list ORACLE-L

Actually, even if you use bind varaibles histograms affect the density number used by the optimizer, see http://www.centrexcc.com/papers where he shows that without histograms the optimizer ususally makes density equal to the reciprocal of the Number of Distinct Values. With histograms in place,this is no longer true.

Allan
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Nelson, Allan
INET: anelson_at_midf.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------