How to tune SGA in Oracle Database .
Photo by Mille Sanders on Unsplash
If you have reached so far as to google this topic, you must be aware of sga and its usage in a oracle database. So lets just brush up your memory on some key notes before proceeding on the topic. I am assuming here you must be on a oracle version 12c and above while you are reading this blog. You must have set the sga_target parameter in your database to let automatic memory management of the various components of SGA. There is also a parameter memory_target which if you set will automatically manage your pga and sga memory allocations for you based on the database needs. This parameter is effective if you are managing a small database with limited usage . However if you are managing a high stake environment you might need more granular control by setting sga_target and pga_target parameters.
So firstly lets get a picture of the sga and its various components utilizing memory
SYS@MYORACLEDB>show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 18G
sga_min_size big integer 0
sga_target big integer 18G
SYS@MYORACLEDB>SELECT component, current_size/1024/1024/1024 as size_gb, min_size/1024/1024/1024 as min_size_gb
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY component;
COMPONENT SIZE_GB MIN_SIZE_GB
------------------------------ -------------------- --------------------
DEFAULT buffer cache 15.985 15.985
Shared IO Pool .005 0
large pool .025 .025
shared pool 1.98 1.98
The above sql’s suggest that i have set 18Gb as my sga and my major memory component is being utilized by database buffer cache . No wonder in high stake environments oracle databases tend to be read/write intensive.
If you feel your database is slow and your awr also shows you have frequent buffer cache misses, it means you might need some memory for your Oracle db instance . Its best to look into the magic table of v$sga_target_advice . Lets run it on the database
SYS@MYORACLEDB>SELECT sga_size, sga_size_factor, estd_db_time_factor
FROM v$sga_target_advice
ORDER BY sga_size ASC; 2 3
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME_FACTOR
-------------------- -------------------- --------------------
2 .125 7.5287
4 .25 4.3331
9 .5 1.3433
13 .75 1.1268
18 1 1
23 1.25 .9336
27 1.5 .9178
32 1.75 .9163
36 2 .9158
In the above example on MYORACLEDB we hardly see a improvement in 10% of db time factor even if we double our sga size from 18GB to 36GB (increasing the sga size factor from 1->2 ) . It would be unfair to not give you a example from a problematic db facing a lot of buffer cache misses. Here in this example on the left , just by going from sga size factor of 1 -> 2 (meaning doubling the sga size from 584MB to 1168MB) you are getting more than 50% of improvement in db time which is a huge performance gain. Your next step should be to change the sga size based on the recommendation and if your host has free memory to be allocated to the database.
References:1
