12 Tuning the System Global Area

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:

• Setting the SGA_TARGET Parameter in Oracle Enterprise Manager

• Setting the SGA_TARGET Parameter in the Command-Line Interface

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:

• Enabling Automatic Shared Memory Management

• Disabling Automatic Shared Memory Management

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.

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.

12.2.4 Operating System Memory Use

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

• Reduce Paging

• Fit the SGA into Main Memory

• Allow Adequate Memory to Individual Users

SHOW SGA

Total System Global Area  840205000 bytes
Fixed Size                   279240 bytes
Variable Size             520093696 bytes
Database Buffers          318767104 bytes
Redo Buffers                1064960 bytes

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.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.

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.

ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

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.

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.

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.