1/25

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database In-Memory Guide

• Changes in Oracle Database 12c Release 2 (12.2.0.1)
  • New Features

Part I Oracle Database In-Memory Concepts

1 Introduction to Oracle Database In-Memory

• Challenges for Analytic Applications
• The Single-Format Approach
• The Oracle Database In-Memory Solution
  • What Is Database In-Memory?
  • Improved Performance for Analytic Queries
    • Improved Performance for Data Scans
    • Improved Performance for Joins
    • Improved Performance for Aggregation
  • Improved Performance for Mixed Workloads
  • High Availability Support
  • Ease of Adoption
• Prerequisites for Database In-Memory
• Principal Tasks for Database In-Memory
• Tools for the IM Column Store
  • In-Memory Advisor
  • Cloud Control Pages for the IM Column Store
  • Oracle Compression Advisor
  • Oracle Data Pump and the IM Column Store

2 In-Memory Column Store Architecture

• Dual-Format: Column and Row
  • Columnar Data in the In-Memory Area
    • Size of the In-Memory Area
    • Memory Pools in the In-Memory Area
  • Row Data in the Database Buffer Cache
• In-Memory Storage Units
  • In-Memory Compression Units (IMCUs)
    • IMCUs and Schema Objects
      • In-Memory Compression
      • IMCUs and Rows
    • Column Compression Units (CUs)
      • Structure of a CU
      • Local Dictionary
    • In-Memory Storage Indexes
  • Snapshot Metadata Units (SMUs)
    • IMCUs and SMUs
    • Transaction Journal
  • In-Memory Expression Units (IMEUs)
• Expression Statistics Store (ESS)
• In-Memory Process Architecture
  • In-Memory Coordinator Process (IMCO)
  • Space Management Worker Processes (Wnnn)
• CPU Architecture: SIMD Vector Processing

Part II Configuring the IM Column Store

3 Enabling and Sizing the IM Column Store

• Overview of Enabling the IM Column Store
• Estimating the Required Size of the IM Column Store
• Enabling the IM Column Store for a Database
• Increasing the Size of the IM Column Store Dynamically
• Disabling the IM Column Store

4 Enabling Objects for In-Memory Population

• About In-Memory Population
  • Purpose of In-Memory Population
  • How In-Memory Population Works
    • Prioritization of In-Memory Population
    • How Background Processes Populate IMCUs
  • Controls for In-Memory Population
    • The INMEMORY Subclause
    • Priority Options for In-Memory Population
    • IM Column Store Compression Methods
    • Oracle Compression Advisor
• Enabling and Disabling Tables for the IM Column Store
  • Enabling New Tables for the In-Memory Column Store
  • Enabling and Disabling Existing Tables for the IM Column Store
  • Enabling and Disabling Tables for the IM Column Store: Examples
• Enabling and Disabling Columns for In-Memory Tables
  • About IM Virtual Columns
  • Enabling IM Virtual Columns
  • Enabling a Subset of Columns for the IM Column Store: Example
  • Specifying INMEMORY Column Attributes on a NO INMEMORY Table: Example
• Enabling and Disabling Tablespaces for the IM Column Store
• Enabling and Disabling Materialized Views for the IM Column Store
• Forcing Initial Population of an In-Memory Object: Tutorial
• Enabling ADO for the IM Column Store
  • About ADO Policies and the IM Column Store
  • Purpose of ADO and the IM Column Store
  • How ADO Works with Columnar Data
    • How Heat Map Works
    • How Policy Evaluation Works
  • Controls for ADO and the IM Column Store
  • Creating an ADO Policy for the IM Column Store

Part III Optimizing In-Memory Queries

5 Optimizing Queries with In-Memory Expressions

• About IM Expressions
  • Purpose of IM Expressions
  • How IM Expressions Work
    • IM Expressions Infrastructure
    • Capture of IM Expressions
    • How the ESS Works
    • How the Database Populates IM Expressions
    • How IMEUs Relate to IMCUs
  • User Interfaces for IM Expressions
    • INMEMORY_EXPRESSIONS_USAGE
    • DBMS_INMEMORY_ADMIN and DBMS_INMEMORY
  • Basic Tasks for IM Expressions
• Configuring IM Expression Usage
• Capturing and Populating IM Expressions
• Dropping IM Expressions

6 Optimizing Joins with Join Groups

• About In-Memory Joins
• About Join Groups
• Purpose of Join Groups
• How Join Groups Work
  • How a Join Group Uses a Common Dictionary
  • How a Join Group Optimizes Scans
• Creating Join Groups
• Monitoring Join Group Usage

7 Optimizing Joins with In-Memory Aggregation

• About IM Aggregation
• Purpose of IM Aggregation
  • When IM Aggregation Is Useful
  • When IM Aggregation Is Not Beneficial
• How IM Aggregation Works
  • When the Optimizer Chooses IM Aggregation
  • Key Vector
  • Two Phases of IM Aggregation
  • IM Aggregation: Scenario
    • Sample Analytic Query of a Star Schema
    • Step 1: Key Vector and Temporary Table Creation for geography Dimension
    • Step 2: Key Vector and Temporary Table Creation for products Dimension
    • Step 3: Key Vector Query Transformation
    • Step 4: Row Filtering from Fact Table
    • Step 5: Aggregation Using an Array
    • Step 6: Join Back to Temporary Tables
• Controls for IM Aggregation
• In-Memory Aggregation: Example

8 Optimizing Repopulation of the IM Column Store

• About Repopulation of the IM Column Store
• How Data Loading Works with the IM Column Store
  • How Conventional DML Works with the IM Column Store
    • Staleness Threshold
    • Double Buffering
  • How Direct Path Loads Work with the IM Column Store
  • How a Partition Exchange Load Works with the IM Column Store
• When the Database Repopulates the IM Column Store
  • Threshold-Based and Trickle Repopulation
  • Factors Affecting Repopulation
• Controls for Repopulation of the IM Column Store
• Optimizing Trickle Repopulation: Tutorial

Part IV High Availability and the IM Column Store

9 Managing IM FastStart for the IM Column Store

• About IM FastStart
  • Purpose of IM FastStart
  • How IM FastStart Works
    • How the Database Manages the FastStart Area
    • How the Database Reads from the FastStart Area
• Enabling IM FastStart for the IM Column Store
• Retrieving the Name of the Current IM FastStart Tablespace
• Migrating the FastStart Area to a Different Tablespace
• Disabling IM FastStart for the IM Column Store

10 Deploying IM Column Stores in Oracle RAC

• Overview of Database In-Memory and Oracle RAC
  • Multiple IM Column Stores
  • Distribution and Duplication of Columnar Data in Oracle RAC
    • Distribution of Columnar Data in Oracle RAC
      • Distribution by Partition
      • Distribution by Subpartition
      • Distribution by Rowid Range
    • Duplication of Columnar Data in Oracle RAC
      • DUPLICATE Clause in Oracle RAC
      • DUPLICATE ALL Clause in Oracle RAC
      • NO DUPLICATE Clause in Oracle RAC
  • Parallelism in Oracle RAC
    • Serial and Parallel Queries in Oracle RAC
    • Auto DOP in Oracle RAC
  • FastStart Area in Oracle RAC
• Configuring In-Memory Services in Oracle RAC
  • Instance-Level Service Controls
  • Object-Level Service Controls
  • Benefits of Services for Database In-Memory in Oracle RAC
  • Configuring an In-Memory Service for a Subset of Nodes: Example

11 Deploying an IM Column Store with Oracle Active Data Guard

• About Database In-Memory and Active Data Guard
  • Purpose of IM Column Stores in Oracle Active Data Guard
    • Identical IM Column Stores in Primary and Standby Databases
    • IM Column Store in Standby Database Only
    • Different Objects in the Primary and Standby IM Column Stores
  • How IM Column Stores Work in Oracle Active Data Guard
• Configuring IM Column Stores in an Oracle Active Data Guard Environment

Part V Database In-Memory Reference

12 In-Memory Initialization Parameters

13 In-Memory Views

A Using IM Column Store In Cloud Control

• Meeting Prerequisites for Using IM Column Store in Cloud Control
• 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

Glossary

Index