Re: index creation fails because temp tablespace is too small

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 May 2005 17:58:43 +0000 (UTC)
Message-ID: <d5gb8j$j9i$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1115401573.200517_at_yasure...
>
> 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.

Try running a query that requires some temp space. The column is not reported if none of the rows have populated it. (The default is null, not zero).

rem 9.2.0.6
rem optimizer_mode = all_rows
rem system stats have been gathered

explain plan for
select source from sys.source$ order by source;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1324K| 2528M| | 724K (1)|
| 1 | SORT ORDER BY | | 1324K| 2528M| 5172M| 724K (1)|
| 2 | TABLE ACCESS FULL | SOURCE$ | 1324K| 2528M| | 4128 (2)|

8 rows selected.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005

Received on Fri May 06 2005 - 12:58:43 CDT