Thursday, June 9, 2011

On SGA and SSDs -1

The System Global Area (SGA) and the set of database processes constitute an Oracle Database instance. Oracle Database automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All database background processes and all server processes that execute on behalf of users can read information contained within the instance's SGA, and several processes write to the SGA during database operation.
Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access. This is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.
Ref: (Oracle official documentation)

That says SGA is not a part of particular h/w or server system.
It’s a component of Oracle database and hence by properly configuring and tuning the same on any h/w or server or server storage combination we could get max out of it.

Again quoting from Oracle:  ( Source: Oracle® Database
Performance Tuning Guide
10g Release 2 (10.2), Part No#B14211-01)

Fit the SGA into main memory
Because the purpose of the SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.

The LOCK_SGA parameter can be used to lock the SGA into physical memory and prevent it from being paged out.
For more Please refer to Oracle metalink doc=
Init.ora Parameter "LOCK_SGA" Reference Note [ID 38998.1]

Now, to address the costly logical I/O of Oracle it’s suggested to use SSD (Ref: “Oracle Performance Tuning with SSD’ by Michael Ault, RAMPANT Press) and that SSD could be from any vendor.
What counts is how fast/efficient the SSD is.

For using that efficiently on ibm AIX you could refer to Oracle documentation: How to enable Large Page Feature on AIX-Based Systems [ID 372157.1]

No comments:

Post a Comment