How to tune SQL to avoid ORA-03232 ?

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Wed, 16 Oct 2002 08:57:38 +0200
Message-ID: <aoj2ri$n3o9s$1@ID-152732.news.dfncis.de>

Hello, this time I have a tuning question.

A member of my team has a query thas runs forever and then stops by messaging
ORA-03232: next 15 extents could not be allocated in TS TEMP

The query is:

SELECT /*+ FIRST_ROWS */
   Per.Name,
   P.Pers_ID,
   P.special_NR
FROM
   Bigtable1 P, --1 million rows
   Bigtable2 Per --1 million rows
WHERE
   To_Number(Substr(To_Char(P.Special_Nr),2,4)) in    (
   select distinct s.vendor_id
   from Bigtable3 s --800,000 rows
   where id in

      (
      select az_id
      from Bigtable4 b   --1 million rows
      where  B.Fact_date Between :P_Date_From And :P_Date_to
      and s.id = b.az_id
      )

   )

And P.Pers_ID = Per.ID
And P.Company       = :P_Company
And P.Dept_ID    = :P_Dept_ID

I increased initial and next extents of default storage to 32k in TS TEMP. TS TEMP is still a DMT (i would like to migrate it to LMT, but I am not allowed to for some reason ...),
and it has 1G space. 600MB of it is not fragmentated.

Parameters set:

---

       471 hash_join_enabled
TRUE
       472 hash_area_size
1280000

       473 hash_multiblock_io_count
0 (AFAIK that this parameters does not need to be set as it is calculated by the optimizer)

Oracle EE 8.1.7 on AIX 4.3.3

What is interesting about it: it is an old query is embedded in a Report that ran fine before we migrated from 8.0.6. some months ago. The query is run just once a year, so we discovered the problem too late. On the other hand, it runs in HORA, slowly (600s), but it runs at least.

What is wrong with the query or my settings ? ? I would appreciate any advices.

Jan Received on Wed Oct 16 2002 - 01:57:38 CDT