7 Replies Latest reply: Dec 4, 2012 4:18 AM by user522961 RSS

    explain plan gives an estimate for TEMP

    user522961
      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
          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....
            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
              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
                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
                  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
                    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/