Using the In-Memory Column Store
Note:
This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).
The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans. The IM column store is included with the Oracle Database In-Memory option.

内存中的列存储(IM列存储)是一个可选的部分系统全局区(SGA)存储表的副本,表分区,和其他数据库对象。IM列存储,数据填充的列而不是行其他地区的SGA,快速扫描和数据优化。我列存储包含Oracle数据库内存选项。

This section contains the following topics:

About the IM Column Store
Initialization Parameters Related to the IM Column Store
Enabling the IM Column Store for a Database
Enabling and Disabling Tables for the IM Column Store
Enabling and Disabling Tablespaces for the IM Column Store
Enabling and Disabling Materialized Views for the IM Column Store
Data Pump and the IM Column Store
Using IM Column Store In Enterprise Manager

About the IM Column Store
This section contains the following topics:

Overview of the IM Column Store
IM Column Store Compression Methods
IM Column Store Data Population Options

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_SIZEinitialization parameter must be set to a non-zero value.

我列存储是一种新的静态池SGA。我列中的数据存储不驻留在传统行格式,而是在一个柱状的格式。每一列作为一个单独的存储结构。IM列存储不替换缓冲区缓存,但作为一个补充,这样两行数据可以存储在内存和柱状格式。让我列存储,INMEMORY_SIZE初始化参数必须设置为非零值。

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.

如果启用了它在表空间级别,那么所有表和表空间的物化视图为IM启用默认列存储。你可以填充所有数据库对象的列的IM列存储或数据库对象的列的一个子集。同样,对于分区表或物化视图,您可以填充所有的分区的IM列存储或分区的一个子集。

列式存储在查询上的优势：

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;
你可以增加优先级增加的可能性数据库对象填充IM列存储，意思就是你可以增加可能性数据在im列存储的优先级。
Note:
A database object that is enabled for the IM column store might not be populated in it. Therefore, such a database object might not appear in the results for this query. However, you can increase the priority level to increase the likelihood that the database object is populated the IM column store. See "IM Column Store Data Population Options". Other views, such as the DBA_TABLES view, show candidates for the IM column store.
列式存储所不能提高性能的操作：

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列存储所拥有的 SYS用户存储在 SYSTEM或 SYSAUX表空间。

See Also:
"Initialization Parameters Related to the IM Column Store"

"Enabling the IM Column Store for a Database"

Oracle Database Concepts

Oracle Database SQL Language Reference for more information about the INMEMORY clause

Oracle Real Application Clusters Administration and Deployment Guide for more information about the IM column store and Oracle RAC

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

Video:
Oracle Database 12c demos: In-Memory Column Store Compression
Table 6-5 summarizes the data compression methods supported in the IM column store.

Table 6-5 IM Column Store Compression Methods

CREATE/ALTER Syntax        Description
NO MEMCOMPRESS

The data is not compressed.

MEMCOMPRESS FOR DML

This method optimizes the data for DML operations and compresses IM column store data the least (excluding NO MEMCOMPRESS).

MEMCOMPRESS FOR QUERY LOW

This method results in the best query performance.

This method compresses IM column store data more than MEMCOMPRESS FOR DML but less than MEMCOMPRESS FOR QUERY HIGH.

This method is the default when the INMEMORY clause is specified without a compression method in a CREATE or ALTER SQL statement or when MEMCOMPRESS FOR QUERY is specified without including either LOWor HIGH.

MEMCOMPRESS FOR QUERY HIGH

This method results in excellent query performance.

This method compresses IM column store data more than MEMCOMPRESS FOR QUERY LOW but less than MEMCOMPRESS FOR CAPACITY LOW.

MEMCOMPRESS FOR CAPACITY LOW

This method results in good query performance.

This method compresses IM column store data more than MEMCOMPRESS FOR QUERY HIGH but less than MEMCOMPRESS FOR CAPACITY HIGH.

This method is the default when MEMCOMPRESS FOR CAPACITY is specified without including either LOW or HIGH.

MEMCOMPRESS FOR CAPACITY HIGH

This method results in fair query performance.

This method compresses IM column store data the most.


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.

Video:
Oracle Database 12c demos: In-Memory Column Store Priority
Table 6-6 describes the supported priority levels.

Table 6-6 Priority Levels for Populating a Database Object in the IM Column Store

CREATE/ALTER Syntax        Description
PRIORITY NONE

Oracle Database controls when the database object's data is populated in the IM column store. A scan of the database object triggers the population of the object into the IM column store.

This is the default level when PRIORITY is not included in the INMEMORYclause.

PRIORITY LOW

The database object's data is populated in the IM column store before database objects with the following priority level: NONE.

The database object's data is populated in the IM column store after database objects with the following priority levels: MEDIUM, HIGH, orCRITICAL.

PRIORITY MEDIUM

The database object's data is populated in the IM column store before database objects with the following priority levels: NONE or LOW.

The database object's data is populated in the IM column store after database objects with the following priority levels: HIGH or CRITICAL.

PRIORITY HIGH

The database object's data is populated in the IM column store before database objects with the following priority levels: NONE, LOW, or MEDIUM.

The database object's data is populated in the IM column store after database objects with the following priority level: CRITICAL.

PRIORITY CRITICAL

The database object's data is populated in the IM column store before database objects with the following priority levels: NONE, LOW, MEDIUM, orHIGH.


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.

当多个数据库对象以外的优先级 NONE、Oracle数据库队列的所有数据库对象的数据填充的IM列存储基于优先级。数据的数据库对象 CRITICAL优先级第一,填充数据的数据库对象HIGH优先级填充接下来,等等。如果没有剩余空间的IM列存储,然后没有额外的对象填充,直到足够的可用空间。

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.

在数据库重启动的时候，ddl 操作将不会先返回值，除非记录全部存到IM中。

Note:
The priority level setting must apply to an entire table or to a table partition. Specifying different IM column store priority levels for different subsets of columns in a table is not allowed.

If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated in it.

优先级设置必须适用于整个表或表分区。指定不同的IM列存储优先级级别不同子集的列在一个表中是不允许的。

如果磁盘上的部分是64 KB或更少,那么它不是填充IM列存储。因此,一些小型数据库对象启用即时通讯列存储可能不是填充。


Initialization Parameters Related to the IM Column Store
Table 6-7 describes the initialization parameters related to the IM column store.

Table 6-7 Initialization Parameters Related to the IM Column Store

Initialization Parameter        Description
INMEMORY_SIZE

This initialization parameter sets the size of the IM column store in a database instance.

The default value is 0, which means that the IM column store is not used. This initialization parameter must be set to a non-zero value to enable the IM column store. If the parameter is set to a non-zero value, then the minimum setting is 100M.

In a multitenant environment, the setting for this parameter in the root is the setting for the entire multitenant container database (CDB). This parameter can also be set in each pluggable database (PDB) to limit the maximum size of the IM column store for each PDB. The sum of the PDB values can be less than, equal to, or greater than the CDB value. However, the CDB value is the maximum amount of memory available in the IM column store for the entire CDB, including the root and all of the PDBs. Unless this parameter is specifically set for a PDB, the PDB inherits the CDB value, which means that the PDB can use all of the available IM column store for the CDB.

INMEMORY_FORCE

This initialization parameter can enable tables and materialized views for the IM column store or disable all tables and materialized views for the IM column store.

Set this parameter to DEFAULT, the default value, to allow the INMEMORYor NO INMEMORY attributes on the individual database objects determine if they will be populated in the IM column store.

Set this parameter to OFF to specify that all tables and materialized views are disabled for the IM column store.

INMEMORY_CLAUSE_DEFAULT

This initialization parameter enables you to specify a default IM column store clause for new tables and materialized views.

Leave this parameter unset or set it to an empty string to specify that there is no default IM column store clause for new tables and materialized views. Setting the value of this parameter to NO INMEMORYhas the same effect as setting it to the default value (the empty string).

Set this parameter to a valid INMEMORY clause to specify that the clause is the default for all new tables and materialized views. The clause can include valid clauses for IM column store compression methods and data population options.

If the clause starts with INMEMORY, then all new tables and materialized views, including those without an INMEMORY clause, are populated in the IM column store. If the clause omits INMEMORY, then it only applies to new tables and materialized views that are enabled for the IM column store with an INMEMORY clause during creation.

INMEMORY_QUERY

This initialization parameter specifies whether in-memory queries are allowed. Set this parameter to ENABLE, the default value, to allow queries to access database objects populated in the IM column store, or set this parameter to DISABLE to disable access to the database objects populated in the IM column store.

INMEMORY_MAX_POPULATE_SERVERS

This initialization parameter specifies the maximum number of background populate servers to use for IM column store population, so that these servers do not overload the rest of the system. Set this parameter to an appropriate value based on the number of cores in the system.

INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT

This initialization parameter limits the maximum number of background populate servers used for IM column store repopulation, as trickle repopulation is designed to use only a small percentage of the populate servers. The value for this parameter is a percentage of theINMEMORY_MAX_POPULATE_SERVERS initialization parameter value. For example, if this parameter is set to 10 andINMEMORY_MAX_POPULATE_SERVERS is set to 10, then on average one core is used for trickle repopulation.

OPTIMIZER_INMEMORY_AWARE

This initialization parameter enables or disables all of the optimizer cost model enhancements for in-memory. Setting the parameter to FALSEcauses the optimizer to ignore the in-memory property of tables during the optimization of SQL statements.


See Also:
"Specifying Initialization Parameters"
"Managing Initialization Parameters Using a Server Parameter File"
Enabling the IM Column Store for a Database
Before tables, tablespaces, or materialized views can be enabled for the IM column store, you must enable the IM column store for the database.

启动列存表，表空间，物化视图的之前必须要先启用 database；

开启数据库列存的步骤：

To enable the IM column store for a database, complete the following steps:

Ensure that the database is at 12.1.0 or higher compatibility level.

Set the INMEMORY_SIZE initialization parameter to a non-zero value.

When you set this parameter in a server parameter file (SPFILE) using the ALTER SYSTEM statement, you must specify SCOPE=SPFILE.

The minimum setting is 100M.

Restart the database.

You must restart the database to initialize the IM column store in the SGA.

Optionally, you can check the amount of memory currently allocated for the IM column store by entering the following in SQL*Plus:

SHOW PARAMETER INMEMORY_SIZE
See Also:
Oracle Database Upgrade Guide for information about setting the database compatibility level
"Specifying Initialization Parameters"
Oracle Database Reference for more information about the INMEMORY_SIZE initialization parameter
Chapter 3, "Starting Up and Shutting Down"
Enabling and Disabling Tables for the IM Column Store
You enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. You disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.

启用表级别列存的步骤：

To enable or disable a table for the IM column store, complete the following steps:

Ensure that the IM column store is enabled for the database.

See "Enabling the IM Column Store for a Database".

Connect to the database instance as a user with the appropriate privileges to either create the table or alter the table.

Run a CREATE TABLE or ALTER TABLE statement with an INMEMORY clause or a NO INMEMORY clause.

See Also:
Chapter 20, "Managing Tables" for information about creating and altering tables

Oracle Database SQL Language Reference for information about the CREATE TABLE or ALTER TABLE statements

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"

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

The following example creates the test_inmem table and enables it 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.

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

The following example enables the oe.product_information table for the IM column store:

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.

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

The following example enables the oe.product_information table for the IM column store and specifies the compression method FOR CAPACITY LOW:

ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
This example uses the default for the PRIORITY clause. Therefore, PRIORITY NONE is used.

Example 6-4 Enabling a Table for the IM Column Store with HIGH Data Population Priority

The following example enables the oe.product_information table for the IM column store and specifies PRIORITY HIGH for populating the table data in memory:

ALTER TABLE oe.product_information INMEMORY PRIORITY HIGH;
This example uses the default for the MEMCOMPRESS clause. Therefore, MEMCOMPRESS FOR QUERY is used.

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

The following example enables the oe.product_information table for the IM column store and specifies FOR CAPACITY HIGH table compression and PRIORITY LOW for populating the table data in memory:

优先级和压缩能力配合使用：

ALTER TABLE oe.product_information INMEMORY
  MEMCOMPRESS FOR CAPACITY HIGH
  PRIORITY LOW;
Example 6-6 Enabling Columns in a Table for the IM Column Store

This example enables some columns in the oe.product_information table for the IM column store but not others. It also specifies different IM column store compression methods for the columns enabled for the IM column store.

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

Note:
The priority level setting must apply to an entire table or to a table partition. Specifying different IM column store priority levels for different subsets of columns in a table is not allowed.
Oracle Database Reference for more information about the V$IM_COLUMN_LEVEL view
Example 6-7 Disabling a Table for the IM Column Store

To disable a table for the IM column store, use the NO INMEMORY clause. The following example disables the oe.product_information table for the IM column store:

ALTER TABLE oe.product_information NO INMEMORY;
Note:
You can query the V$IM_SEGMENTS view to list the database objects that are populated in the IM column store. See "Overview of the IM Column Store" for a sample query.
Enabling and Disabling Tablespaces for the IM Column Store
You can enable a tablespace for the IM column store during tablespace creation with a CREATE TABLESPACE statement that includes the INMEMORYclause. You can also alter a tablespace to enable it for the IM column store with an ALTER TABLESPACE statement that includes the INMEMORY clause.

You disable a tablespace for the IM column store by including a NO INMEMORY clause in a CREATE TABLESPACE or ALTER TABLESPACE statement.

When a tablespace is enabled for the IM column store, all tables and materialized views in the tablespace are enabled for the IM column store by default. The INMEMORY clause is the same for tables, materialized views, and tablespaces. The DEFAULT storage clause is required before theINMEMORY clause when enabling a tablespace for the IM column store and before the NO INMEMORY clause when disabling a tablespace for the IM column store.

When a tablespace is enabled for the IM column store, individual tables and materialized views in the tablespace can have different in-memory settings, and the settings for individual database objects override the settings for the tablespace. For example, if the tablespace is set to PRIORITY LOW for populating data in memory, but a table in the tablespace is set to PRIORITY HIGH, then the table uses PRIORITY HIGH.

To enable or disable a tablespace for the IM column store, complete the following steps:

启用或者禁用表空间IM列存的步骤：

Ensure that the IM column store is enabled for the database.

See "Enabling the IM Column Store for a Database".

Connect to the database instance as a user with the appropriate privileges to either create the tablespace or alter the tablespace.

Run a CREATE TABLESPACE or ALTER TABLESPACE statement with an INMEMORY clause or a NO INMEMORY clause.

Example 6-8 Creating a Tablespace and Enabling It for the IM Column Store

The following example creates the tbs1 tablespace and enables it for the IM column store:

CREATE TABLESPACE tbs1
   DATAFILE 'tbs1.dbf' SIZE 40M
   ONLINE
   DEFAULT INMEMORY;
This example uses the defaults for the INMEMORY clause. Therefore, MEMCOMPRESS FOR QUERY is used, and PRIORITY NONE is used.

Example 6-9 Altering a Tablespace to Enable It for the IM Column Store

The following example alters the tbs1 tablespace to enable it for the IM column store and specifies FOR CAPACITY HIGH compression for the database objects in the tablespace and PRIORITY LOW for populating data in memory:

ALTER TABLESPACE tbs1 DEFAULT INMEMORY
   MEMCOMPRESS FOR CAPACITY HIGH
   PRIORITY LOW;
Enabling and Disabling Materialized Views for the IM Column Store
You enable a materialized view for the IM column store by including an INMEMORY clause in a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement. You disable a materialized view for the IM column store by including a NO INMEMORY clause in a CREATE MATERIALIZED VIEW orALTER MATERIALIZED VIEW statement.

To enable or disable a materialized view for the IM column store, complete the following steps:

Ensure that the IM column store is enabled for the database.

See "Enabling the IM Column Store for a Database".

Connect to the database instance as a user with the appropriate privileges to either create the materialized view or alter the materialized view.

Run a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement with an INMEMORY clause or a NO INMEMORY clause.

Example 6-10 Creating a Materialized View and Enabling It for the IM Column Store

The following example creates the oe.prod_info_mv materialized view and enables it for the IM column store:

CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY
  AS SELECT * FROM oe.product_information;
This example uses the defaults for the INMEMORY clause. Therefore, MEMCOMPRESS FOR QUERY is used, and PRIORITY NONE is used.

Example 6-11 Enabling a Materialized View for the IM Column Store with HIGH Data Population Priority

The following example enables the oe.prod_info_mv materialized view for the IM column store:

ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;
See Also:
Oracle Database SQL Language Reference for information about the CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEWstatements
列存用在导入导出，列存+压缩
Data Pump and the IM Column Store
You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:y option of the impdp command. With this option, Data Pump keeps the IM column store clause for all objects that have one. When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops the IM column store clause from all objects that have one.

You can also use the TRANSFORM=INMEMORY_CLAUSE:string option to override the IM column store clause for a database object in the dump file during import. For example, you can use this option to change the IM column store compression for a database object being imported.

Video:
Oracle Database 12c demos: In-Memory Column Store Monitored and Oracle Data Pump
See Also:
Oracle Database Utilities for more information about the TRANSFORM impdp parameter
Using IM Column Store In Enterprise Manager
The following sections discuss how to use the IM column store in Enterprise Manager Cloud Control. This section contains the following topics:

Prerequisites to Using IM Column Store in Enterprise Manager

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

Specifying In-Memory Details When Creating a Table or Partition

Viewing or Editing IM Column Store Details of a Table

Viewing or Editing IM Column Store Details of a Partition

Specifying IM Column Store Details During Tablespace Creation

Viewing and Editing IM Column Store Details of a Tablespace

Specifying IM Column Store Details During Materialized View Creation

Viewing or Editing IM Column Store Details of a Materialized View

Video:
Oracle Database 12c demos: In-Memory Column Store Monitored with Enterprise Manager
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:

设置兼容性水品的步骤：

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.

Search for the parameter COMPATIBLE.

The category for the parameter is Miscellaneous.

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:

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

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

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:

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

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.

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

Optionally, you can click on the Partitions tab.

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.

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:

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

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

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.

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:

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

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

Click Edit to launch the Edit Table page.

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

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

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

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:

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

Click Create to create a tablespace.

Enter the details that appear on the General tab.

Click the In-Memory Column Store tab.

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.

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:

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

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

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

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:

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

Click Create to create a materialized view.

Enter the materialized view name, and specify its query.

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

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.

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:

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

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

Click Edit to launch the Edit Materialized View page.

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

Edit the required details, and click Apply.

Memory Management Reference
This section contains the following reference topics for memory management:

Platforms That Support Automatic Memory Management
Memory Management Data Dictionary Views
Platforms That Support Automatic Memory Management
The following platforms support automatic memory management—the Oracle Database ability to automatically tune the sizes of the SGA and PGA, redistributing memory from one to the other on demand to optimize performance:

Linux
Solaris
Windows
HP-UX
AIX
Memory Management Data Dictionary Views
The following dynamic performance views provide information on memory management:

View        Description
V$SGA        Displays summary information about the system global area (SGA).
V$SGAINFO        Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT        Displays detailed information about how memory is allocated within the shared pool, large pool, Java pool, and Streams pool.
V$PGASTAT        Displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, whenPGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
V$MEMORY_DYNAMIC_COMPONENTS        Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.
V$SGA_DYNAMIC_COMPONENTS        Displays the current sizes of all SGA components, and the last operation for each component.
V$SGA_DYNAMIC_FREE_MEMORY        Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$MEMORY_CURRENT_RESIZE_OPS        Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component.
V$SGA_CURRENT_RESIZE_OPS        Displays information about dynamic SGA component resize operations that are currently in progress.
V$MEMORY_RESIZE_OPS        Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations forSGA_TARGET and PGA_AGGREGATE_TARGET.
V$SGA_RESIZE_OPS        Displays information about the last 800 completed SGA component resize operations.
V$MEMORY_TARGET_ADVICE        Displays information that helps you tune MEMORY_TARGET if you enabled automatic memory management.
V$SGA_TARGET_ADVICE        Displays information that helps you tune SGA_TARGET.
V$PGA_TARGET_ADVICE        Displays information that helps you tune PGA_AGGREGATE_TARGET.
V$IM_SEGMENTS        Displays information about the storage allocated for all segments in the IM column store.
Note: This view is available starting with Oracle Database 12c Release 1 (12.1.0.2).


See Also:
Oracle Database Reference for detailed information on memory management views.

花钱的kevin 发表于 2016-3-21 23:27
谢谢波神分享！

谢谢波神分享！

感谢分享！
很感谢！
感谢！
重要的事情说三遍！