Memory Paging cause Oracle database performance issue
Error
On checking the operating system, heavy paging is notice which degrade the performance of the Oracle Database.
On unix base system
vmstat and top command output shows the heavy paging
On windows:
Performance manager notice heavy paging.
Caused
SGA and PGA involved in Paging, they are in-memory and out-memory.
Solution
To stop the SGA and PGA from paging on a system , any system resource large need memory caused them to page in and out.
If heavy paging is experienced by the Oracle processes when the database is up and running, then O/S considers computational memory as a candidate for paging.
Computational memory stores the working data of running process and released memory when process end.
For Prevent SGA memory from paging, Set the following parameter in init.ora file (SPFILE/PFILE)
LOCK_SGA=TRUE
Restart the Oracle database.
Note:
LOCK_SGA locks the entire SGA into physical memory.
If OS not support the parameter then its ignored automatically.
