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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.