This content has been marked as final. Show 8 replies
SELECT * FROM V_$SGA_TARGET_ADVICE;1 person found this helpful
SELECT * FROM V_$PGA_TARGET_ADVICE;
1 person found this helpful
What is the best criteria to set the size of the SGA and PGA size in oracle 10g?It will depend on your requirement and availability of memory on the server. But you can start with minimum size and can observe the system and increase accordingly.
You can read asktom note..
Hello,1 person found this helpful
What is the best criteria to set the size of the SGA and PGA size in oracle 10g? and the memory_target size in oracle 11g ?First of all you have to satistify the prerequisites given by the Installation Guide corresponding to the Oracle Release.
Is it related to the size of data ? what is the criteria/factors in which it affect setting the size of the SGA, PGA (10g) and memory_target (11g)?
Then, you have to take into account the memory available on the Server. If the SGA is too large the Server may swap and the Performances can become worst.
Afterwards, it depends on the activity of the Database, for a DSS Database you may need a large SGA. If you have a lot of sessions, the Database may use a lot of PGA.
In *10g* you have the ASMM (Automatic Shared Memory Management) and the Memory Advisor (see the Views V$SGA_TARGET_ADVICE / V$PGA_AGGREGATE_TARGET) may help you to set the Parameter SGA_TARGET and PGA_AGGREGATE_TARGET.
In *11g* you have the AMM (Automatic Memory Management). The Memory Advisor with the View V$MEMORY_TARGET_ADVICE may help to set the Parameter MEMORY_TARGET.
Of course, in *11g* you can also disable the AMM and use the ASMM instead. But its recommended to use the AMM and let Oracle tunes the memory components.
Please, find below links about the Memory Management and Memory Advisor:
Hope this help.
One more clarification
How can identify the best value for memory_target from V$MEMORY_TARGET_ADVICE?
is it the last value in the MEMORY_SIZE column ? which has the minimum ESTD_DB_TIME?
How can identify the best value for memory_target from V$MEMORY_TARGET_ADVICE?We can take the example below:
You can see that beyond 786 Mo the ESTD_DB_TIME_FACTOR cannot be improved (it stays here stuck at 0,9997). So, I may set (in this example) the MEMORY_TARGET to 786 Mo, I won't have benefit to increase it more.
SQL> select * from v$memory_target_advice; MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION ----------- ------------------ ------------ ------------------- ---------- 393 ,75 17909 1,0004 0 524 1 17902 1 0 655 1,25 17900 ,9999 0 786 1,5 17897 ,9997 0 917 1,75 17897 ,9997 0 1048 2 17897 ,9997 0
Hope this help.
Auto change in 10g.