I cant sql trace it now. I hae run statspack. this query is running now and I dont
want to run another copy with a trace on until this finishes, since I dont want to
suck up resources. Im at a loss as to where the 20 billion rows comes from in this
explain plan? Everything including the indexes
to be that is the problem. any possible way to tune a join against such few
distinct values
>
> From: <[EMAIL PROTECTED]>
> Date: 2003/11/04 Tue PM 01:34:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: explain plan conundrum
>
> I
It's not 20 billion rows but 20 billion bytes. It's "only" 1 billion rows.
The cartesion product of 5K rows and 366K rows is 1830M rows or 1.8G. If
the join predicate is not very selective, .5 or .33 for example, that would
yield an estimated join cardinality of 1G (after rounding).
At 11:34 AM
PM 01:34:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: explain plan conundrum
>
> I cant sql trace it now. I hae run statspack. this query is running now and I dont
> want to run another copy with a trace on until this finishes, since I dont w
Hi, Ryan,
Where's the 20 billion rows? There's 1 G rows and 20 G bytes.
What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES for
TABLE2? Did you analyze using ANALYZE command or DBMS_STATS?
Yong Huang
--- [EMAIL PROTECTED] wrote:
> I cant sql trace it now. I hae run statspack.
bad decision.
how do i analyze why it made a bad join order decision? hints like this are a stop gap
fix.
>
> From: Yong Huang <[EMAIL PROTECTED]>
> Date: 2003/11/04 Tue PM 02:09:30 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: exp
Could you please post the entire sql and plan and statistics of the tables
and indexes so that we can comment on it rather than letting us guess on
selective bits of the entire problem.
At 01:29 PM 11/4/2003, you wrote:
everything is analyzed. For all indexes, for all indexed columns.
I used an
t; To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: explain plan conundrum
>
> Hi, Ryan,
>
> Where's the 20 billion rows? There's 1 G rows and 20 G bytes.
>
> What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES f
optimizer made a bad decision.
>
> how do i analyze why it made a bad join order decision? hints like this
are
> a stop gap fix.
> >
> > From: Yong Huang <[EMAIL PROTECTED]>
> > Date: 2003/11/04 Tue PM 02:09:30 EST
> > To: Multiple recipients of list ORACLE-
other table and that filters out enough
> rows that that join should go first. The optimizer made a bad decision.
>
> how do i analyze why it made a bad join order decision? hints like this
are
> a stop gap fix.
> >
> > From: Yong Huang <[EMAIL PROTECTED]>
> > Date: 2
However, since it is a join predicate, the histogram data can not be used.
The CBO uses the density values of the join column(s) to derive the join
selectivity. The density value of a column changes (from 1/num_distinct)
when you collect a histogram. If you create a frequency histogram (aka
val
and only 4 distinct values on the join column) oracle
chooses the proper join 'order'
>
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/11/05 Wed AM 11:49:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: Re: exp
are histograms only used to determine whether to use an index or join type, not join
order?
>
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/11/05 Wed AM 11:49:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re:
> Date: 2003/11/05 Wed AM 11:49:26 EST
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: Re: Re: explain plan conundrum
> >
> > However, since it is a join predicate, the histogram data can not be used.
> > The CBO uses the d
The join order of an access plan - in the absence of any leading or ordered
hints - is determined strictly like everything else by the CBO: the join
order with the lowest estimated cost wins. And the selectivity and
cardinality estimates play a big role in determining the cardinality and
thus c
Histograms are only used to refine the selectivity of a predicate. This in
turn determines the cardinality estimate and various costs such as index
access cost and then of course join costs (NL, sort-merge, and hash) and
join cardinality. This ultimately will drive the decision whether a
partic
16 matches
Mail list logo
