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 ----- Original Message ----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Friday, February 08, 2002 1:28 AM > 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 > [EMAIL PROTECTED] > 214.954.1781 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Larry Elkins > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
