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

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.

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.

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

To enable a table for fast ingest:

1. In SQL*Plus, log in to the database as a user with ALTER TABLE privileges.

2. Run the CREATE TABLE or ALTER TABLE statement with the MEMOPTIMIZE FOR WRITE clause.

   The following example creates a new table test_fast_ingest and enables it for fast ingest:

   CREATE TABLE test_fast_ingest (
        id        NUMBER(5) PRIMARY KEY,
        test_col  VARCHAR2(15))
     MEMOPTIMIZE FOR WRITE;

   The following example enables the existing table hr.employees for fast ingest:

   ALTER TABLE hr.employees MEMOPTIMIZE FOR WRITE;

Prerequisites

This task assumes that a table is already enabled for fast ingest.

To use fast ingest for data inserts:

1. In SQL*Plus, log in to the database as a user with the privileges to insert data into tables.

2. Run the INSERT statement with the MEMOPTIMIZE_WRITE hint for a table that is already enabled for fast ingest.

   For example:

   INSERT  /*+ MEMOPTIMIZE_WRITE */ INTO test_fast_ingest VALUES (1,'test');

To disable a table for fast ingest:

1. In SQL*Plus, log in to the database as a user with the ALTER TABLE privileges.

2. Run the ALTER TABLE statement with the NO MEMOPTIMIZE FOR WRITE clause.

   The following example disables the table hr.employees for fast ingest:

   ALTER TABLE hr.employees NO MEMOPTIMIZE FOR WRITE;

Overview of the V$MEMOPTIMIZE_WRITE_AREA view

The V$MEMOPTIMIZE_WRITE_AREA view provides the following information about the memory usage and data inserts in the large pool by fast ingest:

• Total amount of memory allocated for fast ingest data in the large pool

• Total amount of memory currently used by fast ingest data in the large pool

• Total amount of memory currently free for storing fast ingest data in the large pool

• Number of fast ingest insert operations for which data is still in the large pool and is yet to be written to disk

• Number of clients currently using fast ingest for inserting data into the database

Overview of the DBMS_MEMOPTIMIZE package subprograms

You can use the following subprograms of the DBMS_MEMOPTIMIZE package to view and control the fast ingest data in the large pool:

Overview of the DBMS_MEMOPTIMIZE_ADMIN package subprograms

You can use the following subprograms of the DBMS_MEMOPTIMIZE_ADMIN package to control the fast ingest data in the large pool:

Prerequisites

This task assumes that the COMPATIBLE initialization parameter is set to 18.0.0 or higher.

To enable the memoptimize pool:

1. In SQL*Plus, log in to the database as a user with administrative privileges.

2. Set the MEMOPTIMIZE_POOL_SIZE initialization parameter to a non-zero value. The minimum setting is 100 MB. When you set this initialization parameter in a server parameter file (SPFILE) using the ALTER SYSTEM statement, you must specify SCOPE=SPFILE.

   For example, the following statement sets the memoptimize pool size to 10 GB:

   ALTER SYSTEM SET MEMOPTIMIZE_POOL_SIZE = 10G SCOPE=SPFILE;

3. Restart the database for the change to take effect.

Example: Enabling the Memoptimize Pool

Assume that the MEMOPTIMIZE_POOL_SIZE initialization parameter is initially set to 0. The following example enables the memoptimize pool by setting the MEMOPTIMIZE_POOL_SIZE to 10 GB:

SQL> SHOW PARAMETER MEMOPTIMIZE_POOL_SIZE

NAME                   TYPE         VALUE
---------------------  -----------  -----
memoptimize_pool_size  big integer  0

SQL> ALTER SYSTEM SET MEMOPTIMIZE_POOL_SIZE=10G SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 1.1832E+10 bytes
Fixed Size                  9010864 bytes
Variable Size            1.1799E+10 bytes
Database Buffers           16777216 bytes
Redo Buffers                7766016 bytes
Database mounted.
Database opened.

SQL> SHOW PARAMETER MEMOPTIMIZE_POOL_SIZE

NAME                   TYPE         VALUE
---------------------  -----------  -----
memoptimize_pool_size  big integer  10G

Prerequisites

This task assumes that the memoptimize pool is enabled.

To enable a table for fast lookup:

1. In SQL*Plus, log in to the database as a user with ALTER TABLE privileges.

2. Run the CREATE TABLE or ALTER TABLE statement with the MEMOPTIMIZE FOR READ clause for the table that needs to be enabled for fast lookup.

   The following example creates a new table test_fast_lookup and enables it for fast lookup:

   CREATE TABLE test_fast_lookup (
        id        NUMBER(5) PRIMARY KEY,
        test_col  VARCHAR2(15))
     MEMOPTIMIZE FOR READ;

   The following example enables the existing table hr.employees for fast lookup:

    ALTER TABLE hr.employees MEMOPTIMIZE FOR READ;

Prerequisites

This task assumes that a table is already enabled for fast lookup.

To disable a table for fast lookup:

1. In SQL*Plus, log in to the database as a user with the ALTER TABLE privileges.

2. Run the ALTER TABLE statement with the NO MEMOPTIMIZE FOR READ clause for the table that needs to be disabled for fast lookup.

   The following example disables the hr.employees table for fast lookup:

   ALTER TABLE hr.employees NO MEMOPTIMIZE FOR READ;
   

Overview of the DBMS_MEMOPTIMIZE package subprograms

The following are the DBMS_MEMOPTIMIZE package subprograms that can be used to delete or populate fast lookup data for a table in the memoptimize pool: