Re: explain plan conundrum

From: Yong Huang <yong321_at_yahoo.com>
Date: Tue, 04 Nov 2003 11:09:30 -0800
Message-ID: <F001.005D5907.20031104110930@fatcity.com>

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).

Received on Tue Nov 04 2003 - 13:09:30 CST