Database Reference
In-Depth Information
Each table queue is composed of three (up to five with RAC) buffers for each pair of processes that communicate
through it. The size of each buffer (in bytes) is set through the
parallel_execution_message_size
initialization
parameter. The default size depends on the database engine version. Through version 11.1, it's either 2,152 bytes or,
if the
parallel_automatic_tuning
initialization parameter is set to
TRUE
, 4,096 bytes. From version 11.2 onward, the
default size is 16KB. For the best performance, you should set it to the highest supported value. Depending on the
platform you're using, this could be either 16KB, 32KB, or 64KB. Therefore, especially prior to version 11.2, I advise
you to change the default value.
When increasing the
parallel_execution_message_size
initialization parameter, you should make sure that
the necessary memory is available. You can use Formula 15-1 to estimate the maximum amount of the large pool
that should be available for a non-RAC database instance. For that purpose, the formula computes how many buffers
are necessary for an execution that requires two sets of slave processes and uses the maximum possible degree of
parallelism (half the value of the
parallel_max_servers
initialization parameter). Note that in a RAC environment,
not only can the number of buffers used for each pair of processes that communicate be higher (up to five instead of
three), but the maximum degree of parallelism also depends on the number of instances.
Formula 15-1.
The amount of the large pool used by non-RAC database instances for table queues
æ
ç
2
ö
÷
×
parallel_max_server
s
large_pool_size
>×
3
parallel_max_servers
+
parallel_execution_message_size
4
To display how much of the large pool is currently in use by a database instance, you can run the following query:
SQL> SELECT *
2 FROM v$sgastat
3 WHERE name = 'PX msg pool';
POOL NAME BYTES
---------- ----------- ---------
large pool PX msg pool 823296000
You can run the following query to display the number of queue table buffers that are currently allocated (the
Buffers Current
statistic) and also the maximum number that have been allocated at one time since database
instance startup (the
Buffers HWM
statistic):
SQL> SELECT *
2 FROM v$px_process_sysstat
3 WHERE statistic IN ('Buffers Current ',
4 'Buffers HWM ');
STATISTIC VALUE
----------------------------- -----
Buffers Current 45076
Buffers HWM 49924
Because the memory configuration is database instance specific, it's not possible, in a 12.1 multitenant
environment, to set the
parallel_automatic_tuning
and
parallel_execution_message_size
initialization
parameters at the PDB level.
