This discussion is archived
7 Replies Latest reply: Dec 4, 2012 2:18 AM by user522961 RSS

explain plan gives an estimate for TEMP

user522961 Newbie
Currently Being Moderated
Hi all,
is it true :
An explain plan gives an estimate for TEMP space usage.

In my eplain plans I can not see that.

Thank you.
  • 1. Re: explain plan gives an estimate for TEMP
    Fran Guru
    Currently Being Moderated
    Hi all,
    is it true :
    An explain plan gives an estimate for TEMP space usage.
    
    In my eplain plans I can not see that.
    
    Thank you.
    with this information what do you think we can do?
  • 2. Re: explain plan gives an estimate for TEMP
    Aman.... Oracle ACE
    Currently Being Moderated
    user522961 wrote:
    Hi all,
    is it true :
    An explain plan gives an estimate for TEMP space usage.

    In my eplain plans I can not see that.
    You should search in a more better way,
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12519780526076

    Aman....
  • 3. Re: explain plan gives an estimate for TEMP
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please review:
    Temporary Tablespace-usage
    Temporary Tablespace
    HIGH Temp tablespace usage

    Reagrd
    Helios
  • 4. Re: explain plan gives an estimate for TEMP
    Dom Brooks Guru
    Currently Being Moderated
    is it true :
    An explain plan gives an estimate for TEMP space usage.
    In my eplain plans I can not see that.
    Then one of the following applies:
    1. No temp space is estimated to be used.
    2. You're using an older version of Oracle
    3. You're using a older version of the plan table (in which case dbms_xplan will tell you this).

    SQL> create table t1 as
      2  select o.* from dba_objects o, (select rownum from dual connect by rownum <= 100);
    
    Table created.
    
    SQL> select count(*) from t1;
    
      COUNT(*)
    ----------
      11255700
    
    SQL> explain plan for
      2  select *
      3  from   t1 t1
      4  ,      t1 t2
      5  where  t1.object_id = t2.object_id;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1734879205
    
    -----------------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |  1531M|   590G|       |   309K  (3)| 01:01:55 |
    |*  1 |  HASH JOIN         |      |  1531M|   590G|  2313M|   309K  (3)| 01:01:55 |
    |   2 |   TABLE ACCESS FULL| T1   |    11M|  2187M|       | 36682   (1)| 00:07:21 |
    |   3 |   TABLE ACCESS FULL| T1   |    11M|  2187M|       | 36682   (1)| 00:07:21 |
    -----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
    
    Note
    -----
       - dynamic sampling used for this statement
    
    19 rows selected.
    
    SQL>
  • 5. Re: explain plan gives an estimate for TEMP
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.
    I'm in 11g R2 but my explain plan is
    SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
                                                                                                                                                
    ----------------------------------------------------------------------------------------------                                              
    | Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)|                                              
    ----------------------------------------------------------------------------------------------                                              
    |   0 | SELECT STATEMENT                   |                    |     1 |   255 |  1009   (0)|                                              
    |   1 |  TABLE ACCESS BY INDEX ROWID       | PS_NAME_PREFIX_LNG |     1 |    12 |     1   (0)|                                              
    |*  2 |   INDEX UNIQUE SCAN                | PS_NAME_PREFIX_LNG |     1 |       |     0   (0)| 
    
    Note                                                                                                                                        
    -----                                                                                                                                       
       - 'PLAN_TABLE' is old version   
    As you see I have not tempsapce column.
    How to avoid /resolve "old version ?

    Regards.
  • 6. Re: explain plan gives an estimate for TEMP
    Dom Brooks Guru
    Currently Being Moderated
    Why would expect that part of the execution plan to use temp space?

    Regarding the old plan table, see:
    http://jonathanlewis.wordpress.com/2010/01/25/old-plan_table/
  • 7. Re: explain plan gives an estimate for TEMP
    user522961 Newbie
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points