This post describes some deep knowledge of SGA.
LOCK_SGA is used to make sure that the contents from the SGA are not flushed, i.e data from the DB buffer cache not written back to disc. It is like to pin the contents of SGA.
SGA_LOCK locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.
The LOCK_SGA parameter is used to make the Oracle SGA region ineligible for swapping, effectively pinning the SGA RAM in memory. This technique is also known as “page fencing“, using lock_sga=TRUE to guarantee that SGA RAM never sent to the swap disk group -out the operation.
The LOCK_SGA was introduced in Oracle 8 to locks the entire SGA into physical memory. This parameter is only useful if the OS is swapping the SGA in the virtual memory to disk. In this case, this parameter improves performance. Unless you are having paging problems. LOCK_SGA is not supported on some platforms like Windows NT, AIX etc.
When to use lock_sga
In practice, the lock_sga parameter is only needed when you have an over-stressed server with RAM paging, and it’s always a better idea to add more RAM than to lock-in the SGA by making it non-swappable. The lock_sga parameter is also useful in cases with multiple instances on an over-stressed server, where the lock_sga=true instance is to have priority over memory usage, at the expense of the other SGA’s.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez
Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
