PGA , SGA , MEMORY Advisors
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 5th 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 5th 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 3rd
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.
MEMORY TARGET Advisor
Sql>select memory_size,memory_size_factor,estd_db_time
from v$memory_target_advice;
This query is against the
memory target advisor, which gives advice on the total (SGA plus PGA) memory allocation.Here, the value of MEMORY_SIZE in 3rd row is equal to the value of parameter memory_target mentioned in initorcldb.ora.The above figure shows that the optimal value is 3248 MB
which is the current value already set.So, no changes are needed.Refer: http://oracledbasagar.blogspot.com for more information.
