Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> PGA_AGGREGATE_TARGET is another way to instability.

PGA_AGGREGATE_TARGET is another way to instability.

From: <J.Velikanovs_at_alise.lv>
Date: Thu, 7 Oct 2004 02:44:12 +0300
Message-ID: <OFF8C882E2.0DDA339F-ONC2256F25.00820570-C2256F25.00828667@alise.lv>


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
Received on Wed Oct 06 2004 - 18:41:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US