| |
 |
|
Oracle _smm_px_max_size Parameter
Oracle Tips by Burleson Consulting
|
The hidden parameter _smm_px_max_size is used for Oracle parallel
query, and defaults to 30% of the pga_aggregate_target
setting, divided by degree of parallelism (as set by a PARALLEL
hint, the “alter table xxx parallel” command, or the
parallel_automatic_tuning initialization parameter).
For example, by default a DEGREE=4 parallel query would have a
maximum value of 15 megabytes. Remember, parallel full-table scans
bypass the data buffers and store the incoming data rows in the PGA
region and not inside the data buffers (as defined by the
db_cache_size parameter).
Oracle
professional might want to allow individual tasks to exceed the
default limits imposed by Oracle. For example, PC-based Oracle
servers (1 or 2 CPU’s with 8 gigabytes of RAM) will often have
unused RAM available. For example, a fully-cached 5 gigabyte
database on an 8 gigabyte dedicated Oracle server will have
approximately 1 gigabyte available for the PGA (allowing 20% for the
OS and other SGA regions).
By re-setting
pga_aggregate_target = 1000m, _pga_max_size = 1000m and
_smm_px_max_size = 333m,
parallel queries may now have up to 330 megabytes of RAM (30% of
pga_aggegate_target), such that a DEGREE=4 parallel query would
have 83 megabytes (333 meg/4).
|
