6 Managing Memory

About Automatic Shared Memory Management

Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.

When automatic shared memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.

If you are using a server parameter file (SPFILE), the database remembers the sizes of the automatically tuned SGA components across instance shutdowns. As a result, the database instance does not need to learn the characteristics of the workload again each time the instance is started. The instance can begin with information from the previous instance and continue evaluating workload where it left off at the last shutdown.

Components and Granules in the SGA

The SGA comprises several memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

The memory for dynamic components in the SGA is allocated in the unit of granules. The granule size is determined by the amount of SGA memory requested when the instance starts. Specifically, the granule size is based on the value of the SGA_MAX_SIZE initialization parameter. Table 6-1 shows the granule size for different amounts of SGA memory.

Some platform dependencies may arise. Consult your operating system specific documentation for more details.

You can query the V$SGAINFO view to see the granule size that is being used by an instance. The same granule size is used for all components in the SGA.

If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, the database actually allocates 12 MB.

Setting Maximum SGA Size

The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool, and streams pool but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by SGA_MAX_SIZE.

If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization time. If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter.

Setting SGA Target Size

You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a nonzero value. This parameter sets the total size of the SGA. It replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically and dynamically resized (tuned) as needed.

Table 6-2 lists the SGA components that are automatically sized when SGA_TARGET is set. For each SGA component, its corresponding initialization parameter is listed.

The manually sized parameters listed in Table 6-3, if they are set, take their memory from SGA_TARGET, leaving what is available for the components listed in Table 6-2.

In addition to setting SGA_TARGET to a nonzero value, you must set to zero all initialization parameters listed in Table 6-2 to enable full automatic tuning of the automatically sized SGA components.

Alternatively, you can set one or more of the automatically sized SGA components to a nonzero value, which is then used as the minimum setting for that component during SGA tuning. This is discussed in detail later in this section.

SQL> select * from v$sga_target_advice order by sga_size;
 
  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       290              .5       448176              1.6578             1636103
       435             .75       339336              1.2552             1636103
       580               1       270344                   1             1201780
       725            1.25       239038               .8842              907584
       870             1.5       211517               .7824              513881
      1015            1.75       201866               .7467              513881
      1160               2       200703               .7424              513881

Enabling Automatic Shared Memory Management

The procedure for enabling automatic shared memory management (ASMM) differs depending on whether you are changing to ASMM from manual shared memory management or from automatic memory management.

To change to ASMM from manual shared memory management:

1. Run the following query to obtain a value for SGA_TARGET:

   SELECT (
      (SELECT SUM(value) FROM V$SGA) -
      (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
      ) "SGA_TARGET"
   FROM DUAL;
   

2. Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:

   ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
   

   where value is the value computed in step 1 or is some value between the sum of all SGA component sizes and SGA_MAX_SIZE. For more information on the ALTER SYSTEM statement and its SCOPE clause, see Oracle Database SQL Language Reference.

3. Do one of the following:

   • For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table 6-2 to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.

   • To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. (See the next section for details.) Set the values of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.

To change to ASMM from automatic memory management:

1. Set the MEMORY_TARGET initialization parameter to 0.

   ALTER SYSTEM SET MEMORY_TARGET = 0;
   

   The database sets SGA_TARGET based on current SGA memory allocation.

2. Do one of the following:

   • For more complete automatic tuning, set the sizes of the automatically sized SGA components listed in Table 6-2 to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.

   • To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. (See the next section for details.) Set the sizes of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.

ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;

Automatic Shared Memory Management Advanced Topics

This section provides a closer look at automatic shared memory management. It includes the following topics:

• Setting Minimums for Automatically Sized SGA Components

• Dynamic Modification of SGA_TARGET

• Modifying Parameters for Automatically Sized Components

• Modifying Parameters for Manually Sized Components

Enabling Manual Shared Memory Management

There is no initialization parameter that in itself enables manual shared memory management. You effectively enable manual shared memory management by disabling both automatic memory management and automatic shared memory management.

To enable manual shared memory management:

1. Set the MEMORY_TARGET initialization parameter to 0.

2. Set the SGA_TARGET initialization parameter to 0.

You must then set values for the various SGA components, as described in the following sections.

Setting the Buffer Cache Initialization Parameters

The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use them to specify the sizes of caches for the various block sizes used by the database. These initialization parameters are all dynamic.

The size of a buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping.

Oracle Database supports multiple block sizes in a database. If you create tablespaces with non-standard block sizes, you must configure non-standard block size buffers to accommodate these tablespaces. The standard block size is used for the SYSTEM tablespace. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.

If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set. Oracle Database assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured.

The sizes and numbers of non-standard block size buffers are specified by the following parameters:

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Each parameter specifies the size of the cache for the corresponding block size.

DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=1024M
DB_2K_CACHE_SIZE=256M
DB_8K_CACHE_SIZE=512M

Specifying the Shared Pool Size

The SHARED_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the shared pool component of the SGA. Oracle Database selects an appropriate default value.

In releases before Oracle Database 10g, the amount of shared pool memory that was allocated was equal to the value of the SHARED_POOL_SIZE initialization parameter plus the amount of internal SGA overhead computed during instance startup. The internal SGA overhead refers to memory that is allocated by Oracle Database during startup, based on the values of several other initialization parameters. This memory is used to maintain state for different server components in the SGA. For example, if the SHARED_POOL_SIZE parameter is set to 64MB and the internal SGA overhead is computed to be 12MB, the real size of the shared pool is 64+12=76MB, although the value of the SHARED_POOL_SIZE parameter is still displayed as 64MB.

Starting with Oracle Database 10g, the size of the internal SGA overhead is included in the user-specified value of SHARED_POOL_SIZE. If you are not using automatic memory management or automatic shared memory management, the amount of shared pool memory that is allocated at startup is equal to the value of the SHARED_POOL_SIZE initialization parameter, rounded up to a multiple of the granule size. You must therefore set this parameter so that it includes the internal SGA overhead in addition to the desired value for shared pool size. In the previous example, if the SHARED_POOL_SIZE parameter is set to 64MB at startup, then the available shared pool after startup is 64-12=52MB, assuming the value of internal SGA overhead remains unchanged. In order to maintain an effective value of 64MB for shared pool memory after startup, you must set the SHARED_POOL_SIZE parameter to 64+12=76MB.

When migrating from a release that is earlier than Oracle Database 10g, the Oracle Database 12c migration utilities recommend a new value for this parameter based on the value of internal SGA overhead in the pre-upgrade environment and based on the old value of this parameter. Beginning with Oracle Database 10g, the exact value of internal SGA overhead, also known as startup overhead in the shared pool, can be queried from the V$SGAINFO view. Also, in manual shared memory management mode, if the user-specified value of SHARED_POOL_SIZE is too small to accommodate even the requirements of internal SGA overhead, then Oracle Database generates an ORA-00371 error during startup, with a suggested value to use for the SHARED_POOL_SIZE parameter.When you use automatic shared memory management in Oracle Database 12c, the shared pool is automatically tuned, and an ORA-00371 error would not be generated.

Specifying the Large Pool Size

The LARGE_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the large pool component of the SGA. The large pool is an optional component of the SGA. You must specifically set the LARGE_POOL_SIZE parameter to create a large pool. Configuring the large pool is discussed in Oracle Database Performance Tuning Guide.

Specifying the Java Pool Size

The JAVA_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the java pool component of the SGA. Oracle Database selects an appropriate default value. Configuration of the java pool is discussed in Oracle Database Java Developer's Guide.

Specifying the Streams Pool Size

The STREAMS_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the Streams Pool component of the SGA. If STREAMS_POOL_SIZE is set to 0, then the Oracle Streams product transfers memory from the buffer cache to the Streams Pool when it is needed. For details, see the discussion of the Streams Pool in Oracle Streams Replication Administrator's Guide.

Specifying the Result Cache Maximum Size

The RESULT_CACHE_MAX_SIZE initialization parameter is a dynamic parameter that enables you to specify the maximum size of the result cache component of the SGA. Typically, there is no need to specify this parameter, because the default maximum size is chosen by the database based on total memory available to the SGA and on the memory management method currently in use. You can view the current default maximum size by displaying the value of the RESULT_CACHE_MAX_SIZE parameter. To change this maximum size, you can set RESULT_CACHE_MAX_SIZE with an ALTER SYSTEM statement, or you can specify this parameter in the text initialization parameter file. In each case, the value is rounded up to the nearest multiple of 32K.

If RESULT_CACHE_MAX_SIZE is 0 upon instance startup, the result cache is disabled. To reenable it you must set RESULT_CACHE_MAX_SIZE to a nonzero value (or remove this parameter from the text initialization parameter file to get the default maximum size) and then restart the database.

Note that after starting the database with the result cache disabled, if you use an ALTER SYSTEM statement to set RESULT_CACHE_MAX_SIZE to a nonzero value but do not restart the database, querying the value of the RESULT_CACHE_MAX_SIZE parameter returns a nonzero value even though the result cache is still disabled. The value of RESULT_CACHE_MAX_SIZE is therefore not the most reliable way to determine if the result cache is enabled. You can use the following query instead:

SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
---------------------------------------------
ENABLED

The result cache takes its memory from the shared pool, so if you increase the maximum result cache size, consider also increasing the shared pool size.

The view V$RESULT_CACHE_STATISTICS and the PL/SQL package procedure DBMS_RESULT_CACHE.MEMORY_REPORT display information to help you determine the amount of memory currently allocated to the result cache.

The PL/SQL package function DBMS_RESULT_CACHE.FLUSH clears the result cache and releases all the memory back to the shared pool.

Specifying Miscellaneous SGA Initialization Parameters

You can set a few additional initialization parameters to control how the SGA uses memory.

Overview of the IM Column Store

The IM column store is a new static pool in the SGA. Data in the IM column store does not reside in the traditional row format but instead in a columnar format. Each column is stored as a separate structure. The IM column store does not replace the buffer cache, but acts as a supplement, so that data can be stored in memory in both row and columnar formats. To enable the IM column store, the INMEMORY_SIZE initialization parameter must be set to a non-zero value.

You can enable the IM column store at any of the following levels:

• Column

• Table

• Materialized view

• Tablespace

• Partition

If it is enabled at the tablespace level, then all tables and materialized views in the tablespace are enabled for the IM column store by default. You can populate all of a database object's columns in the IM column store or a subset of the database object's columns. Similarly, for a partitioned table or materialized view, you can populate all of the partitions in the IM column store or a subset of the partitions.

Storing a database object in the IM column store can improve performance significantly for the following types of operations performed on the database object:

• A query that scans a large number of rows and applies filters that use operators such as the following: =, <, >, and IN

• A query that selects a small number of columns from a table or materialized view with a large number of columns, such as a query that selects five columns from a table with 100 columns

• A query that joins a small table to a large table

• A query that aggregates data

Typically, multi-column indexes are created to improve the performance of analytic and reporting queries. These indexes can impede the performance of data manipulation language (DML) statements. When a database object is populated in the IM column store, indexes used for analytic or reporting queries can be reduced or eliminated without affecting query performance. Eliminating these indexes can improve the performance of transactions and data loading operations.

You enable database objects for the IM column store by including an INMEMORY clause in the following SQL statements:

• CREATE TABLE

• ALTER TABLE

• CREATE TABLESPACE

• ALTER TABLESPACE

• CREATE MATERIALIZED VIEW

• ALTER MATERIALIZED VIEW

To determine which database objects are populated in the IM column store currently, run the following query on the V$IM_SEGMENTS view:

SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION 
   FROM V$IM_SEGMENTS;

The IM column store does not improve performance for the following types of operations:

• Queries with complex predicates

• Queries that select a large number of columns

• Queries that return a large number of rows

• Queries with multiple large table joins

Also, a database object cannot be populated in the IM column store if it is owned by the SYS user and it is stored in the SYSTEM or SYSAUX tablespace.

IM Column Store Compression Methods

In the IM column store, data can be compressed, and SQL queries execute directly on compressed data.

Table 6-5 summarizes the data compression methods supported in the IM column store.

In a SQL statement, the MEMCOMPRESS keyword must be preceded by the INMEMORY keyword.

IM Column Store Data Population Options

When you enable a database object for the IM column store, you can either let Oracle Database control when the database object's data is populated in the IM column store (default), or you can specify a priority level that determines the priority of the database object in the population queue. Oracle SQL includes an INMEMORY PRIORITY subclause that provides more control over the queue for population. For example, it might be more important or less important to populate a database object's data before populating the data for other database objects.

Table 6-6 describes the supported priority levels.

When more than one database object has a priority level other than NONE, Oracle Database queues all of the data for the database objects to be populated in the IM column store based on priority level. Data for database objects with the CRITICAL priority level are populated first, data for database objects with the HIGH priority level are populated next, and so on. If there is no space remaining in the IM column store, then no additional objects are populated in it until sufficient space becomes available.

When a database is restarted, all of the data for database objects with a priority level other than NONE are populated in the IM column store during startup. For a database object with a priority level other than NONE, an ALTER TABLE or ALTER MATERIALIZED VIEW DDL statement involving the database object does not return until the DDL changes are recorded in the IM column store.

Examples of Enabling and Disabling the IM Column Store for Tables

The following examples illustrate how to enable or disable tables for the IM column store:

• Example 6-1, "Creating a Table and Enabling It for the IM Column Store"

• Example 6-2, "Enabling a Table for the IM Column Store"

• Example 6-3, "Enabling a Table for the IM Column Store with FOR CAPACITY LOW Compression"

• Example 6-5, "Enabling a Table for the IM Column Store with FOR CAPACITY HIGH Compression and LOW Data Population Priority"

• Example 6-6, "Enabling Columns in a Table for the IM Column Store"

• Example 6-7, "Disabling a Table for the IM Column Store"

CREATE TABLE test_inmem (
     id        NUMBER(5) PRIMARY KEY,
     test_col  VARCHAR2(15))
  INMEMORY;

This example uses the defaults for the INMEMORY clause. Therefore, MEMCOMPRESS FOR QUERY is used, and PRIORITY NONE is used.

ALTER TABLE oe.product_information INMEMORY;

This example uses the defaults for the INMEMORY clause. Therefore, MEMCOMPRESS FOR QUERY is used, and PRIORITY NONE is used.

ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY LOW;

This example uses the default for the PRIORITY clause. Therefore, PRIORITY NONE is used.

ALTER TABLE oe.product_information INMEMORY PRIORITY HIGH;

This example uses the default for the MEMCOMPRESS clause. Therefore, MEMCOMPRESS FOR QUERY is used.

ALTER TABLE oe.product_information INMEMORY 
  MEMCOMPRESS FOR CAPACITY HIGH 
  PRIORITY LOW;

ALTER TABLE oe.product_information  
   INMEMORY MEMCOMPRESS FOR QUERY (
      product_id, product_name, category_id, supplier_id, min_price)
   INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (
      product_description, warranty_period, product_status, list_price)
   NO INMEMORY (
      weight_class, catalog_url);

Specifically, this example specifies the following:

• The list of columns starting with product_id and ending with min_price are enabled for the IM column store with the MEMCOMPRESS FOR QUERY compression method.

• The list of columns starting with product_description and ending with list_price are enabled for the IM column store with the MEMCOMPRESS FOR CAPACITY HIGH compression method.

• The weight_class and catalog_url columns are not enabled for the IM column store.

This example uses the default for the PRIORITY clause. Therefore, PRIORITY NONE is used.

You can query the V$IM_COLUMN_LEVEL view to determine the selective column compression levels that are defined for a database object.

ALTER TABLE oe.product_information NO INMEMORY;

Prerequisites to Using IM Column Store in Enterprise Manager

Before you can enable a database to use the IM column store, you must ensure that the database is at a Release 12.1.0.2 or higher compatibility level. In other words, the value for the initialization parameter COMPATIBLE should be set to 12.1.0.0. To set the compatibility level, follow these steps:

1. From the Database Home page in Enterprise Manager, navigate to the Initialization Parameters page by choosing Initialization Parameters from the Administration menu.

   You can use this page to set or change the compatibility level.

2. Search for the parameter COMPATIBLE.

   The category for the parameter is Miscellaneous.

3. Change the value to 12.1.0.0 and click Apply.

   You will be prompted to restart the database.

After the database is restarted, the new value that you set takes effect.

Similarly, you can set or change the size of the IM column store. To do so, follow these steps:

1. From the Database Home page in Enterprise Manager, navigate to the Initialization Parameters page by choosing Initialization Parameters from the Administration menu.

2. Search for the parameter INMEMORY_SIZE. The category for the parameter is In-Memory.

3. Change the value and click Apply.

   You can set the value to any value above the minimum size of 100M.

   You will then be prompted to restart the database.

Using the In-Memory Column Store Central Home Page to Monitor In-Memory Support for Database Objects

You can use the In-Memory Column Store Central Home page to monitor in-memory support for database objects such as tables, indexes, partitions and tablespaces. You can view in-memory functionality for objects and monitor their In-Memory usage statistics.

The In-Memory Object Access Heatmap displays the top 100 objects in the In-Memory Store with their relative sizes and shows you how frequently objects are accessed, represented by different colors. To activate the heat map, you must turn on the option for the heatmap in the init.ora parameter file. Generally there is a one day wait period before the map is activated. You can use the date selector to pick the date range for objects displayed in the Heat Map. You can also use the slider to control the granularity of the color.

Use the Configuration section to view the status settings such as In-Memory Query, In-Memory Force, and Default In-Memory Clause. Click Edit to navigate to the Initialization Parameters page where you can change the values and settings displayed in this section. Use the Performance section to view the metrics for Active Sessions.

Use the Objects Summary section to view the Compression Factor and data about the memory used by the populated objects. The In-Memory Enabled Object Statistics are available in a pop-up window through a drill-down from the View In-Memory Enabled Object Statistics link on the page.

Use the In-Memory Objects Distribution section to view the distribution on a percentage basis of the various objects used in memory. The section includes a chart showing the distribution of Partitions, Sub-partitions, Non-partitioned Tables, and Non-partitioned Materialized Views. The numerical values for each are displayed above the chart.

Use the In-Memory Objects Search section to search for objects designated for In-Memory use. Click Search after you enter the parameters by which you want to search. The results table shows the Name of each object found along with its Size, Size in Memory, Size on Disk, In-Memory percentage, and its In-Memory parameters. You can also search for accessed objects that are either in-memory or not in-memory. If the heatmap is enabled, the Accessed Objects option appears in the drop-down list in the View field of the In-Memory Objects Search box. When you select Accessed Objects, you can filter based on the top 100 objects with access data that are either in-memory or not in-memory. You can select a time range and search for objects within that range. If you select the All Objects In-Memory option, you can view the list of top 100 objects that are in-memory based on their in-memory size.

If you are working in a RAC environment, you can quickly move between instances by selecting the instance in the Instances selection box above and on the right side of the heatmap.

Specifying In-Memory Details When Creating a Table or Partition

To specify IM column store details when creating a table or partition, follow these steps:

1. From the Schema menu, choose Database Objects, then select the Tables option.

2. Click Create to create a table.

   The Create Table page is shown. Select the In-Memory Column Store tab to specify the in-memory options for the table.

3. Choose to override the column level in-memory details (if required) in the table below where the columns are specified.

4. Optionally, you can click on the Partitions tab.

5. Create table partitions as needed using the wizard.

   To specify IM column store details for a partition, select it from the table in the Partitions tab, and then click Advanced Options.

6. After entering all necessary IM column store details at the table level, column level, and partitions level, click Show SQL to see the generated SQL. Click OK to create the table.

Viewing or Editing IM Column Store Details of a Table

To view or edit IM column store details of a table, follow these steps:

1. From the Schema menu, choose Database Objects, then select the Tables option.

2. Search for the desired table and click View to view its details.

3. Click Edit to launch the Edit Table page.

   Alternatively, you can also click Edit on the Search page. Use the In-Memory Column Store tab to specify in-memory options for the table.

4. Edit the required details and click Apply.

Viewing or Editing IM Column Store Details of a Partition

To view or edit IM column store details of a partition, follow these steps:

1. From the Schema menu, choose Database Objects, then select the Tables option.

2. Search for the table that contains the desired partition, select it, then click View.

3. Click Edit to launch the Edit Table page.

   Alternatively, you can also click Edit on the Table Search page.

4. Click the Partitions tab, select the desired partition, and click Advanced Options.

5. Edit the required details, and click OK to go back to the Partitions tab.

6. After making similar changes to all desired partitions of the table, click Apply.

Specifying IM Column Store Details During Tablespace Creation

To specify IM column store details when creating a tablespace, follow these steps:

1. From the Administration menu, choose Storage, and then select the Tablespaces option.

2. Click Create to create a tablespace.

3. Enter the details that appear on the General tab.

   Click the In-Memory Column Store tab.

4. After entering all required IM column store details for the tablespace, click Show SQL. Click Return from the Show SQL page and then in the resulting page click OK.

5. Click OK to create the tablespace.

The IM column store settings of a tablespace apply for any new table created in the tablespace. IM column store configuration details must be specified at the individual table level if a table must override the configuration of the tablespace.

Viewing and Editing IM Column Store Details of a Tablespace

To view or edit IM column store details of a tablespace, follow these steps:

1. From the Administration menu, choose Storage, then select the Tablespaces option.

2. Search for the desired tablespace, select it, then click View.

3. Click Edit to launch the Edit Tablespace page, then click the In-Memory Column Store tab.

4. Edit the required details and click Apply.

Specifying IM Column Store Details During Materialized View Creation

To specify IM column store details when creating a materialized view, follow these steps:

1. From the Schema menu, choose Materialized Views, then select the Materialized Views option.

2. Click Create to create a materialized view.

3. Enter the materialized view name, and specify its query.

4. Click the In-Memory Column Store tab to specify IM column store options for the materialized view.

5. After entering all necessary IM column store details, click Show SQL. Click Return from the Show SQL page, and then in the resulting page click OK.

6. Click OK to create the materialized view.

Viewing or Editing IM Column Store Details of a Materialized View

To view or edit IM column store details of a materialized view, follow these steps:

1. From the Schema menu, choose Materialized Views, then select the Materialized Views option.

2. Search for the desired materialized view, and click View to view its details.

3. Click Edit to launch the Edit Materialized View page.

4. Click the In-Memory Column Store tab to specify IM column store options for the materialized view.

5. Edit the required details, and click Apply.