ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 12c In-Memory Column Store

12c In-Memory Column Store

原创 Oracle 作者：哎呀我的天呐 时间：2015-10-27 14:50:32 0 删除 编辑

Database Administrator's Guide

http://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN00207

6  Managing Memory

This chapter contains the following topics:

  • About Memory Management

  • Memory Architecture Overview

  • Using Automatic Memory Management

  • Configuring Memory Manually

  • Using Force Full Database Caching Mode

  • Configuring Database Smart Flash Cache

  • Using the In-Memory Column Store

  • Memory Management Reference

    Using the In-Memory Column Store

    Note:

    This feature is available starting with Oracle Database 12 c  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的可选部分，表的镜像，分区表，和其他数据库对象。在inmemory区中，数据是列示存储而不是行，这就是为了快速查询而优化的部分。

    This section contains the following topics:

    See Also:

    Oracle Database Concepts

    About the IM Column Store

    This section contains the following topics:

    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.
    翻译： IM的列存储区在SGA中一个新的静态区域。在IM存储中的数据不存在于传统的行格式，而不是列式存储。 每一列作为一个单独的结构存储。即时通讯栏存储不替换缓冲区缓存，IM不是代替buffer cache而是它的补充 ，因此数据可以被存储在内存中的行和列式格式。想要使用inmemory option 必须设置inmemory_size参数必须设置为非零值。

    Video:

    Oracle Database 12c demos: In-Memory Column Store Architecture Overview

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

    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.
    翻译： 如果是在表空间级启用，然后所有的表和视图的表空间为我列存储默认情况下启用。您可以在“即时通讯”栏存储或数据库对象的列的一个子集中填充所有数据库对象的列。同样，对于一个分区表或物化视图，您可以在“即时通讯”栏存储或分区的一个子集中填充所有分区。

    Video:

    Oracle Database 12c demos: In-Memory Column Store Columns

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

    Video:

    Oracle Database 12c demos: In-Memory Column Store Queries

    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:

    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;

    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:

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

    See Also:

    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 thanMEMCOMPRESS 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 LOW or HIGH.

    MEMCOMPRESS FOR QUERY HIGH

    This method results in excellent query performance.

    This method compresses IM column store data more thanMEMCOMPRESS 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 thanMEMCOMPRESS 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 theINMEMORY clause.

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

    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, or HIGH.


    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.

    Note:

    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 theINMEMORY or 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 INMEMORY has 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 toFALSE causes the optimizer to ignore the in-memory property of tables during the optimization of SQL statements.


    See Also:

    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.

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

    See Also:

    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:

    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

    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:

    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.

    See Also:

    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 theINMEMORY clause. You can also alter a tablespace to enable it for the IM column store with an ALTER TABLESPACE statement that includes theINMEMORY 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 toPRIORITY 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:

    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 or ALTER MATERIALIZED VIEW statement.

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

    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:

    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:

    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:

    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:

    Viewing or Editing IM Column Store Details of a Table

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

    Viewing or Editing IM Column Store Details of a Partition

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

    Specifying IM Column Store Details During Tablespace Creation

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

    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:

    Specifying IM Column Store Details During Materialized View Creation

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

    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:

    Memory Management Reference

    This section contains the following reference topics for memory management:

    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:

    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$PGASTATare 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 for SGA_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 12 c Release 1 (12.1.0.2).


    See Also:

    Oracle Database Reference  for detailed information on memory management views.

    Previous Page

    Page 13 of 61


    没写完后续继续

    • CREATE TABLE

    • ALTER TABLE

    • CREATE TABLESPACE

    • ALTER TABLESPACE

    • CREATE MATERIALIZED VIEW

    • ALTER MATERIALIZED VIEW

    • 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

    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 .

    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.

    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 .

    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.

    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 .

    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 .

    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.

    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.

    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.

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

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

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

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

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

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

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

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

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

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

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

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

      See Also:

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

    2. 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 specifySCOPE=SPFILE.

      The minimum setting is 100M.

    3. Restart the database.

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

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

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29990276/viewspace-1816558/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： create objects inmemory options
下一篇： MySQL slow log + pt_query_digest
请登录后发表评论 登录
全部评论
哎呀我的天呐
专家姓名：姚崇
https://cloud.tencent.com/developer/inventory/14362

注册时间：2014-10-30

  • 博文量
    403
  • 访问量
    2528743

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593