Re: PGA_AGGREGATE_TARGET is another way to instability.

Jurijs,

I think that if your miss of sql in the shared pool is 10% that most statements that are not frequently executed will quickly be aged out and re-parsed during subsequent execution.

If then follows that this is only a concern if your application SQL code is nearly perfectly written and statements are not invalidated/aged out.

Right.

How is this any different, if the bind variables used at parse (with cursor_sharing=SIMILAR) differ greatly from one execution to another, in terms of distribution?

Paul

On Thu, 7 Oct 2004 02:44:12 +0300, j.velikanovs_at_alise.lv <j.velikanovs_at_alise.lv> wrote:
> Hi!
> I would like to share my thought with the list.
> I know in many systems PGA_AGGREGATE_TARGET feature can be appropriate and
> useful,
> as well as tuned with wonderful advisories ;)
>
> But if we imagine situation:
>
> 0.
> PGA_AGGREGATE_TARGET=2G
> After night DB restart (for maintenance purposes for example) the system's
> users starting to use application.
>
> 1.
> Early in the morning there first user logged and run SQL1.
> Oracle Optimizer sees that it can give for SQL1 execution 100MB (1/20) of
> PGA to this particular connection. So CBO have taken the best execution
> plan and used HASH JOIN which fully executed in memory.
>
> 2.
> Lets take a look on the system at the middle of the day.
> There are 2000 users and Oracle can give session for SQL1 execution just
> 1M.
> Then session heavily use TEMP TBS for SQL1 execution and in this case the
> best way to execute SQL1 probably will be NL, but execution plan already
> in shared_pool and session use it.
>
> Conclusion: Depending on available PGA CBO need to invalidate execution
> plan for SQL1 for getting better execution plan.
>
> Whilst the PGA_AGGREGATE_TARGET is another way to unstabilize Oracle
> database.
>
> What do you think?
> Jurijs
>
> --
> http://www.freelists.org/webpage/oracle-l
>

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