Hidden parameters for Oracle PGA regions Oracle Tips by Burleson Consulting

Hidden parameters for Oracle PGA regions

With proper understanding (and knowing that these undocumented parameters are not supported by Oracle), you can adjust your PGA regions to allow for system-specific sorting and hash joins.

·       _pga_max_size – this hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.

·       _smm_px_max_size – This parameter 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, "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 sort area value of 15 megabytes per session with a 200 megabyte pga_aggregate_target setting. 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).

We also see these additional undocumented parameters:

·       _smm_advice_enabled: If TRUE, enable v$pga_advice

·       _smm_advice_log_size:  This overwrites default size of the PGA advice workarea history log

·       _smm_auto_cost_enabled:  If TRUE, use the AUTO size policy cost functions

·       _smm_auto_max_io_size: The maximum IO size (in KB) used by sort/hash-join in auto mode

·       _smm_auto_min_io_size: The minimum IO size (in KB) used by sort/hash-join in auto mode

·       _smm_bound:  This overwrites memory manager automatically computed bound

·       _smm_control:  This provides controls on the memory manager

·       _smm_max_size: This is the maximum work area size in auto mode (serial)

·       _smm_min_size:  The minimum work area size in auto mode

·       _smm_px_max_size: The maximum work area size in auto mode (global)

·       _smm_trace: The on/off tracing for SQL memory manager

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.