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