Oracle - SPLessons

Chapter 4

Oracle 12C Memory Architecture

Oracle 12C Memory Architecture

Oracle 12C Memory Architecture utilizes memory structures, that progress the data and automatically executed in the oracle database. The main memory contain memory structures that exits in the database system inside the computer.Processes are the tasks that work in the main memory of these computers. Oracle database allocates memory area and begins the background processes when an instance is started. The data stored in the memory area are as follows.

  • Data about each associated session, even if it is not currently active.
  • Program code.
  • Cached data, such as data blocks and redo records, that also exists on disk.
  • Data required amid project execution, for instance, the current state of a query from which rows are being fetched.
  • Data such as lock data that is shared and communicated among processes.

To perform several jobs in Oracle 12C Memory Architecture, the database will create and use memory structures. For instance, The data that is shared among the clients will be executed in the store programs inside the memory.

Global Area Components

Global area components is a memory particular to a working process or string that is not shared by different procedures or strings on the framework. A parsed SQL statement and other session particularly the data will hold the private sql area for the processing. At the point when a server process executes pl/SQL or SQL code, the procedures utilizes the query execution state information, private SQL area to store bind variable values, and query execution work regions and in Oracle 12C Memory Architectures, the Global area components consisting of

The program global area(PGA)

In Oracle 12C Memory Architecture, Program global area is a memory particular to a working process or string that is not shared by different procedures or strings on the framework. Since the Program global area is procedure particular, it doesn’t apportioned in the System global area.
The session-dependent variables are required to share the server process that contain in the memory heap of program global area. Memory structures that it requires in the PGA is allotted by the server process. The agent clears a segment of the ledge, utilizes the work space to store the insights about the client demand and to sort the envelopes asked for by the client, and after that surrenders the space when the work is finished.

For the alternate reasons the program global area is sub divided into different area. The dedicated server session are normally utilized for different possible contents. In each and every case the program global area will not be exists.

Private SQL Area

A parsed SQL statement and other session particularly the data will hold the private sql area for the processing. At the point when a server process executes pl/SQL or SQL code, the procedures utilizes the query execution state information, private SQL area to store bind variable values, and query execution work regions. For each executions in private sql area are not shared and may contain distant data and values. A specific private SQL area can be handle by a cursor. cursor can be understand as a state on the server side and as a pointer on the client side. Since private SQL areas are closely associated by the cursors, and some times these changes are not convertible.
The private sql areas are divided into the following types

Persistent area

Bind variable values are contained in this area. At the run time when the statement is executed, The SQL statements will receives a blind variable values. When the cursor is closed then only the persistent area is freed.

The run-time area

The query execution state information will be contained in this area. For instance, the number of rows retrieved during the run time area track in a full table scan will be checked. As the first step in an execute request oracle database will create the run-time area. When the SQL statement is closed the run time area will be freed in the DML statement.

SQL Work Areas

The memory-intensive operations are the work areas of private allocation in the private global areas. For instance, the sort area to sort a set of rows used by a sort operator. In the same way, A hash area to build a hash table to its left input by a hash join operator, whereas a bitmap combinely utilizes the bitmap merge area to merge data retrieved from scans of different bitmap files.

System global area

A group of shared memory structures that contain control information and data for one Oracle database instance is called as system global area.If for the same instance, multiple users are connected simultaneously, then the data in the instance of system global area is distributed between the clients and at the same time the system global area is called the shared global area.

An System global area and Oracle processes constitute an Oracle instance. Oracle 12C Memory Architecture automatically allocates memory for an System global area while starting of an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The System global area will write/read. All the read information contained within the instances of system global area, will be connected to multi-process database instance and several processes write to the SGA during execution of Oracle. The data structures contained in the system global area are:

User Global Area (UGA)

The User global area is session memory, where session variables are allocated to memory, like logon information and other information required by a database session. And finally the user global area stores session state.

The life of the session, the user global area must be available to a database session.Due to this reason, When using a shared server connection the user global area  cannot be stored in the program global area because  a single process is specific to the program global area. Therefore, when using shared server connections the user global area is stored in the system global area, enabling any shared server process access to it. When using a dedicated server connection, the user global area stored in system global area.

Managing Memory

Memory administration includes keeping up ideal sizes for the Oracle Database instance memory structures as request on the database change. The memory structures that must be overseen are the framework world wide zone and the instance for program worldwide territory.

Oracle 12C Database supports different memory administration strategies, which are picked by instatement parameter settings. Oracle suggest to enable the technique known as automatic memory management.

Automatic memory management

Starting with Release 11g, Oracle Database can deal with the SGA memory and example PGA memory totally consequently. You assign just the aggregate memory size to be utilized by the occurrence, and Oracle Database progressively trades memory between the SGA and the example PGA as expected to meet handling requests. This capacity is alluded to as programmed memory administration. With this memory administration strategy, the database additionally powerfully tunes the sizes of the individual SGA segments and the sizes of the individual PGAs.

Manual memory management

In the event one’s want to practice more straightforward control over the sizes of individual memory segments, you can debilitate programmed memory administration and arrange the database for manual memory administration. There are a couple of various techniques accessible for manual memory administration. Some of these strategies hold some level of corroboration.

Summary

  • Memory architecture – Includes memory components,physical components and logical structures.
  • Program global area – Process SQL statements and hold logon and other session information.
  • System global area – Is a part of system memory shared by all processes.
  • Used global area – That hold session information in the memory.