Re: CBO irregularity
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jun 2004 17:12:00 +0100
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@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxx 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@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:
