RE: Help me solve this cardinality puzzle
Date: Thu, 17 Apr 2008 21:10:56 +0200
Message-ID: <012101c8a0be$c7472ca0$6401a8c0@trivadis.com>
Hi Christo,
Be aware of the fact, that on 10.2.0.3 there is bug related to bind peeking.
In the secription of Bug 5082178 you will read:
In some situations bind peeking can occur when it should not eg: Bind peeking can occur for user binds even if
"_optim_peek_user_binds" is set to FALSE.
This can cause binds to be marked "unsafe" leading to cursors not being shared when they should be.
Full bug description you will find in ML:5082178.8. Oracle 10.2.0.3 is definitely one of the affected versions (although I have noticed the same Problem on 10.2.0.2 several times).
The only relief is to go on 10.2.0.4 ...
HTH. Milen
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christo Kutrovsky
Sent: Wednesday, April 16, 2008 3:01 PM
To: Wolfgang Breitling
Cc: oracle-l
Subject: Re: Help me solve this cardinality puzzle
Oops, seems like I forgot a few details.
- Oracle EE 10.2.0.3
- bind peeking disabled
- purelly looking at cardinality of explain plan
What puzzles me is that for what looks like very similar columns, oracle uses different sources of information for estimating the number of rows returned.
In one case it uses num_rows * density, in other num_rows/ndv. Both have same type of histograms, same data type, same indexes.
The only logical conclusion I am making so far is some kind of sanity check causing it to fall back to use num_rows/ndv instead of density when density < num_rows/ndv.
On Wed, Apr 16, 2008 at 4:50 PM, Wolfgang Breitling <centrex_at_centrexcc.com> wrote:
> When histograms are present the cardinality calculation depends, among
> other things, on whether the predicate value (bind value in your
> case) is
> a) a popular value, which then of course shows up in a row in the histogram
> or
> b) a non-popular value which has a row in the histogram
> or
> c) a non-popular value which has no row in the histogram ( but is still >=
> low_value and <= high_value )
>
>
>
> Quoting Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>:
>
>
> >
> >
> >
> > I've been trying to figure this out all morning.
> > I have a table with 20 or so columns. 2 Of those columns are
> > NUMBER(10) not null and are indexed each with it's own one column
> > index. Both have HEIGHT BALANCED histograms with 254 values. Both
> > have computed DENSITY that is way different then num_rows/ndv. For a
> > query of the type:
> >
> > select * from table where col1 = :b1
> >
> > CBO uses num_rows/ndv
> >
> > for the query of the type
> >
> > select * from table where col2 = :b2
> >
> > CBO uses density
> >
> > I am puzzled here ... what am I missing?
> >
> > Table stats:
> > NUM_ROWS 3613399
> > BLOCKS 1023556
> > EMPTY_BLOCKS 0
> > AVG_SPACE 0
> > CHAIN_CNT 0
> > AVG_ROW_LEN 63
> >
> > (columns names changed)
> >
> > col1:
> > NUM_DISTINCT 3460
> > LOW_VALUE 80
> > HIGH_VALUE C403252106
> > DENSITY 1.7228608595956E-5
> > NUM_NULLS 0
> > NUM_BUCKETS 254
> > LAST_ANALYZED 2008-Apr-16 05:39:26
> > SAMPLE_SIZE 3613399
> > GLOBAL_STATS YES
> > USER_STATS NO
> > AVG_COL_LEN 3
> > HISTOGRAM HEIGHT BALANCED
> >
> > col2:
> > NUM_DISTINCT 102775
> > LOW_VALUE C5035A450E3C
> > HIGH_VALUE C50419610363
> > DENSITY 0.00077343180803863
> > NUM_NULLS 0
> > NUM_BUCKETS 254
> > LAST_ANALYZED 2008-Apr-16 05:39:26
> > SAMPLE_SIZE 3613399
> > GLOBAL_STATS YES
> > USER_STATS NO
> > AVG_COL_LEN 7
> > HISTOGRAM HEIGHT BALANCED
> >
> > Any ideas?
> >
> > --
> > Christo Kutrovsky
> > DBA Team Lead
> > The Pythian Group - www.pythian.com
> > I blog at http://www.pythian.com/blogs/
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
>
>
>
> --
> Wolfgang Breitling
> Centrex Consulting Corporation
>
-- Christo Kutrovsky DBA Team Lead The Pythian Group - www.pythian.com I blog at http://www.pythian.com/blogs/ -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 17 2008 - 14:10:56 CDT