PGA , SGA , MEMORY Advisors

MEMORY_Advisors
One of the issues that Database DBA face is oracle database memory set correctly or do I need to increase it.
Oracle 11g has cool solution.
Querying from table: v$memory_target_advice , tells if increasing memory size would provide any advantage, if so how much should you increase.
Here is the example:


Review the results in the screen shot. Currently database has a memory_target of 2048 MB (First line shows this).It can be compared with DB initialization parameter MEMORY_TARGET.
Line 2 shows that increasing memory size to 2560 MB (which is 25% more) did not add further benefit which can be seen in the column : EST_DB_TIME_FACTOR value 1. However, in line 3 if Memory size is 3072 (i.e. 50% more ) estimated time factor is 0.9602 that means 1 sec process can be done in 0.9602 sec this is an improvement.
Even if you increase memory by 75%, there is no additional benefit, but there is very minor benefit when memory is  double.
In this scenario increasing memory by 50% is adding benefit for the database.

PGA Advisor

Sql>select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

In the above figure, look at 5^th row
Value=1358954496 is the current orcldb.__pga_aggregate_target  value  (set in initorcldb.ora)

So, Subtract pga_target_for_estimate - estd_extra_bytes_rw for 5^th row , 1358954496-954314752

equals to 404639744 i.e eqals to 386 MB.

So, conclusion is that adding extra 386 MB will improve performance(less I/O would be needed).

SGA Advisor

Sql>select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

DB_TIME is an overall figure for the amount of time spent taken within the database to execute SQL;

Minimizing DB_TIME is the overall objective of all tuning.

Look at the 3^rd row i.e value 1952 MB , this is the value of parameter orcldb.__sga_target being set

in initorcldb.ora.

So,It can be seen that if the SGA is raised from its current value of 1952 MB to 3904 MB then

DB_TIME would reduce.