RE: Re: explain plan conundrum

The default SIZE of the histograms is different for ANALYZE and DBMS_STATS. Be sure you got what you wanted.

Also, as Wolfgang Breitling discusses in his papers, histograms don't deal with all kinds of skew. For example, if two fields in a table are dependent (they both show similar/identical skew), and if both are in your WHERE clause, the optimizer will assume they are independent and its cardinality guesstimate will be a lot lower than the actual number of rows returned (i.e. a table includes fields A & B. 90% of the data values in field A is the number 1, 90% of the data values in field B is number 1. A WHERE clause of 'WHERE A=1' will do just about the same amount of filtering as 'WHERE A=1 AND B=1' but the optimizer thinks the second clause is more selective). This problem can also happen with joins between tables.

Henry

-----Original Message-----
Ryan
Sent: Tuesday, November 04, 2003 6:49 PM To: Multiple recipients of list ORACLE-L

the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. ----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, November 04, 2003 5:04 PM

> First I would take a look at the papers posted at Wolfgang Breitling's
site
> http://www.centrexcc.com/ Next, try and compare the number of rows the
> optimizer expects to bring back at each step (cardinality as seen in
explain
> plan), to the actual number returned (rows as seen in sql_trace=true --
> tkprof; or manually do each part of the query, but be careful because of
the
> filtering). Focus in on a discrepency between these two methods. That is
> where the optimizer is being fooled. It might be because of bad
statistics,
> skewed data, init.ora settings, ...
>
> Henry
>
>
> -----Original Message-----
> ryan_oracle_at_cox.net
> Sent: Tuesday, November 04, 2003 3:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
> everything is analyzed. For all indexes, for all indexed columns.
>
> I used analyze. its the same as dbms_stats, just not as robust. I use it
> when I dont feel like typing out dbms_stats.
>
> Are there optimizer parameters that help the optimizer determine join
order?
> Ive never had to use the 'ordered' hint on the CBO before when everything
is
> analyzed. The difference was huge. Ran for 2 hours and still going, with
the
> hint ran in 45 seconds.
>
> im assuming there are some init.ora parameters that I should check out?
Does
> oracle take into account 'distinctness' of the columns being joined?
> I have 1 table with 366,000 rows and another with 5,000 rows. the columns
> being joined have 4 distinct values each. However, the table with 366,000
> rows joins on its primary key to another 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 <yong321_at_yahoo.com>
> > Date: 2003/11/04 Tue PM 02:09:30 EST
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > 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
for
> > TABLE2? Did you analyze using ANALYZE command or DBMS_STATS?
> >
> > Yong Huang
> >
> > --- ryan_oracle_at_cox.net wrote:
> > > 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 are
> > > analyzed.
> > >
> > > when the two tables involved have 36k and 5k rows involved.
> > > looks like some form of cartesian join, but its not showing up in the
> plan.
> > > The two tables are joined by a column.
> > >
> > > any place to look on this? I know I need the 10046 trace, but I cant
get
> that
> > > yet and it make take 12 hours to get it after this runs.
> > >
> > > select col1,
> > > col2,
> > > col3
> > > from tab1
> > > tab2
> > > where tab1.col1 = tab2.col2;
> > >
> > >
> > > Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> > >
> > > SELECT STATEMENT Optimizer Mode=CHOOSE 1 G 237
> > > HASH JOIN 1 G 20G 237
> > > INDEX FAST FULL SCAN PK1 5 K 11 K 3
> > > TABLE ACCESS FULL TABLE2 366 K 4 M 231
> >
> > __________________________________
> > Do you Yahoo!?
> > Protect your identity with Yahoo! Mail AddressGuard
> > http://antispam.yahoo.com/whatsnewfree
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Yong Huang
> > INET: yong321_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <ryan_oracle_at_cox.net
> INET: ryan_oracle_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Henry Poras
> INET: hporas_at_etal.uri.edu
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ryan
  INET: ryan_oracle_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Henry Poras
  INET: hporas_at_etal.uri.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).