Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

From: Mladen Gogala <>
Date: Sat, 30 May 2009 17:17:44 +0000 (UTC)
Message-ID: <gvrpnn$567$>

On Fri, 29 May 2009 17:56:08 +0100, Jonathan Lewis wrote:

> The cost calculation for the hash join is based on a number derived from
> the pga_aggregate_target parameter, not from the amount of memory
> currently available; so load doesn't affect the plan.

That is true. If you check V$SYS_OPTIMIZER_ENV or V$SES_OPTIMIZER_ENV, you will find that both tables contain PGA_AGGREGATE_TARGET. Those tables specifically list parameters that influence CBO behavior. The reference manual says the following:

"V$SYS_OPTIMIZER_ENV displays the contents of the optimizer environment for the instance. The optimizer environment stores the value of the main parameters used by the Oracle optimizer when building the execution plan of a SQL statement. Hence, modifying the value of one or more of these parameters (for example, by issuing an ALTER SYSTEM statement) could lead to plan changes."

Received on Sat May 30 2009 - 12:17:44 CDT

Original text of this message