Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> explain plan conundrum

explain plan conundrum

From: <ryan_oracle_at_cox.net>
Date: Tue, 04 Nov 2003 10:34:26 -0800
Message-ID: <F001.005D58FA.20031104103426@fatcity.com>


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  	 	      	             	 


--

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). Received on Tue Nov 04 2003 - 12:34:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US