Databases Reference
In-Depth Information
Tuning memory to avoid Operating System
paging
Tuning memory is a task common to both the database administrators and the system
administrators. However, the DBA's task is to optimize the use of the memory available to the
database instance, while the system administrator will focus on the overall memory available
to the system and on how to divide it among the required applications and users.
It's common best practice to have a dedicated system to manage the database, not sharing
it with other applications. The system administrator and DBA tasks are very similar, so this is
often the same person.
In this recipe, we will see how to configure the total memory size of our database instance to
avoid problems related to the use of virtual memory and pagination.
How to do it...
The following steps will demonstrate how to tune memory to avoid Operating System paging:
1.
Connect to the database as
SYSDBA
using SQL*Plus:
CONNECT / AS SYSDBA
2.
Show the allocated memory to the System Global Area (SGA):
SHOW SGA
3.
Query the
V$SGA
view to obtain the same information as mentioned earlier:
SELECT * FROM V$SGA;
4.
Query the
V$SGAINFO
dynamic performance view to show more details about
memory usage:
SELECT * FROM V$SGAINFO;
5.
Connect to Oracle Enterprise Manager as
SYSDBA
and go to Advisor Central.
6.
Choose Memory Advisors to verify if Automatic Memory Management (AMM)
is enabled, the total (and maximum) memory size configured, and the allocation
history graph.
7.
Click on the Advice button to see the Memory Size Advice graph, which helps us
choose the right value for total memory size.
