About HASH_MULTIBLOCK_IO_COUNT

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