How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary to increase the pga or the sga? Thank you

  • You ask how to determine if it is necessary to increase SGA/PGA. If your users are not complaining about performance, then it is not necessary. By definition. That said, read the comments of others to determine if it might be benificial, as opposed to "necessary". Beware of contracting a case of Compulsive Tuning Disorder.
    – EdStevens
    May 23, 2019 at 22:11

4 Answers 4


Tuning usually starts with a reason. Oracle has made great strides in reducing the need to tune for many use cases. Most tuning I have done has been necessitated by poorly written application code or trying to mix an OLAP application and reporting on the same instance. Without knowing your operating system and specifications, how the database is used, size of the OS paging file, number of users only general advice can be given. The documentation is excellent and worth your time.

If you don't have a specific issue then set the aggregate PGA target parameter to 0 and let Oracle do the work. The existing settings can be seen here


Official documentation is here.

The SGA can also be automatically managed by Oracle with these settings:

  • SGA_TARGET to a nonzero value
  • SGA_MAX_SIZE to a value larger than SGA_TARGET but small enough that there is enough memory free on the server for the OS and other running processes.

You can query the existing settings from the SQL command line with


A very rough rule of thumb is to allocate at least four to six GB for the OS on a dedicated server.

Official documentation is here


You may take a look to the following views:


Both will tell you how much improvement can you "expect" when increasing / decreasing the memory of both.


The exact queries for SGA and PGA tuning you might be looking for are:

SELECT sga_size, sga_size_factor, estd_db_time_factor 
FROM v$sga_target_advice ORDER BY sga_size ASC;

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, 
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, 
FROM v$pga_target_advice;

You should seek for a SGA value where the estd_db_time_factor is optimally low in the first query; and a PGA value where it does not dramatically affect the other columns in the latter query.


This query will show you what the database would like for the SGA. A similar query is possible for the PGA.

    SELECT TO_CHAR( al.execution_start, 'dd Mon yyyy HH24:MI:SS') AS finding_time
       aa.num_attr1/1048576 AS current_target_mb,
       aa.num_attr2/1048576 AS advised_target_mb
  FROM dba_advisor_actions  aa,
       dba_advisor_findings af,
       dba_advisor_log      al
 WHERE 1=1
   AND al.owner          = af.owner
   AND al.task_name      = af.task_name
   AND aa.owner          = af.owner
   AND aa.task_name      = af.task_name
   AND aa.execution_name = af.execution_name
   AND af.finding_name   = 'Undersized SGA'
   AND aa.attr1          = 'sga_target'
    BY al.execution_start;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.