Re: PGA_AGGREGATE_TARGET is another way to instability.
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: j.velikanovs@xxxxxxxx
- Date: Wed, 6 Oct 2004 20:05:28 -0400
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@xxxxxxxx
<j.velikanovs@xxxxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
