HASH_MULTIBLOCK_IO_COUNT

Jared - we were wrong, there are worse applications than Remedy out there. I may be involved with one now. The application uses massive sql statements generated from a VB front-end connecting to COBOL on the server via ODBC that runs against the database. Some of our power users have been receiving intermittent ORA-03232 errors.

>From the manual:

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.

HASH_MULTIBLOCK_IO_COUNT specifies how many sequential blocks a hash join reads and writes in one IO. When operating in multi-threaded server mode, however, this parameter is ignored (a value of 1 is used even if you set the parameter to another value). Because Oracle computes the value for this parameter based on the query, you need not set the value for this parameter.

The maximum value for HASH_MULTIBLOCK_IO_COUNT varies by operating system. It is always less than the operating system's maximum I/O size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE). This parameter strongly affects performance because it controls the number of partitions into which the input is divided. If you change the parameter value, try to make sure that the following formula remains true:

     R / M <= Po2(M/C)

where:

     R = size of(left input to the join)
     M = HASH_AREA_SIZE * 0.9
     Po2(n) = largest power of 2 that is smaller than n
     C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE

Right now, I don't want to alter the value of next, as this beast is barely under control as it is. So I thought I might change the value of HASH_MULTIBLOCK_IO_COUNT. Problem is, I can't seem to determine a value for R. v$sqlarea and v$sqltext don't seem to be of much help outside of being able to see the actual sql. Ditto with a few others I've tried.

Any takers? Oracle 8.1.7/HPUX 11(64 bit)

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: DBarbour_at_austin.isd.tenet.edu

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).