Basics of the System Global Area in Oracle 12c - dummies

Basics of the System Global Area in Oracle 12c

By Chris Ruel, Michael Wessler

The System Global Area (SGA) is a group of shared memory structures within Oracle 12c. It contains things like data and SQL. It is shared between Oracle background processes and server processes.

The SGA is made up of several parts called the SGA components:

  • Shared pool

  • Database buffer cache

  • Redo log buffer

  • Large pool

  • Java pool

  • Streams pool

The memory areas are changed with initialization parameters.

  • You can modify each parameter individually for optimum tuning (only for the experts).

  • You can tell Oracle how much memory you want the SGA to use (for everyone else).

Say you want Oracle to use 1GB of memory. The database actually takes that 1GB, analyzes how everything is running, and tunes each component for optimal sizing. It even tells you when it craves more.

Redo log buffer in Oracle 12c

The redo log buffer is another memory component that protects you from yourself, bad luck, and Mother Nature. This buffer records every SQL statement that changes data. The statement itself and any information required to reconstruct it is called a redo entry. Redo entries hang out here temporarily before being recorded on disk. This buffer protects against the loss of dirty blocks.

Dirty blocks aren’t written to disk constantly.

Imagine that you have a buffer cache of 1,000 blocks, and 100 of them are dirty. Then imagine a power supply goes belly up in your server, and the whole system comes crashing down without any dirty buffers being written. That data is all lost, right? Not so fast. . . .

The redo log buffer is flushed when these things occur:

  • Every time there’s a commit to data in the database

  • Every three seconds

  • When the redo buffer is 1/3 full

  • Just before each dirty block is written to disk

Why does Oracle bother maintaining this whole redo buffer thingy when instead, it could just write the dirty buffers to disk for every commit? It seems redundant.

  • The file that records this information is sequential. Oracle always writes to the end of the file. It doesn’t have to look up where to put the data. It just records the redo entry. A block exists somewhere in a file. Oracle has to find out where, go to that spot, and record it. Redo buffer writes are very quick in terms of I/O.

  • One small SQL statement could modify thousands or more database blocks. It’s much quicker to record that statement than wait for the I/O of thousands of blocks. The redo entry takes a split second to write, which reduces the window of opportunity for failure.

  • It also returns your commit only if the write is successful. You know right away that your changes are safe. In the event of failure, the redo entry might have to be re-executed during recovery, but at least it isn’t lost.

Large pool in Oracle 12c

We’re not referring to the size of your neighbor’s swimming pool. Not everyone uses the optional large pool component. The large pool relieves the shared pool of sometimes-transient memory requirements.

These features use the large pool:

  • Oracle Recovery Manager

  • Oracle Shared Server

  • Parallel processing

  • I/O-related server processes

Because many of these activities aren’t constant and allocate memory only when they’re running, it’s more efficient to let them execute in their own space.

Without a large pool configured, these processes steal memory from the shared pool’s SQL area. That can result in poor SQL processing and constant resizing of the SQL area of the shared pool. Note: The large pool has no LRU. Once it fills up (if you size it too small) the processes revert to their old behavior of stealing memory from the shared pool.

Java pool in Oracle 12c

The Java pool isn’t a swimming pool filled with coffee (Okay, we’re cutting off the pool references.) The Java pool is an optional memory component.

Starting in Oracle 8i, the database ships with its own Java Virtual Machine (JVM), which can execute Java code out of the SGA. In our experience, this configuration is relatively rare. In fact, you’ll see this where Oracle-specific tools are installed.

However, don’t let that discourage you from developing your own Java-based Oracle applications. The fact is, even though Oracle has its own Java container, many other worthwhile competing alternatives are out there.

Streams Pool in Oracle 12c

The streams pool is used only if you’re using Oracle Streams functionality. Oracle Streams is an optional data replication technology where you replicate (reproduce) the same transactions, data changes, or events from one database to another (sometimes remote) database. You would do this if you wanted the same data to exist in two different databases.

The streams pool stores buffered queue messages and provides the memory used to capture and apply processes. By default, the value of this pool is zero and increases dynamically if Oracle Streams is in use.