Re: How to tune SQL to avoid ORA-03232 ?

oerr ora 3232

03232, 00000, "unable to allocate an extent of %s blocks from tablespace %s"

// *Cause:  An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value
//          that is greater than the tablespace's NEXT value
// *Action: Increase the value of NEXT for the tablespace using
//          ALTER TABLESPACE DEFAULT STORAGE or decrease the value of
//          HASH_MULTIBLOCK_IO_COUNT.

Run the query again, but before doing so, execute: alter session set events '10104 trace name context forever, level 1';

You will get a trace file of the hash activity, and near the top of the file you could get some basic stats about the resource usage. Look for a chunk lke the following:

Number of slots: 13
Cluster (slot) size: 36864
Multiblock IO: 9
Bit vector memory allocation: 26214
Per partition bit vector length: 2048

The multiblock IO tells you the minimum size (in blocks) to which you must set your temp tablespace extent size. (possibly add one to it). I suspect you will get a value around 20 blocks.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

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





Jan Gelbrich wrote in message ...


>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

>

>