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