Oracle 9i Database Administration in 10 Minutes

- Asim Abbasi (Author)

Chapter 2: Understanding the Oracle Instance

Page #: 1 | 2 | 3

Understanding Oracle Instance’s Memory Architecture

By saying, memory architecture of Oracle Instance we mean how RAM is utilized by Oracle Instance. The RAM or simply memory, occupied by Oracle is divided into two major categories. One is called Shared Memory and the other one is called Non Shared Memory.

Figure 5: Oracle Instance memory architecture.
________________________________________

Oracle allocates SGA (System Global Area) whenever Instance starts and deallocates it when the Instance ends. SGA has three mandatory areas and two optional areas as shown in the Figure 5. Optional area means you can have it if the requirement arises. In mandatory areas, we have Shared Pool, Database Buffer Cache and Redo Log Buffer Cache whereas in optional areas we have Large Pool and Java Pool. Shared Pool is utilized for SQL requests coming from the connected users, PL/SQL objects execution, data dictionary views, locks and so on. There is a special area inside Shared Pool that deals with SQL and PL/SQL, is called the Library Cache. If one user has executed the SQL statement that statement will get parsed and complied utilizing this area.

The “Data Dictionary Cache” holds the most recently used database dictionary information. Data Dictionary is basically the information about the data inside the database or in other words metadata in the form of tables and view about the database.

Database Buffer Cache holds the DB Blocks recently read from the data files. Database Buffer Cache has further three sub-caches. One is called KEEP, second one is RECYCLE and the third one is DEFAULT. In KEEP, DB blocks will be retained in the RAM and will not get aged out. Oracle Instance will remove stuff placed in RECYCLE from the RAM as soon as they are not needed. DB Blocks without any assigned attribute (KEEP or RECYCLE) are placed in the DEFAULT buffer pool.

Within whole Database Buffer Cache, there lie three different types of buffers or in other words three different states of buffers. These three states are, Dirty Buffers, Free Buffers and Pinned Buffers. Buffers that need to be written to the data files are marked dirty and hence got the name Dirty Buffers. Once written to the data files they are available to be over-written and those which never had any data, are called Free Buffers. Buffers that are currently being accessed or those that are explicitly marked for future use, are called Pinned Buffers e.g. KEEP buffer pool.

Redo Log Buffer Cache holds the information about the changes that are being made to the database. Large Pool is optionally required for specific database operation like backup or restore to avoid contention with rest of the good stuff of SGA.

Java Pool is another optional area and can be requested to provide memory for java objects within Oracle database. The Non-Shared part of memory is called PGA (Program Global Area) and contains the data and control information for a server process. Stack space in the PGA is utilized by server process to hold variables, arrays and other useful information pertaining to connected session.

Chapter 2: Understanding the Oracle Instance

Page #: 1 | 2 | 3

products

books

FREE VB, ASP Projects

Oracle 9i Database Administration in 10 Minutes