Re: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence

to address your below question: For the PGA part this is true - if you use automatic PGA memory management changes to the available memory per workarea will influence the cost calculation of the Cost Based Optimizer. So it is possible that operations that require a workarea, like a HASH JOIN for example, will be costed differently depending on the estimated PGA per workarea available - and this information is constantly checked and re-calculated by background processes (and possibly even forced to be adjusted during operation / execution of a foreground process if a massive "drift" is detected). If the calculation changes in such a way that a HASH JOIN for example turns from a optimal to a non-optimal (one or multi-pass) operation then the cost might change significantly potentially leading to other plan alternatives being favoured.

Note that a change to that information doesn't invalidate existing cursors, although being part of the optimizer environment settings.

The current amount of I/O or multiple statements accessing the same tables as far as I know doesn't influence the cost calculation (at least currently available versions).

Hope this helps,
Randolf

> This is more of a curiosity question really.
> If the database is under high load (many sessions, lots of IO, multiple statements accessing the same tables) would it be reasonable to think that the optimizer might choose a different execution plan based on how the workload area is being used versus when the database is under light load?

--
http://www.freelists.org/webpage/oracle-l