12 Tuning the System Global Area

This chapter describes how to tune the System Global Area (SGA). If you are using automatic memory management to manage the database memory on your system, then there is no need to tune the SGA as described in this chapter.

This chapter contains the following topics:

12.1 Using Automatic Shared Memory Management

Automatic shared memory management simplifies the configuration of the SGA by automatically distributing the memory in the SGA for the following memory pools:

  • Database buffer cache (default pool)

  • Shared pool

  • Large pool

  • Java pool

  • Streams pool

Automatic shared memory management is controlled by the SGA_TARGET parameter. Changes in the value of the SGA_TARGET parameter automatically resize these memory pools. If these memory pools are set to nonzero values, then automatic shared memory management uses these values as minimum levels. Oracle recommends that you set the minimum values based on the minimum amount of memory an application component requires to function properly.

The following memory caches are manually-sized components and are not controlled by automatic shared memory management:

  • Redo log buffer

    The redo log buffer is sized using the LOG_BUFFER initialization parameter, as described in "Configuring the Redo Log Buffer".

  • Other buffer caches (such as KEEP, RECYCLE, and other nondefault block size)

    The KEEP pool is sized using the DB_KEEP_CACHE_SIZE initialization parameter, as described in "Configuring the KEEP Pool".

    The RECYCLE pool is sized using the DB_RECYCLE_CACHE_SIZE initialization parameter, as described in "Configuring the RECYCLE Pool".

  • Fixed SGA and other internal allocations

    Fixed SGA and other internal allocations are sized using the DB_nK_CACHE_SIZE initialization parameter.

The memory allocated to these memory caches is deducted from the value of the SGA_TARGET parameter when automatic shared memory management computes the values of the automatically-tuned memory pools.

The following sections describe how to access and set the value of the SGA_TARGET parameter:

See Also:

12.1.1 User Interfaces for Setting the SGA_TARGET Parameter

This section describes the user interfaces for setting the value of the SGA_TARGET parameter.

This section contains the following topics:

12.1.1.1 Setting the SGA_TARGET Parameter in Oracle Enterprise Manager

You can change the value of the SGA_TARGET parameter in Oracle Enterprise Manager by accessing the SGA Size Advisor from the Memory Parameters SGA page.

12.1.1.2 Setting the SGA_TARGET Parameter in the Command-Line Interface

You can change the value of the SGA_TARGET parameter in the command-line interface by querying the V$SGA_TARGET_ADVICE view and using the ALTER SYSTEM command.

12.1.2 Setting the SGA_TARGET Parameter

This section describes how to enable and disable automatic shared memory management by setting the value of the SGA_TARGET parameter.

This section contains the following topics:

12.1.2.1 Enabling Automatic Shared Memory Management

To enable automatic shared memory management, set the following initialization parameters:

  • STATISTICS_LEVEL to TYPICAL or ALL

  • SGA_TARGET to a nonzero value

    The SGA_TARGET parameter can be set to a value that is less than or equal to the value of the SGA_MAX_SIZE initialization parameter. Set the value of the SGA_TARGET parameter to the amount of memory that you intend to dedicate to the SGA.

12.1.2.2 Disabling Automatic Shared Memory Management

To disable automatic shared memory management, set the value of the SGA_TARGET parameter dynamically to 0 at instance startup.

This disables automatic shared memory management and the current auto-tuned sizes will be used for each memory pool. If necessary, you can manually resize each memory pool, as described in "Sizing the SGA Components Manually".

12.2 Sizing the SGA Components Manually

If the system is not using automatic memory management or automatic shared memory management, then you must manually configure the sizes of the following SGA components:

  • Database buffer cache

    The database buffer cache is sized using the DB_CACHE_SIZE initialization parameter, as described in "Configuring the Database Buffer Cache".

  • Shared pool

    The shared pool is sized using the SHARED_POOL_SIZE initialization parameter, as described in "Configuring the Shared Pool".

  • Large pool

    The large pool is sized using the LARGE_POOL_SIZE initialization parameter, as described in "Configuring the Large Pool".

  • Java pool

    The Java pool is sized using the JAVA_POOL_SIZE initialization parameter.

  • Streams pool

    The Streams pool is sized using the STREAMS_POOL_SIZE initialization parameter.

  • In-memory column store

    The In-memory column store is an optional static SGA pool. It is sized using the INMEMORY_SIZE initialization parameter, as described in "Sizing the In-Memory Column Store".

The values for these parameters are also dynamically configurable using the ALTER SYSTEM statement, except the value for the INMEMORY_SIZE parameter. The INMEMORY_SIZE parameter is a static parameter; so after altering the size of it using the ALTER SYSTEM statement, you must restart the database instance to make the change effective.

Before configuring the sizes of these SGA components, take the following considerations into account:

See Also:

12.2.1 SGA Sizing Unit

Memory for the buffer cache, shared pool, large pool, and Java pool is allocated in units of granules. If the SGA size is less than 1 GB, then the granule size is 4MB. If the SGA size is greater than 1 GB, the granule size changes to 16MB. The granule size is calculated and fixed when the database instance starts up. The size does not change during the lifetime of the instance.

To view the granule size that is currently being used for the SGA, use the V$SGA_DYNAMIC_COMPONENTS view. The same granule size is used for all dynamic components in the SGA.

12.2.2 Maximum Size of the SGA

The maximum amount of memory usable by the database instance is determined at instance startup by the value of the SGA_MAX_SIZE initialization parameter. You can expand the total SGA size to a value equal to the SGA_MAX_SIZE parameter. The value of the SGA_MAX_SIZE parameter defaults to the aggregate setting of all the SGA components.

If the value of the SGA_MAX_SIZE parameter is not set, then decrease the size of one cache and reallocate that memory to another cache if necessary. Alternatively, you can set the value of the SGA_MAX_SIZE parameter to be larger than the sum of all of the SGA components, such as the buffer cache and the shared pool. Doing so enables you to dynamically increase a cache size without having to decrease the size of another cache.

Note:

The value of the SGA_MAX_SIZE parameter cannot be dynamically resized.

12.2.3 Application Considerations

When configuring memory, size the memory caches appropriately based on the application's needs. Conversely, tuning the application's use of the memory caches can greatly reduce resource requirements. Efficient use of the memory caches also reduces the load on related resources, such as latches, CPU, and the I/O system.

For optimal performance, consider the following:

  • Design the cache to use the operating system and database resources in the most efficient manner.

  • Allocate memory to Oracle Database memory structures to best reflect the needs of the application.

  • If changes or additions are made to an existing application, resize Oracle Database memory structures to meet the needs of the modified application.

  • If the application uses Java, investigate whether the default configuration for the Java pool needs to be modified.

See Also:

Oracle Database Java Developer's Guide for information about Java memory usage

12.2.4 Operating System Memory Use

For most operating systems, it is important to consider the following when configuring memory:

See Also:

Your operating system hardware and software documentation, and the Oracle documentation specific to your operating system, for more information on tuning operating system memory usage

12.2.4.1 Reduce Paging

Paging occurs when an operating system transfers memory-resident pages to disk solely to load new pages into memory. Many operating systems page to accommodate large amounts of information that do not fit into real memory. On most operating systems, paging reduces performance.

To determine whether significant paging is occurring on the host system, use operating system utilities to examine the operating system. If significant paging is occurring, then the total system memory may not be large enough to hold the memory caches for which memory is allocated. Consider either increasing the total memory on the system, or decreasing the amount of memory allocated.

12.2.4.2 Fit the SGA into Main Memory

Because the purpose of the SGA is to store data in memory for fast access, the SGA should reside in the 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.

This section contains the following topics:

12.2.4.2.1 Viewing SGA Memory Allocation

To view how much memory is allocated to the SGA and each of its internal structures, use the SHOW SGA statement in SQL*Plus, as shown in Example 12-1.

Example 12-1 Viewing SGA Memory Allocation

SHOW SGA

The output of this statement might look like the following:

Total System Global Area  840205000 bytes
Fixed Size                   279240 bytes
Variable Size             520093696 bytes
Database Buffers          318767104 bytes
Redo Buffers                1064960 bytes
12.2.4.2.2 Locking the SGA into Physical Memory

To prevent the SGA from being paged out, consider locking the SGA into physical memory by enabling the LOCK_SGA parameter. The database does not use the MEMORY_TARGET and MEMORY_MAX_TARGET parameters when the LOCK_SGA parameter is enabled.

12.2.4.3 Allow Adequate Memory to Individual Users

When sizing the SGA, ensure that you allow enough memory for the individual server processes and any other programs running on the system.

12.2.5 Iteration During Configuration

Configuring memory allocation involves distributing available memory to Oracle Database memory structures, depending on the needs of the application. The distribution of memory to Oracle Database structures can affect the amount of physical I/O necessary for Oracle Database to operate properly. Having a proper initial memory configuration provides an indication of whether the I/O system is effectively configured.

After the initial pass through the memory configuration process, it may be necessary to repeat the steps of memory allocation. Subsequent passes enable you to make adjustments to earlier steps, based on changes in subsequent steps. For example, decreasing the size of the buffer cache enables you to increase the size of another memory structure, such as the shared pool.

12.3 Monitoring Shared Memory Management

Table 12-1 lists the views that provide information about SGA resize operations.

Table 12-1 Shared Memory Management Views

View Description

V$SGA_CURRENT_RESIZE_OPS

Displays information about SGA resize operations that are currently in progress.

V$SGA_RESIZE_OPS

Displays information about the last 800 completed SGA resize operations. This does not include operations that are currently in progress.

V$SGA_DYNAMIC_COMPONENTS

Displays information about the dynamic components in the SGA. This view summarizes information of all completed SGA resize operations that occurred after instance startup.

V$SGA_DYNAMIC_FREE_MEMORY

Displays information about the amount of SGA memory available for future dynamic SGA resize operations.


See Also:

Oracle Database Reference for information about these views

12.4 Configuring the In-Memory Column Store

The In-Memory Column Store is an optional area of the SGA that stores copies of tables, partitions, and other database objects in a columnar format that is optimized for rapid scans.

This section contains the following topics:

Note:

The In-Memory Column Store is available starting with Oracle Database 12c Release 1 (12.1.0.2).

12.4.1 About the In-Memory Column Store

The In-Memory Column Store stores copies of tables, partitions, and other database objects in the SGA. The In-Memory Column Store does not replace the database buffer cache. Instead, they complement each other so that both memory areas can store the same data in different formats. Rows stored in the In-Memory Column Store are divided into large memory regions in a columnar format. Within each region, a column resides separately in a contiguous area of memory.

You can enable the In-Memory Column Store for any of the following database objects:

  • Tables

  • Materialized views

  • Partitions

  • Tablespaces

You can choose to store all columns of a table or a materialized view in the In-Memory Column Store, or only a subset of its columns. Similarly, for a partitioned table, you can choose to store all of the table's partitions in the In-Memory Column Store, or only a subset of the partitions. Enabling the In-Memory Column Store at the tablespace level automatically enables all tables and materialized views in the tablespaces for the In-Memory Column store.

12.4.2 Performance Benefits of Using the In-Memory Column Store

By storing database objects in memory, Oracle Database can perform scans, queries, joins, and aggregates much faster than on disk. The In-Memory Column Store can drastically improve performance when:

  • Scanning a large number of rows and applying filters, such as <, >, =, and IN.

  • Querying a small subset of columns from a large number of columns, such as selecting 5 columns from a table with 100 columns.

  • Joining a small table to a large table, particularly when join conditions filter most of the rows.

  • Aggregating data in a query.

The In-Memory Column Store also improves the performance of data manipulation language (DML) statements. Online transaction processing (OLTP) systems typically require many indexes to be created on commonly accessed columns. These indexes can have a negative performance impact on DML statements. When a database object is stored in the In-Memory Column Store, these indexes can be reduced or eliminated because scans run much faster. Reducing the number of indexes improves the performance of DML statements because fewer indexes need to be updated.

See Also:

Oracle Database Concepts for information about In-Memory Column Store benefits

12.4.3 Estimating the Required Size of the In-Memory Column Store

Using the In-Memory Column Store requires a great deal of memory. To reduce its memory requirements, the In-Memory Column Store enables you to compress the data it stores. Queries are then executed directly on the compressed data. You can specify the compression method for each database object to be stored in the In-Memory Column Store. The amount of memory required by the In-Memory Column Store thus depends on the database objects that you want to store in it and their individual compression methods.

On one hand, choosing a high compression method reduces the amount of memory required by the In-Memory Column Store, but does not provide the greatest performance benefits. On the other hand, choosing a low compression method provides the greatest performance benefits, but also requires more memory. In choosing the compression method for the database objects you want to store in the In-Memory Column Store, balance the performance benefits you want to achieve with the amount of available memory.

The In-Memory Column Store supports the following compression methods:

  • NO MEMCOMPRESS

    This compression method does not compress data.

  • MEMCOMPRESS FOR DML

    This compression method optimizes the data for DML operations and compresses In-Memory Column Store data the least.

  • MEMCOMPRESS FOR QUERY LOW

    This compression method results in the best query performance. This method compresses In-Memory Column Store data more than MEMCOMPRESS FOR DML but less than MEMCOMPRESS FOR QUERY HIGH.

  • MEMCOMPRESS FOR QUERY HIGH

    This compression method results in excellent query performance. This method compresses In-Memory Column Store data more than MEMCOMPRESS FOR QUERY LOW but less than MEMCOMPRESS FOR CAPACITY LOW.

  • MEMCOMPRESS FOR CAPACITY LOW

    This compression method results in good query performance. This method compresses In-Memory Column Store data more than MEMCOMPRESS FOR QUERY HIGH but less than MEMCOMPRESS FOR CAPACITY HIGH.

  • MEMCOMPRESS FOR CAPACITY HIGH

    This compression method results in fair query performance. This method compresses In-Memory Column Store data the most.

Example 12-2 shows an example that enables the oe.product_information table for the In-Memory Column Store and specifies the compression method MEMCOMPRESS FOR CAPACITY HIGH.

Example 12-2 Enabling a Table for the In-Memory Column Store with MEMCOMPRESS FOR CAPACITY HIGH Compression

ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

See Also:

Oracle Database Administrator's Guide for information about how to estimate compression ratio using Compression Advisor

Once you have estimated the amount of memory needed to store each database object, you can calculate the total amount of memory required by the In-Memory Column Store as roughly the sum of the amount of memory required by all database objects that you want to store. You should also include some additional space to allow for the growth of the database objects, and to store updated versions of rows after DML operations.

See Also:

Oracle Database Administrator's Guide for information about the in-memory compression methods

12.4.4 Sizing the In-Memory Column Store

After you have determined the memory required to store your database objects in the In-Memory Column Store based on their compression methods, you can set its size by using the INMEMORY_SIZE initialization parameter.

To set the size of the In-Memory Column Store: 

  • Set the INMEMORY_SIZE initialization parameter to the required size.

    The default value of this parameter is 0, which means that the In-Memory Column Store is not used. To enable the In-Memory Column Store, set this parameter to a nonzero value.

    In a multitenant environment, you can set this parameter per pluggable database (PDB) to specify the size of the In-Memory Column Store for each PDB. The sum of the PDB values does not have to equal the value for the container database (CDB), and may even be greater.

Example 12-3 shows an example of setting the size of the In-Memory Column Store to 100 GB.

Example 12-3 Setting the Size of the In-Memory Column Store

ALTER SYSTEM
  SET INMEMORY_SIZE = 100G;

After setting the size of the In-Memory Column Store, you must restart your database instance to enable the database objects to be stored in it.

See Also: