Re: index creation fails because temp tablespace is too small

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 06 May 2005 10:50:11 -0700
Message-ID: <1115401573.200517@yasure>

Jonathan Lewis wrote:
> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1115396206.396494_at_yasure...
>

>>steven wrote:
>>
>>
>>>If utlxpls.sql can estimate temp tablespace usage when building
>>>index,that would be very nice.
>>>
>>>Steven
>>
>>I can not. UTLXPLS.SQL is nothing more than a call to DBMS_XPLAN
>>to view an explain plan.

>
>
>
> But an execution plan includes the column temp_space,
> which is supposed to be exactly what the user wanted.
> And a call to dbms_xplan will populate and report it
> if CPU costing is enabled.
>
> The materialized view advisor uses explain plan in just
> this way to estimate the size of materialized views.
>
> I am not totally convinced that the answer will be correct -
> I haven't tested it thoroughly, but I have some examples
> which are far too small, and one example which is much
> too big, but would be correct if the plan were supposed
> to be reporting the size that "sort_area_size" would have
> to be to do an in-memory sort to create the index.

The column temp_space may be in PLAN_TABLE but I've not seen it in the output of DBMS_XPLAN.

PLAN_TABLE_OUTPUT

---

Plan hash value: 1237609277

---

| Id  | Operation         | Name                 | Rows  | Bytes | Cost 
(%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |   999 |  7992 |     2 
   (0)| 00:00:01 |
|   1 |  NESTED LOOPS     |                      |   999 |  7992 |     2 
   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN | PK_SERVERS           |   141 |   564 |     1 
   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IX_SRVR_INST_SRVR_ID |     7 |    28 |     0 

   (0)| 00:00:01 |

---

PLAN_TABLE_OUTPUT

---

Predicate Information (identified by operation id):

---

    3 - access("S"."SRVR_ID"="I"."SRVR_ID")

15 rows selected.

SQL> and, at least with my testing (10.1.0.4) it is not populated by running an explain plan.

SQL> explain plan set statement_id = 'abc' for

   2 select s.srvr_id
   3 from servers s, serv_inst i
   4 where s.srvr_id = i.srvr_id;

Explained.

SQL> select temp_space from plan_table;

TEMP_SPACE

---

8 rows selected.

SQL> Am I missing something? Hopefully not something too obvious.

Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri May 06 2005 - 12:50:11 CDT