Re: PL/SQL insert on 230K rows causes TEMP tablespace to grow to over 10GB

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 10 May 2006 07:24:23 +0200
Message-ID: <f0u2625jtqlhataluf4u1rfmnlcqtt2jrv@4ax.com>

On 9 May 2006 16:03:58 -0700, "paul_swensen_at_splwg.com" <paul_swensen_at_splwg.com> wrote:

>I am trying to run a PL/SQL statement something like the following:
>
>INSERT INTO customer_table(a, b, c, d, e)
> SELECT ( cust_seq.nextval, /*sequence for this table */
> details.cust_key,
> location.loc_key,
> meter.meter_key,
> cust_idx.id
> FROM cust_idx, details, meter, location
> WHERE cust_idx.id = details.id AND
> cust_idx.loc_id = location.loc_id AND
> cust_idx.meter_id = meter.meter_id AND
> details.most_recent = 'Y' AND
> meter.most_recent = 'Y' AND
> location.most_recent = 'Y';
>COMMIT;
>
>Each table in the statement has roughly 200K rows give or take 10K.
>
>My database is Oracle 9.2.0.4 on Fedora Core 4. When I try and run
>this in I see the tempfile for my temp tablespace grow to consume all
>my available disk space. I have tried freeing up disk space to the
>point where I have more than 10GB but it still takes it all. My entire
>database is only 3GB so this does not seem right! I did notice that
>the 3 columns in the WHERE clause at the end (.most_recent = 'Y') were
>not indexed. I removed them and the PL/SQL ran successfully. However
>when I created indexes on them in their respective tables and re-ran
>the PL/SQL I still saw the same problem.
>
>My temp tablespace was created with the following command:
>
>CREATE TEMPORARY TABLESPACE temp TEMPFILE
>'/home/oracle/oradata/rhodes/temp01.dbf'
>SIZE 1000m AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512k.
>
>I read somewhere on the web that UNIFORM SIZE should be the same as
>SORT_AREA_SIZE and it is.
>
>Your help would be greatly appreciated.

some questions to ask

- did you look at the explain plan for this statement
- are the statistics current
- are optimizer_index_cost_adj and optimizer_index_caching altered

from their default values
- what is the cardinality of the most_recent columns - are those columns indexed.

Somehow you seem to have sort/merge joins or hash joins, and when you remove the most_recent clause you get nested_loop joins. You need to find out why the optimizer makes a different decision for each case. Probably because it doesn't know about the cardinality of the most_recent column (no histogram present), or because the most_recent column isn't properly indexed .

--
Sybrand Bakker, Senior Oracle DBA

Received on Wed May 10 2006 - 00:24:23 CDT