Hi Steve,
Below is Oracle definition for this parameter, it seems that the value is dynamic and dependant on the query, being derived from other parameter values.
Hope this helps,
Rob
HASH_MULTIBLOCK_IO_COUNT
Parameter type: Integer
Parameter class: Dynamic. Scope= ALTER SESSION, ALTER SYSTEM.
Default value: Query dependent. Appears as 0 in V$PARAMETER.
Range of values: Operating system dependent
HASH_MULTIBLOCK_IO_COUNT specifies how many sequential blocks a hash join reads and writes in one I/O. The maximum value is operating system dependent. It is always less than the maximum I/O size of the operating system expressed as Oracle blocks (MAX_IO_SIZE / DB_BLOCK_SIZE).
You need not set or change the value of this parameter, because Oracle computes the value individually for every query. If you let Oracle do the automatic computation, the value of the parameter appears as 0 in the V$PARAMETER dynamic performance view.
Oracle Corporation does not recommend that you set or change the value of this parameter. If you must set it to investigate its effect on performance, make sure that the following formula remains true:
R / M <= Po2(M/C)
where:
R = number of bytes in the smaller relation to be joined. The number of bytes is the product of the size of each column in the smaller relation times the number of rows in that relation.
M = HASH_AREA_SIZE * 0.9
C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE
Po2(n) = a function that returns the largest power of 2 that is smaller than its argument.
Note: If you are using Oracle's multi-threaded server architecture, Oracle ignores any value you set for this parameter, and instead uses a value of 1.
>>> Steve Dunnings <[Email Address Removed] 05/31/01 03:57PM >>>
Hello All
I have an 8.1.6.2.0 database experiencing an ORA-3232 error.
Text: unable to allocate an extent of %s blocks from tablespace %s
---------------------------------------------------------------------------
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.
The tablespace is TEMP with a next extent of 128K. The HASH_MULTIBLOCK_IO_COUNT=0, HASH_JOIN_ENABLED = true and the HASH_AREA_SIZE=10485760.
I do not particularly want to alter the TEMP storage.
Can anyone please advise on what value I should set HASH_MULTIBLOCK_IO_COUNT to or any other relevant advice to avoid this error?
Thanks in advance.
Regards
Steve
__________________________________________________________________
Get your own FREE, personal Netscape Webmail account today at http://webmail.netscape.com/
--------
Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed]
To subscribe: send a blank email to oracledba-[Email Address Removed]
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page