Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> About HASH_MULTIBLOCK_IO_COUNT

About HASH_MULTIBLOCK_IO_COUNT

From: Dino Hsu <dino1_nospam_at_ms1.hinet.net>
Date: Sat, 28 Jul 2001 18:10:05 +0800
Message-ID: <ub35mtc1p7mb00q40slspnneqqldlpusek@4ax.com>

Dear all,

I encountered a Transformer error after 8.0.5 to 8.1.7 migration: ORA-03232: unable to allocate an extent of 8 blocks from 'tablespace 9'

Tablesapce 9 is actually a temporary tablespace (v$tablespace), whose NEXT is 80K.

I checked <<Oracle8i Error Message>>:

ORA-03232 unable to allocate an extent of string blocks from tablespace string

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. I also checked <<Data Warehousing Guide>>

HASH_MULTIBLOCK_IO_COUNT
The recommended value is 4.

This parameter specifies how many blocks a hash join reads and writes at once. Increasing the value of HASH_MULTIBLOCK_IO_COUNT decreases the number of hash buckets. If a system is I/O bound, you can increase the efficiency of I/O by having larger transfers per I/O.

Because memory for I/O buffers comes from the HASH_AREA_SIZE, larger I/O buffers mean fewer hash buckets. There is a trade-off, however. For large tables (hundreds of gigabytes in size) it is better to have more hash buckets and slightly less efficient I/Os. If you find an I/O bound condition on temporary space during hash joins, consider increasing the value of HASH_MULTIBLOCK_IO_COUNT.

Therefore, I add the missing HASH_MULTIBLOCK_IO_COUNT = 4 (default to 0) in init.ora and shutdown/startup the database, the error is gone.

I think I have fixed the problem, but I don't fully understand why. Can anyone clerify this for me? Thanks in advance.

Dino Received on Sat Jul 28 2001 - 05:10:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US