Re: Dan Tow's SQL formula

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 26 Feb 2009 06:22:41 -0800 (PST)
Message-ID: <281358.28798.qm_at_web80607.mail.mud.yahoo.com>

I figured out what that 0.225274725 number is. Create an index on mgr column. For each distinct mgr value, generate 10053 trace for SQLs

select * from emp where mgr=<each of the mgr values>

Find all index selectivities (ix_sel) in the trace file(s). Get a weight-modified average of them as follows (in desc order of mgr value):

(0.15385*2+0.38462*5+0.076923*1+0.076923*1+0.23077*3+0.076923*1)/14 = .22527707142857142857

where the numbers 0.xxx in the parentheses are index selectivities and the numbers they're multiplied by are the number of occurrencies for the corresponding mgr for which the ix_sel is obtained. 14 is the number of rows.

But it still needs some work to match this calculation with Dan's formula.

Yong Huang

• On Thu, 2/26/09, amonte <ax.mount_at_gmail.com> wrote:

> From: amonte <ax.mount_at_gmail.com>
> Subject: Re: Dan Tow's SQL formula
> To: yong321_at_yahoo.com
> Cc: oracle-l_at_freelists.org
> Date: Thursday, February 26, 2009, 1:12 AM
>
> I am having some private mailing with Dan, basically I came
> up with 3
> possible selectivities
>
>
>
> 1. Dan's formula gets a selectivity of 0.225274725
> 2. 1/NDV, 0.166666667
> 3. With histograms for different value we get different
> result if we use
> literals and not binds so for example mgr = 7698 we would
> get a selectivity
> of *count(mgr) where mgr = 7698 / count(mgr)* which is 5/13
> which is
> 0.384615385. But for mgr = 7782 we would get an selectivity
> of 0.076923077
> (much better and lower selectivity) but with binds we know
> what is the
> problem, the bind peeking
>
> Thanks for the reply
>
>
> Alex
      

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 26 2009 - 08:22:41 CST