Re: ORA-03232

On Mon, 14 Jan 2002 17:48:01 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:

>On Mon, 14 Jan 2002 15:38:35 GMT,
>SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch)
>wrote:
>
>>>From the manual (Oracle 8i Error Messages):
>>
>>>"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."
>>
>>>So look up your HASH_MULTIBLOCK_IO_COUNT in V$parameter and adjust it
>>>(or NEXT) accordingly.
>>
>>>Jaap.
>>
>>>On Fri, 11 Jan 2002 19:57:27 GMT,
>>>SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch)
>>>wrote:
>>
>>>snip
>>>
>>>ERROR at line 1:
>>>ORA-03232: unable to allocate an extent of 127 blocks from tablespace
>>>3
>>> snip
>>>
>>>Brian
>>
>>Well, I tried this:
>>
>>SELECT Value FROM V$Parameter WHERE Name = 'hash_multiblock_io_count';
>>
>>VALUE
>>--------------------------------------------------------------------------------
>>0
>>
>>1 row selected.
>>
>>So, I'm not sure that I can decrease it.
>>
>>SELECT NEXT_EXTENT FROM user_tablespaces where tablespace_name =
>>'TEMP';
>>
>>NEXT_EXTENT
>>-----------
>> 65536
>>
>>1 row selected.
>>
>>It seems to be nice.
>>
>>host df -h /u01
>>Filesystem Size Used Avail Use% Mounted on
>>/dev/sda6 22G 16G 4.7G 77% /u01
>>
>>Seems to be room enough to expand.
>>
>> host ls -lh temp*
>>-rw-r--r-- 1 oracle dba 1.0G Jan 11 14:47 temp01.dbf
>>-rw-r--r-- 1 oracle dba 200M Jan 11 14:47 temp02.dbf
>>-rw-r--r-- 1 oracle dba 200M Jan 11 14:47 temp03.dbf
>>
>>Seems to have another some time to go before being full (files are
>>limited to 1 gig). The first file should be empty anyway.
>>
>>Brian
>
>
>OK, I found out a bit more, but I am all foncused and muxed ip.
>
>First. I did a search and came up with the following:
><URL:http://technet.oracle.com/docs/products/designer/doc_library/6i_release2/cmnhlp72/rep_relnotes/rn_rep_6i.htm>
>
><<<
>ORA-03232 during migration of Oracle Designer
>Problem:
>During migration from version 2.x or 6.0 Oracle Designer to version
>6.5 the following error may be reported:
>
>ORA-3232 unable to allocate an extent of %s blocks from tablespace %s
>This is because a join of two tables required a sort operation that
>requested a temporary table segment. The size of the segment requested
>was larger than the "NEXT" extent of the temporary tablespace from
>which it was requested.
>
>Workaround:
>From the problem description it can be seen that there are two
>possible solutions. Firstly, the NEXT extent of the temporary
>tablespace cited in the error could be increased, or the request size
>can be reduced to fit within the NEXT extent.
>
>The preferred solution is to reduce the request size. To do this
>examine the databases init.ora file (or query SYS.V$PARAMETER) to
>determine your database block size. Usually this will be a multiple of
>2K bytes, such as 4096 or 8192. Then issue the sql command:
>
> select next_extent/<DB_BLOCK_SIZE>
> from user_tablespaces
> where tablespace_name = '<THE_TABLESPACE_NAME>'
>replacing <DB_BLOCK_SIZE> with the appropriate value and
><THE_TABLESPACE_NAME> with the name given in the original error
>message.
>
>Take the value calculated and set the init.ora parameter
>hash_multiblock_io_count to be equal to or less than it.
>
>Then restart the database and restart the migration.
><<<
>
>Next extent is 65536, and the DB_BLOCK_SIZE is 8192, thus the request
>size should be no greater than 8. Lo and behold, when I set
>HASH_MULTIBLOCK_IO_SIZE to 9, it fails, but at 8 it runs smoothly.
>(Interesting, it that it finishes under 9 minutes, when the initial
>error appears after 13 minutes.)
>
>So, the question is, what did Oracle want. I had
>HASH_MULTIBLOCK_IO_SIZE set to 0, which means that Oracle decided its
>fate.
>
>The error I got was "ORA-03232: unable to allocate an extent of 127
>blocks from tablespace 3". Which means that Oracle set
>HASH_MULTIBLOCK_IO_SIZE to 127 for this query. That is rather large.
>127 * 8196 = 1040892. That ranks in at ~99.26% of a meg (1024 * 1024 =
>1048576. 1040892 / 1048576 = 0.992671966552734375).
>
>Well, that works, though I am afraid that I may not be aware of al the
>consequences. Besides, Oracle Reference does say "Oracle Corporation
>does not recommend that you set or change the value of this
>parameter". So I'd rather not change it unless I must.
>
>So, with changing HASH_MULTI_BLOCK_IO_SIZE, I did some testing.
>
>I guessed that this had to do with HASH_AREA_SIZE. The Oracle
>Reference mention that it defaults to 2 * SORT_AREA_SIZE. I just
>changes SORT_AREA_SIZE by a factor of 10 (was 10 meg, now its 100), so
>this is obviously a significant difference to HASH_AREA_SIZE. I
>verified it by querying V$Parameter. It jumped from 20 meg to 200 meg.
>
>So, I dutifully tried an ALTER SESSION to SET HASH_AREA_SIZE back to
>20 meg. The error still appeared.
>
>Further, I grabbed the init.ora from the live database, and restarted.
>It still wanted 127 blocks. Now I am completely baffled.
>
>Brian

OK, so the only difference between the test and live DB is that I threw on statistics for the test DB. OPTIMIZER_MODE is set to choose. So, I ALTERed my SESSION in the live DB to set it to ALL_ROWS. I rant the query, and I got the error with the live DB (except that it wanted 128 blocks as opposed to 127).

Further, I set the test DB (my session at least) to RULE, and the error did not show up. Though the query has not yet finished running. It seems to benefit from the CBO.

Could someone help me here? I would like to find the exact problem here, and fix that, rather than blindly changing HASH_MULTIBLOCK_IO_SIZE. Brian

Brian Received on Mon Jan 14 2002 - 14:49:12 CST