Oracle-L: temp tables in explain plan

From: Gene Gurevich <gurelei_at_yahoo.com>
Date: Mon, 22 Sep 2003 11:04:40 -0800
Message-ID: <F001.005D0B81.20030922110440@fatcity.com>

hi all:

Oracle's explain plan for a report I'm trying to tune is referring to several temp tables. Is there a way for me to see any info aobut these tables. Here is my explain plan:
1.1184788883 SELECT STATEMENT (56700, 5386500, 1184788883)

  2.0 TEMP TABLE GENERATION      (, , )
  2.0 TEMP TABLE GENERATION      (, , )
  2.0 TEMP TABLE GENERATION      (, , )
  2.0 TEMP TABLE GENERATION      (, , )
    3.1 SORT GROUP BY     (56700, 5386500,
1184788883)PARALLEL_TO_SERIAL
      4.1 SORT GROUP BY     (56700, 5386500,
1184788883)PARALLEL_TO_PARALLEL
        5.1 HASH JOIN      (38394814629,
3647507389755, 270776)PARALLEL_COMBINED_WITH_PARENT
          6.1 TABLE ACCESS FULL ORA_TEMP_1_A299   


(56700, 2041200, 5)PARALLEL_FROM_SERIAL

          6.2 HASH JOIN      (298626336, 17618953824,
173326)PARALLEL_TO_PARALLEL
            7.1 TABLE ACCESS FULL EMAIL_OFFR    (30,
330, 1)PARALLEL_FROM_SERIAL
            7.2 HASH JOIN      (298626336,
14334064128, 172831)PARALLEL_TO_PARALLEL
              8.1 PARTITION RANGE ALL  1 2 12 (, ,
)PARALLEL_COMBINED_WITH_PARENT
                9.1 TABLE ACCESS BY LOCAL INDEX ROWID

SENT_EMAIL 1 2 12 (1262850, 18942750,
170290)PARALLEL_TO_PARALLEL

                  10.1 BITMAP CONVERSION TO ROWIDS    


(, , )

                    11.1 BITMAP AND      (, , )
                      12.1 BITMAP MERGE      (, , )
                        13.1 BITMAP KEY ITERATION     


(, , )

                          14.1 SORT BUFFER     (, , )
                            15.1 TABLE ACCESS FULL
ORA_TEMP_1_A298    (16360, 212680, 7)
                          14.2 BITMAP INDEX RANGE SCAN
SENT_EMAIL_FK1_X 1 2 12 (, , )
                      12.2 BITMAP MERGE      (, , )
                        13.1 BITMAP KEY ITERATION     


(, , )

                          14.1 SORT BUFFER     (, , )
                            15.1 TABLE ACCESS FULL
ORA_TEMP_1_A299    (16360, 212680, 7)
                          14.2 BITMAP INDEX RANGE SCAN
SENT_EMAIL_FK2_X 1 2 12 (, , )
              8.2 TABLE ACCESS FULL ORA_TEMP_1_A298

(1671844, 55170852, 131)PARALLEL_FROM_SERIAL
I see two tables ORA_TEMP_1_A298 and ORA_TEMP_1_A299. They must be related to nested query in the WHERE clause. But I only have one query like this, but two tables. Is there an explanation for this?

thanks
Gene

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gene Gurevich
  INET: gurelei_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 Mon Sep 22 2003 - 14:04:40 CDT