RE: Help me solve this cardinality puzzle

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxx> 
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@xxxxxxxxx>:
>
>
> >
> >
> >
> > 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-l


Other related posts: