2

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

1
  • 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

3 Answers 3

1

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

SELECT *
  FROM V$PGASTAT;

Official documentation is here.

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

  • STATISTICS_LEVEL to TYPICAL or ALL
  • 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

SHOW SGA

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

1

You may take a look to the following views:

V$SGA_TARGET_ADVICE V$PGA_TARGET_ADVICE

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

1

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, 
       ESTD_OVERALLOC_COUNT 
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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