Re: CBO irregularity

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 9 Jun 2004 17:12:00 +0100
Message-ID: <025e01c44e3c$7f203d00$7102a8c0@Primary>

Build a test cases with a couple of million rows, using dbms_random.normal , dbms_random.value, dbms_random.string, dbms_random.random,
dbms_random.string and trunc(rownum) mod(rownum) functions.

Run sql_trace whilst you are do the histogram generation and see what happens.

The last time I tried it, the impact was brutal as Oracle went through a few passes of trial and error to decide on a good sample size.

I would advise against it, at present: it seems to do too much work and generate too many histograms if left to its own devices.

The impact may be lessened by exercising your data with deliberate query sets that populate col_usage$ selectively, though. I haven't got as far as testing that idea.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

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 May 1st

Original Message ----- From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, June 08, 2004 5:35 PM Subject: RE: CBO irregularity

On "collect histograms", any thoughts on using the AUTO keyword when specifying the number of buckets?

(Sorry...a little behind here -- migrated to 9.2.0.5 this weekend)

TIA,
Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA





----------------------------------------------------------------

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 Jun 09 2004 - 11:08:50 CDT