Discussion :

[ffs]

Bonjour,

Je cherche des informations sur l'utilisation du paramètre caché _hash_multiblock_io_count sous Oracle 9.2.0.6 sur AIX 5.2.

Pouvez-vous de détailler l'utilisation de ce paramètre .

Par avance merci

[orafrance]

Parameter: HASH_MULTIBLOCK_IO_COUNT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Parameter type: integer
Parameter class: dynamic, scope= ALTER SESSION, ALTER SYSTEM
Default value: For Oracle 7.3 and 8.0 = 1
For Oracle8i it is query dependent. Appears as 0 in V$PARAMETER.
Range of values: operating system dependent
typically 1 - (65,536/<ParameterB_BLOCK_SIZE>)

Related: <ParameterB_BLOCK_SIZE>
<Parameter:HASH_JOIN_ENABLED>
<Parameter:HASH_AREA_SIZE>

*** Note: As of Oracle9i this parameter is hidden
ie: It is renamed to _HASH_MULTIBLOCK_IO_COUNT
This hidden version of the parameter still behaves as
described below.

Description:
~~~~~~~~~~~~
This parameter 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 (that is, the default value of 1 is used even if you set
the parameter to another value).

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

For Oracle 8i 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.

This parameter strongly affects performance because it controls the
number of partitions into which the input is divided.
For Oracle 8i, 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 = sizeof(<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

You can change the value of this parameter without shutting down your
Oracle instance by using the ALTER SESSION or ALTER SYSTEM commands.

Ceci étant, je te conseille de manipuler les paramètres "cachés" avec parcimonie

[ffs]

J'effectivement trouvé cette info, mais je me demandais la réelle implication au niveau de I/O de ce paramètre

Merci pour ta réponse.

[orafrance]

je n'ai jamais touché ce paramètre et les retours d'expérience sont rares