What is Shared Pool in Oracle?

Shared memory area Shared Pool

The Shared Pool is made up of two performance-related memory structures:

    Row cache, discussed below.
    Cache "library" examined in the second advanced tutorial on oracle architecture.

Row cache.

When a user submits an SQL query, the server process extracts, during the analysis of the query, information stored in the dictionary tables (user account data, names of data files, names of table segments and indexes , extents locations, table descriptions and user privileges…).

This information is cached in the dictionary for reuse. During subsequent parse scans, the server process searches the dictionary cache for information to resolve object names and validate access.


Caching data dictionary information reduces response time to LMD statements (SELECT, INSERT, UPDATE, DELETE). A sufficient size of this cache contributes significantly to improving performance.

If the dictionary cache is of limited size, recursive calls slower than the queries made directly in the cache, will be operated by the server process on the database dictionary (disk access).

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is SGA in Oracle?

The SGA (System Global Area) in Oracle Instance

System Global Area (SGA) memory is memory shared by all server and background processes.


The SGA is made up of three mandatory components and three optional elements.

Mandatory components:

     Shared memory area
     Database buffer cache
     Redo log buffer

Optional items:

     LARGE POOL memory area
     Java memory area (Java Pool)
     Streams memory area (streams pool)

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is Oracle instance

What is Oracle instance?

An Oracle server is made up of an Oracle instance and an Oracle database.

An Oracle database can only be accessed through one instance. See our tutorial on managing an oracle instance to learn how to manipulate the associated database.

An Oracle server instance is made up of a SGA (Global Area System) shared memory structure and several background Oracle processes each with a specific role.


An instance can only open one database at a time.

A database can be opened by multiple instances using the RAC (Oracle Real Application Clusters) option for high availability and load balancing. (see diagram below)


In such a configuration, users waiting to be connected can be directed to the least loaded instance and in addition, if one instance fails, the other takes over for better database availability.

N.B: In Windows environment, each instance is associated with a service named OracleService “InstanceName”. This service must be started in order to start the instance.

When it starts up, the instance uses an initialization settings file.

An instance has a SID. This determines the parameter file used at STARTUP (spfile ”SID” .ora or spfile.ora or init ”SID” .ora in this order of priority, if Oracle does not find one it goes to the next).

The components of the instance (SGA + background processes) are described in the following sections.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.