Memory Paging cause Oracle database performance issue

 

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.

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle