Re: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: Christopher.Taylor2@xxxxxxxxxxxx
- Date: Sun, 14 Oct 2012 15:45:00 +0200
Hi Christopher,
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: PGA / WORKAREA_SIZE_POLICY / SYSTEM_STATS & Optimzer Influence - Randolf Geist
