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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: MAX_IO_SIZE and Temp Next Extent approach to ORA-03232 Error

Re: MAX_IO_SIZE and Temp Next Extent approach to ORA-03232 Error

From: claudio cutelli <c_cutelli_at_hotmail.com>
Date: Fri, 08 Feb 2002 09:17:22 -0800
Message-ID: <F001.0040A124.20020208070824@fatcity.com>

When i had this problem i put initial and next extent size of temporary tablespace
equal or greater than MAX_IO_SIZE.

Example: With MAX_IO_SIZE= 128K
the default storage for the initial and next extent must be >= (128 * 1024) = 131072 bytes.

MAX_IO_SIZE is an Operating system dependant parameter.

you can find MAX_IO_SIZE putting db_file_multiblock_read_count = 1000000 and than
trace a full table scan, look in the trace and find the third field in wait: db file scattered read to find
number of bd_block read for any I/O operation.

Ciao

> Listers,
>
> Environment: Solaris 7 running 64 Bit Oracle 8.1.7.2.x.
>
> Received an ORA-03232 error this morning after bumping up the
HASH_AREA_SIZE
> on a QA box overnight. HASH_MULTIBLOCK_IO_COUNT is 0, and has been 0 for
as
> long as I know, meaning that Oracle calculates the value on the fly for
each
> individual SQL statement. From information found in Metalink forums, etc,
it
> appears that this has popped up on lots of people after bumping up
> HASH_AREA_SIZE. Though I couldn't find anything describing *how* Oracle
> calc's the HASH_MULTIBLOCK_IO_COUNT when set to zero, it appears the
> HASH_AREA_SIZE plays some role, based on Usenet and MetaLink forum
comments.
>
> Anyway, to the question. Some of the Oracle notes say that as long as the
> NEXT extent size for the TEMP tablespace is greater than MAX_IO_SIZE,
which
> is OS dependent (and on Solaris defined by maxphys, I think) the problem
> should go away. But, some folks posting in the forums say that even if
they
> went with something along the lines of 1 MB for initial and next, with a
> max_io_size of 128K, they still encountered the error.
>
> Anyway, the quick fix was to set HASH_MUTLIBLOCK_IO_COUNT to 2 since this
> could be done with an ALTER SYSTEM command. But I am curious what other
> people may have done who have run into with this. We eventually would like
> to go back to a HASH_MULTIBLOCK_IO_COUNT of 0, if only because Oracle
> recommends that (good advice?), and address this through appropriate TEMP
> extent sizing. Heck, you could make the extents pretty large but you would
> like to know up front if the "fix" will work. So that's why I ask for
> feedback here.
>
> A TAR will be opened through the point of contact that handles TAR's. We
> hope to get an "official" word from Oracle on this. But, the fact that
some
> people have said the extent size bigger than max_io_size didn't work has
us
> wondering.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Larry Elkins
> INET: elkinsl_at_flash.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: claudio cutelli
  INET: c_cutelli_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Feb 08 2002 - 11:17:22 CST

Original text of this message

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