PGA_AGGREGATE_TARGET is another way to instability.

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