Chapter I Oracle database in memory related concepts (Continued) (im-1.2)

1 Oracle Database In-Memory Relevant concepts

Follow up ： Chapter one Oracle Database In-Memory Relevant concepts (IM-1.1)

Improve the performance of mixed workloads

although OLTP The application cannot start from IM Access data in column store , But dual memory （dual-memory） The format can indirectly improve OLTP performance .

When all data is stored in rows （rows） In the middle of the day , To improve the performance of analysis query, you need to create an access structure . The standard method is to create an analytical index 、 Materialized views and OLAP Cube . for example , Tables may need 3 An index to improve OLTP Application performance （1 Primary keys and 2 A foreign key index ） and 10-20 An additional index , To improve the performance of analysis query . Although this technology can improve the performance of analysis and query , But it will reduce OLTP performance . Inserting rows into a table requires modifying all indexes on the table . As the number of indexes increases , Insertion speed decreases .

Fill data into IM When the column is stored , You can delete the analysis access structure . This technology reduces storage space and processing overhead , Because fewer indexes are needed 、 Materialized views and OLAP Cube . for example , insert data , Will result in modifications 1-3 An index instead of 11-23 An index .

although IM Column storage can significantly improve business applications 、 Performance of special analysis queries and analysis queries in data warehouse workloads , But use the index to find pure data that performs short transactions OLTP Databases benefit less . IM Column storage does not improve the performance of the following types of queries ：

• Queries with complex predicates
• Query for selecting a large number of columns
• A query that returns a large number of rows

High Availability Support

IM Column storage is fully integrated into Oracle In the database , High availability support for all functions .

The column format will not change Oracle Database disk storage format . therefore , The buffer cache modification and redo log functions work in the same way . Fully support RMAN,Oracle Data Guard and Oracle ASM And so on .

stay Oracle Real Application Clusters（Oracle RAC） Environment , By default , Each node has its own IM Column store . According to your request , You can fill objects in different ways ：

• Fill in different tables on each node . for example ,sales The entity table is located on a node , and products Dimension tables are located on different nodes .
• A single table is distributed between different nodes . for example , Same hash partition （hash-partitioned） Different partitions of the table are on different nodes , Or a single non partitioned table rowid The range is on different nodes .
• Some objects appear on each node IM Column storage . for example , You can populate each node with products Dimension table , But distributed among different nodes sales Partition of entity table .

Easy to use

Database In-Memory Easy to implement , And there is no need to change the application .

Database In-Memory Key aspects of adoption include ：

• Easy to deploy Data migration without user management . The database stores data in row format on disk , And fill in IM Automatically convert row data to column format when storing columns .
• Compatibility with existing applications There is no need to change the application . The optimizer automatically utilizes column formatting . If the application connects to the database and issues SQL, Then it can come from Database In-Memory Benefit from function .
• Completely SQL Compatibility Database In-Memory Yes SQL No restrictions . Analyzing queries can benefit , Whatever they use Oracle The analysis function is customized PL / SQL Code .
• Easy to use No complicated setup required . INMEMORY_SIZE The initialization parameter specifies that it is reserved for use IM Amount of memory used by column storage . DDL Statement INMEMORY Clause specifies to populate to IM An object or column in a column store . By configuring IM Column store , You can immediately improve the performance of existing analysis workloads and ad hoc queries .

Database In-Memory prerequisites

all Database In-Memory All functions need Oracle Database In-Memory Options . IM Column storage does not require special hardware .

Prerequisites include ：

• IM Column storage requires at least 100 MB Of memory . The storage size is contained in MEMORY_TARGET in . Please see the “ assessment IM The size necessary for column storage ”.
• about Oracle RAC database , DUPLICATE and DUPLICATE ALL Option required Oracle Engineered Systems. Please see the “ stay Oracle RAC Deployment in China IM Column store ”.

Database In-Memory Main tasks of

For the benefit of IM Queries stored in columns , The only task required is to specify IM The size of the column store , And specify the objects and columns to fill . Query optimization and availability features require additional configuration .

To configure IM The main task of column storage

The main task is ：

• By designation IM Column size to enable IM Column store . Please see the “ Enable... For the database IM Column store ”.
• Specifies to fill to IM Table in column store 、 Column 、 Materialized or materialized view . Please see the “ by In-Memory Fill enabled objects ”.
• （ Optional ） You can create automatic data optimization （ADO） Strategy , In order to be in IM Set... On objects in the column store INMEMORY attribute . for example , Policies can be accessed without 10 From IM Column storage sales surface . In the memory ADO The function needs to initialize parameter settings HEAT_MAP=ON , INMEMORY_SIZE Set to a non-zero value . Please see the “ by IM Column storage enabled ADO”.

The main task of optimizing in memory queries

In-memory Query optimization does not require IM Column storage works . The following optimization tasks are optional ：

• Use DBMS_INMEMORY_ADMIN Package management IM Column storage IM Automatic detection of expressions . for example , call IME_CAPTURE_EXPRESSIONS The procedure to define the database can identify “ heat ” expression , Then gradually fill them . INMEMORY_EXPRESSIONS_USAGE Initialization parameters control what the database can fill IM The type of expression ： static state 、 Dynamic or both . Please see the “INMEMORY_EXPRESSIONS_USAGE”.
• Use CREATE INMEMORY JOIN GROUP Statement defines the connection group （join groups）. Candidates are columns that are often paired in join predicates , for example , Columns connecting real table and dimension table . Please see the “ Create connection group （Join Groups）”.
• If a particular query block requires , Please specify VECTOR_TRANSFORM hint To enable in memory aggregation , perhaps NO_VECTOR_TRANSFORM Disable it . in-memory Aggregation is an automatically enabled feature , Cannot use initialization parameters or DDL Control . Please see the “IM Control of aggregation ”.
• By setting initialization parameters INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT, Limit the passage through the trickle within a two minute interval （trickle） Refill the updated IMCU Number . You can set this initialization parameter to 0 To disable trickle （trickle） Refill . Please see the “ Based on threshold and trickle （Trickle） Refill ”.

The main task of managing availability

The main task is ：

• Use DBMS_INMEMORY_ADMIN.ENABLE_FASTSTART Procedure specifies a quick start in memory （IM FastStart） Table space . IM FastStart Optimize when restarting the database IM The number of database objects in the column store . IM FastStart Store information on disk , To fill... Faster IM Column store . Please see the “ by IM Column storage enabled IM FastStart”.
• For objects or tablespaces , stay DDL Use in statement DISTRIBUTE or DUPLICATE Keyword assignment INMEMORY To control Oracle RAC Data distribution in . By default , Each memory object is distributed in Oracle RAC Between instances , Effectively adopt columnless sharing architecture for IM Column store . Please see the “ stay Oracle RAC Deployment in China IM Column store ”.
• stay Oracle Data Guard Environment , You can use the same... On the primary or standby database Database In-Memory Initialization parameters and statements . for example , Can be set by INMEMORY_SIZE Enable... On the primary and secondary databases IM Column store . （ Optional ） Use DDL Medium INMEMORY DISTRIBUTE FOR SERVICE Clause to fill in the of the primary database and the standby database IM Different datasets in column store . Please see the “ About In-Memory Population”.

IM Tools for column storage

management IM Column storage or other Database In-Memory Features do not require special tools or utilities . Fully support such as SQL * Plus,SQL Developer and Oracle Enterprise manager （ Enterprise manager ） Management tools like that .

This section describes specific Database In-Memory Functional support tools ：

• In-Memory Advisor In-Memory Advisor The program is a downloadable PL / SQL Package , Used to analyze the analysis processing workload in the database . This advisory procedure recommends IM The size of the column store and the list of objects that will benefit from filling in memory .
• IM Column storage Cloud Control Pages Enterprise Manager Cloud Control (Cloud Control) Provides In-Memory Column storage center home page . This page provides IM Dashboard interface for column storage .
• Oracle Compression（ Compress ） Advisor Oracle Compression Advisor It is estimated that you can use MEMCOMPRESS The compression rate achieved by clause . The consultant program uses DBMS_COMPRESSION Interface .
• Oracle Data pump and IM Column store You can use impdp Ordered TRANSFORM=INMEMORY:y Options are imported as IM Columns store enabled database objects .

In-Memory Advisor

In-Memory Advisor The program is a downloadable PL / SQL Package , Used to analyze the analysis processing workload in the database . This advisory procedure recommends IM The size of the column store and the list of objects that will benefit from filling in memory .

In-Memory Advisor according to SQL Plan base 、 Active conversation history （ASH）、 Parallel queries use and other statistics to distinguish analytical processing from other database activities . In-Memory Advisor The program estimates based on statistics and heuristic compression factors IM The size of the object in the column store .

In-Memory Advisor Estimate and analyze the processing performance improvement factors according to the following contents ：

• Eliminate waiting Events , For example, users I / O wait for 、 Cluster transmission wait and buffer cache latch wait
• Query processing advantages associated with a particular compression type
• Decompression cost heuristic algorithm for specific compression type
• SQL Plan base 、 The number of columns in the result set, etc

In-Memory Advisor The output of the program is a report containing recommendations . The consultant program also generates a SQL * Plus Script , adopt INMEMORY Clause to change the recommended object .

In-Memory Advisor The program is not included in the stored PL / SQL In bag . You have to start from Oracle Support Download .

IM Column storage Cloud Control Pages

Enterprise Manager Cloud Control (Cloud Control) Provides In-Memory Column storage center home page . This page provides IM Dashboard interface for column storage .

Use this page to monitor database objects （ Such as table 、 Indexes 、 Partitions and tablespaces ） In memory support . You can view the of the object In-Memory Function and monitor its memory usage statistics . Unless otherwise stated , This manual introduces Database In-Memory Command line interface of function .

Oracle Compression（ Compress ） Advisor

Oracle Compression Advisor It is estimated that you can use MEMCOMPRESS The compression rate achieved by clause . The consultant program uses DBMS_COMPRESSION Interface .

Oracle Data pump and IM Column store

You can use impdp Ordered TRANSFORM=INMEMORY:y Options are imported as IM Columns store enabled database objects .

Use this option ,Oracle Data pump （Data Pump） Will be reserved for all objects with one IM Column storage clause . When specifying TRANSFORM=INMEMORY:n Option ,Data Pump Will be deleted from all objects that contain a IM Column storage clause .

You can also use... During import TRANSFORM=INMEMORY_CLAUSE:string Options , Overwrite database objects in dump file IM Column storage clause . for example , You can use this option to change the name of the imported database object IM Column storage compression .

（ This chapter ends , Chapter two ：IM Column storage architecture （IM-2））

Shandong Oracle User group (Shandong Oracle User Group), abbreviation ：SDOUG, Is a full of vitality 、 Young non-profit organizations , It aims to provide an exchange platform for technology lovers in Jinan and surrounding areas .SDOUG Organize offline technology sharing activities from time to time , Promote local and surrounding IT Technological development 、 Help technology enthusiasts improve themselves . Share technology 、 Share happiness ,SDOUG On the road .