Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Related Documentation

Documentation Accessibility

What's New in Oracle Database?

Oracle Database 10g Release 1 (10.1) New Features in Data Warehousing

Part I Concepts

1 Data Warehousing Concepts

What is a Data Warehouse?

Subject Oriented

Contrasting OLTP and Data Warehousing Environments

Data Warehouse Architectures

Data Warehouse Architecture (Basic)

Data Warehouse Architecture (with a Staging Area)

Data Warehouse Architecture (with a Staging Area and Data Marts)

Part II Logical Design

2 Logical Design in Data Warehouses

Logical Versus Physical Design in Data Warehouses

Creating a Logical Design

Data Warehousing Schemas

Data Warehousing Objects

Creating a New Fact Table

Dimension Tables

Typical Dimension Hierarchy

Unique Identifiers

Example of Data Warehousing Objects and Their Relationships

Part III Physical Design

3 Physical Design in Data Warehouses

Moving from Logical to Physical Design

Physical Design

Physical Design Structures

Tables and Partitioned Tables

Table Compression

Integrity Constraints

Indexes and Partitioned Indexes

Materialized Views

4 Hardware and I/O Considerations in Data Warehouses

Overview of Hardware and I/O Considerations in Data Warehouses

Configure I/O for Bandwidth not Capacity

Stripe Far and Wide

Test the I/O System Before Building the Database

Plan for Growth

Storage Management

5 Parallelism and Partitioning in Data Warehouses

Overview of Parallel Execution

When to Implement Parallel Execution

Granules of Parallelism

Block Range Granules

Partition Granules

Partitioning Design Considerations

Types of Partitioning

Partitioning Methods

Index Partitioning

Performance Issues for Range, List, Hash, and Composite Partitioning

Partitioning and Table Compression

Table Compression and Bitmap Indexes

Example of Table Compression and Partitioning

Partition Pruning

Pruning Using DATE Columns

Avoiding I/O Bottlenecks

Partition-Wise Joins

Full Partition-Wise Joins

Partial Partition-wise Joins

Benefits of Partition-Wise Joins

Performance Considerations for Parallel Partition-Wise Joins

Partitioning and Subpartitioning Columns and Keys

Partition Bounds for Range Partitioning

Comparing Partitioning Keys with Partition Bounds

Multicolumn Partitioning Keys

Implicit Constraints Imposed by Partition Bounds

Index Partitioning

Local Partitioned Indexes

Global Partitioned Indexes

Summary of Partitioned Index Types

The Importance of Nonprefixed Indexes

Performance Implications of Prefixed and Nonprefixed Indexes

Guidelines for Partitioning Indexes

Physical Attributes of Index Partitions

6 Indexes

Using Bitmap Indexes in Data Warehouses

Benefits for Data Warehousing Applications

Bitmap Indexes and Nulls

Bitmap Indexes on Partitioned Tables

Using Bitmap Join Indexes in Data Warehouses

Four Join Models for Bitmap Join Indexes

Bitmap Join Index Restrictions and Requirements

Using B-Tree Indexes in Data Warehouses

Using Index Compression

Choosing Between Local Indexes and Global Indexes

7 Integrity Constraints

Why Integrity Constraints are Useful in a Data Warehouse

Overview of Constraint States

Typical Data Warehouse Integrity Constraints

UNIQUE Constraints in a Data Warehouse

FOREIGN KEY Constraints in a Data Warehouse

RELY Constraints

Integrity Constraints and Parallelism

Integrity Constraints and Partitioning

View Constraints

8 Basic Materialized Views

Overview of Data Warehousing with Materialized Views

Materialized Views for Data Warehouses

Materialized Views for Distributed Computing

Materialized Views for Mobile Computing

The Need for Materialized Views

Components of Summary Management

Data Warehousing Terminology

Materialized View Schema Design

Schemas and Dimension Tables

Materialized View Schema Design Guidelines

Loading Data into Data Warehouses

Overview of Materialized View Management Tasks

Types of Materialized Views

Materialized Views with Aggregates

Requirements for Using Materialized Views with Aggregates

Materialized Views Containing Only Joins

Materialized Join Views FROM Clause Considerations

Nested Materialized Views

Why Use Nested Materialized Views?

Nesting Materialized Views with Joins and Aggregates

Nested Materialized View Usage Guidelines

Restrictions When Using Nested Materialized Views

Creating Materialized Views

Creating Materialized Views with Column Alias Lists

Naming Materialized Views

Storage And Table Compression

Enabling Query Rewrite

Query Rewrite Restrictions

Materialized View Restrictions

General Query Rewrite Restrictions

Refresh Options

General Restrictions on Fast Refresh

Restrictions on Fast Refresh on Materialized Views with Joins Only

Restrictions on Fast Refresh on Materialized Views with Aggregates

Restrictions on Fast Refresh on Materialized Views with UNION ALL

Achieving Refresh Goals

Refreshing Nested Materialized Views

ORDER BY Clause

Materialized View Logs

Using the FORCE Option with Materialized View Logs

Using Oracle Enterprise Manager

Using Materialized Views with NLS Parameters

Adding Comments to Materialized Views

Registering Existing Materialized Views

Choosing Indexes for Materialized Views

Dropping Materialized Views

Analyzing Materialized View Capabilities

Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure

DBMS_MVIEW.EXPLAIN_MVIEW Declarations

Using MV_CAPABILITIES_TABLE

MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details

MV_CAPABILITIES_TABLE Column Details

9 Advanced Materialized Views

Partitioning and Materialized Views

Partition Change Tracking

Join Dependent Expression

Partition Marker

Partial Rewrite

Partitioning a Materialized View

Partitioning a Prebuilt Table

Benefits of Partitioning a Materialized View

Rolling Materialized Views

Materialized Views in OLAP Environments

Partitioning Materialized Views for OLAP

Compressing Materialized Views for OLAP

Materialized Views with Set Operators

Examples of Materialized Views Using UNION ALL

Materialized Views and Models

Invalidating Materialized Views

Security Issues with Materialized Views

Querying Materialized Views with Virtual Private Database

Using Query Rewrite with Virtual Private Database

Restrictions with Materialized Views and Virtual Private Database

Altering Materialized Views

10 Dimensions

What are Dimensions?

Creating Dimensions

Dropping and Creating Attributes with Columns

Multiple Hierarchies

Using Normalized Dimension Tables

Viewing Dimensions

Using Oracle Enterprise Manager

Using the DESCRIBE_DIMENSION Procedure

Using Dimensions with Constraints

Validating Dimensions

Altering Dimensions

Deleting Dimensions

Part IV Managing the Data Warehouse Environment

11 Overview of Extraction, Transformation, and Loading

Overview of ETL in Data Warehouses

ETL Tools for Data Warehouses

Daily Operations in Data Warehouses

Evolution of the Data Warehouse

12 Extraction in Data Warehouses

Overview of Extraction in Data Warehouses

Introduction to Extraction Methods in Data Warehouses

Logical Extraction Methods

Full Extraction

Incremental Extraction

Physical Extraction Methods

Online Extraction

Offline Extraction

Change Data Capture

Data Warehousing Extraction Examples

Extraction Using Data Files

Extracting into Flat Files Using SQL*Plus

Extracting into Flat Files Using OCI or Pro*C Programs

Exporting into Export Files Using the Export Utility

Extracting into Export Files Using External Tables

Extraction Through Distributed Operations

13 Transportation in Data Warehouses

Overview of Transportation in Data Warehouses

Introduction to Transportation Mechanisms in Data Warehouses

Transportation Using Flat Files

Transportation Through Distributed Operations

Transportation Using Transportable Tablespaces

Transportable Tablespaces Example

Other Uses of Transportable Tablespaces

14 Loading and Transformation

Overview of Loading and Transformation in Data Warehouses

Transformation Flow

Multistage Data Transformation

Pipelined Data Transformation

Loading Mechanisms

Loading a Data Warehouse with SQL*Loader

Loading a Data Warehouse with External Tables

Loading a Data Warehouse with OCI and Direct-Path APIs

Loading a Data Warehouse with Export/Import

Transformation Mechanisms

Transformation Using SQL

CREATE TABLE ... AS SELECT And INSERT /*+APPEND*/ AS SELECT

Transformation Using UPDATE

Transformation Using MERGE

Transformation Using Multitable INSERT

Transformation Using PL/SQL

Transformation Using Table Functions

What is a Table Function?

Loading and Transformation Scenarios

Key Lookup Scenario

Exception Handling Scenario

Pivoting Scenarios

15 Maintaining the Data Warehouse

Using Partitioning to Improve Data Warehouse Refresh

Refresh Scenarios

Scenarios for Using Partitioning for Refreshing Data Warehouses

Refresh Scenario 1

Refresh Scenario 2

Optimizing DML Operations During Refresh

Implementing an Efficient MERGE Operation

Maintaining Referential Integrity

Refreshing Materialized Views

Complete Refresh

Partition Change Tracking (PCT) Refresh

ON COMMIT Refresh

Manual Refresh Using the DBMS_MVIEW Package

Refresh Specific Materialized Views with REFRESH

Refresh All Materialized Views with REFRESH_ALL_MVIEWS

Refresh Dependent Materialized Views with REFRESH_DEPENDENT

Using Job Queues for Refresh

When Fast Refresh is Possible

Recommended Initialization Parameters for Parallelism

Monitoring a Refresh

Checking the Status of a Materialized View

Scheduling Refresh

Tips for Refreshing Materialized Views with Aggregates

Tips for Refreshing Materialized Views Without Aggregates

Tips for Refreshing Nested Materialized Views

Tips for Fast Refresh with UNION ALL

Tips After Refreshing Materialized Views

Using Materialized Views with Partitioned Tables

Fast Refresh with Partition Change Tracking

PCT Fast Refresh Scenario 1

PCT Fast Refresh Scenario 2

PCT Fast Refresh Scenario 3

Fast Refresh with CONSIDER FRESH

16 Change Data Capture

Overview of Change Data Capture

Capturing Change Data Without Change Data Capture

Capturing Change Data with Change Data Capture

Publish and Subscribe Model

Change Sources and Modes of Data Capture

Valid Combinations of Change Sources and Change Sets

Getting Information About the Change Data Capture Environment

Preparing to Publish Change Data

Creating a User to Serve As a Publisher

Granting Privileges and Roles to the Publisher

Creating a Default Tablespace for the Publisher

Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter

Determining the Mode in Which to Capture Data

Setting Initialization Parameters for Change Data Capture Publishing

Initialization Parameters for Synchronous Publishing

Initialization Parameters for Asynchronous HotLog Publishing

Initialization Parameters for Asynchronous AutoLog Publishing

Determining the Current Setting of an Initialization Parameter

Retaining Initialization Parameter Values When a Database Is Restarted

Adjusting Initialization Parameter Values When Oracle Streams Values Change

Publishing Change Data

Performing Synchronous Publishing

Performing Asynchronous HotLog Publishing

Performing Asynchronous AutoLog Publishing

Subscribing to Change Data

Considerations for Asynchronous Change Data Capture

Asynchronous Change Data Capture and Redo Log Files

Asynchronous Change Data Capture and Supplemental Logging

Datatypes and Table Structures Supported for Asynchronous Change Data Capture

Managing Published Data

Managing Asynchronous Change Sets

Creating Asynchronous Change Sets with Starting and Ending Dates

Enabling and Disabling Asynchronous Change Sets

Stopping Capture on DDL for Asynchronous Change Sets

Recovering from Errors Returned on Asynchronous Change Sets

Managing Change Tables

Creating Change Tables

Understanding Change Table Control Columns

Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values

Controlling Subscriber Access to Change Tables

Purging Change Tables of Unneeded Data

Dropping Change Tables

Considerations for Exporting and Importing Change Data Capture Objects

Impact on Subscriptions When the Publisher Makes Changes

Implementation and System Configuration

Synchronous Change Data Capture Restriction on Direct-Path INSERT

17 SQLAccess Advisor

Overview of the SQLAccess Advisor in the DBMS_ADVISOR Package

Overview of Using the SQLAccess Advisor

SQLAccess Advisor Repository

Using the SQLAccess Advisor

SQLAccess Advisor Flowchart

SQLAccess Advisor Privileges

SQLAccess Advisor Templates

Creating Templates

Workload Objects

Managing Workloads

Linking a Task and a Workload

Defining the Contents of a Workload

Loading a User-Defined Workload

Loading a SQL Cache Workload

Using a Hypothetical Workload

Using a Summary Advisor 9i Workload

SQLAccess Advisor Workload Parameters

SQL Workload Journal

Adding SQL Statements to a Workload

Deleting SQL Statements from a Workload

Changing SQL Statements in a Workload

Maintaining Workloads

Setting Workload Attributes

Resetting Workloads

Removing a Link Between a Workload and a Task

Removing Workloads

Recommendation Options

Generating Recommendations

EXECUTE_TASK Procedure

Viewing the Recommendations

Access Advisor Journal

Stopping the Recommendation Process

Canceling Tasks

Marking Recommendations

Modifying Recommendations

Generating SQL Scripts

When Recommendations are No Longer Required

Performing a Quick Tune

Updating Task Attributes

Setting DAYS_TO_EXPIRE

Using SQLAccess Advisor Constants

Examples of Using the SQLAccess Advisor

Recommendations From a User-Defined Workload

Generate Recommendations Using a Task Template

Filter a Workload from the SQL Cache

Evaluate Current Usage of Indexes and Materialized Views

Tuning Materialized Views for Fast Refresh and Query Rewrite

DBMS_ADVISOR.TUNE_MVIEW Procedure

TUNE_MVIEW Syntax and Operations

Accessing TUNE_MVIEW Output Results

USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views

Script Generation DBMS_ADVISOR Function and Procedure

Fast Refreshable with Optimized Sub-Materialized View

Part V Data Warehouse Performance

18 Query Rewrite

Overview of Query Rewrite

Cost-Based Rewrite

When Does Oracle Rewrite a Query?

Enabling Query Rewrite

Initialization Parameters for Query Rewrite

Controlling Query Rewrite

Accuracy of Query Rewrite

Query Rewrite Hints

Privileges for Enabling Query Rewrite

Sample Schema and Materialized Views

How Oracle Rewrites Queries

Text Match Rewrite Methods

Text Match Capabilities

General Query Rewrite Methods

When are Constraints and Dimensions Needed?

Rollup Using a Dimension

Compute Aggregates

Filtering the Data

Dropping Selections in the Rewritten Query

Handling of HAVING Clause in Query Rewrite

Handling Expressions in Query Rewrite

Handling IN-Lists in Query Rewrite

Checks Made by Query Rewrite

Join Compatibility Check

Data Sufficiency Check

Grouping Compatibility Check

Aggregate Computability Check

Other Cases for Query Rewrite

Query Rewrite Using Partially Stale Materialized Views

Query Rewrite Using Nested Materialized Views

Query Rewrite When Using GROUP BY Extensions

Hint for Queries with Extended GROUP BY

Query Rewrite with Inline Views

Query Rewrite with Selfjoins

Query Rewrite and View Constraints

Query Rewrite and Expression Matching

Date Folding Rewrite

Partition Change Tracking (PCT) Rewrite

PCT Rewrite Based on LIST Partitioned Tables

PCT and PMARKER

PCT Rewrite with Materialized Views Based on Range-List Partitioned Tables

PCT Rewrite Using Rowid as Pmarker

Query Rewrite and Bind Variables

Query Rewrite Using Set Operator Materialized Views

UNION ALL Marker

Did Query Rewrite Occur?

DBMS_MVIEW.EXPLAIN_REWRITE Procedure

DBMS_MVIEW.EXPLAIN_REWRITE Syntax

Using REWRITE_TABLE

EXPLAIN_REWRITE Benefit Statistics

Support for Query Text Larger than 32KB in EXPLAIN_REWRITE

Design Considerations for Improving Query Rewrite Capabilities

Query Rewrite Considerations: Constraints

Query Rewrite Considerations: Dimensions

Query Rewrite Considerations: Outer Joins

Query Rewrite Considerations: Text Match

Query Rewrite Considerations: Aggregates

Query Rewrite Considerations: Grouping Conditions

Query Rewrite Considerations: Expression Matching

Query Rewrite Considerations: Date Folding

Query Rewrite Considerations: Statistics

Advanced Rewrite Using Equivalences

19 Schema Modeling Techniques

Schemas in Data Warehouses

Third Normal Form

Optimizing Third Normal Form Queries

Snowflake Schemas

Optimizing Star Queries

Tuning Star Queries

Using Star Transformation

Star Transformation with a Bitmap Index

Execution Plan for a Star Transformation with a Bitmap Index

Star Transformation with a Bitmap Join Index

Execution Plan for a Star Transformation with a Bitmap Join Index

How Oracle Chooses to Use Star Transformation

Star Transformation Restrictions

20 SQL for Aggregation in Data Warehouses

Overview of SQL for Aggregation in Data Warehouses

Analyzing Across Multiple Dimensions

Optimized Performance

An Aggregate Scenario

Interpreting NULLs in Examples

ROLLUP Extension to GROUP BY

When to Use ROLLUP

CUBE Extension to GROUP BY

When to Use CUBE

Calculating Subtotals Without CUBE

GROUPING Functions

GROUPING Function

When to Use GROUPING

GROUPING_ID Function

GROUP_ID Function

GROUPING SETS Expression

GROUPING SETS Syntax

Composite Columns

Concatenated Groupings

Concatenated Groupings and Hierarchical Data Cubes

Considerations when Using Aggregation

Hierarchy Handling in ROLLUP and CUBE

Column Capacity in ROLLUP and CUBE

HAVING Clause Used with GROUP BY Extensions

ORDER BY Clause Used with GROUP BY Extensions

Using Other Aggregate Functions with ROLLUP and CUBE

Computation Using the WITH Clause

Working with Hierarchical Cubes in SQL

Specifying Hierarchical Cubes in SQL

Querying Hierarchical Cubes in SQL

SQL for Creating Materialized Views to Store Hierarchical Cubes

Examples of Hierarchical Cube Materialized Views

21 SQL for Analysis and Reporting

Overview of SQL for Analysis and Reporting

Ranking Functions

RANK and DENSE_RANK Functions

Ranking on Multiple Expressions

RANK and DENSE_RANK Difference

Per Group Ranking

Per Cube and Rollup Group Ranking

Treatment of NULLs

Bottom N Ranking

CUME_DIST Function

PERCENT_RANK Function

ROW_NUMBER Function

Windowing Aggregate Functions

Treatment of NULLs as Input to Window Functions

Windowing Functions with Logical Offset

Centered Aggregate Function

Windowing Aggregate Functions in the Presence of Duplicates

Varying Window Size for Each Row

Windowing Aggregate Functions with Physical Offsets

FIRST_VALUE and LAST_VALUE Functions

Reporting Aggregate Functions

RATIO_TO_REPORT Function

LAG/LEAD Functions

LAG/LEAD Syntax

FIRST/LAST Functions

FIRST/LAST Syntax

FIRST/LAST As Regular Aggregates

FIRST/LAST As Reporting Aggregates

Inverse Percentile Functions

Normal Aggregate Syntax

Inverse Percentile Example Basis

As Reporting Aggregates

Inverse Percentile Restrictions

Hypothetical Rank and Distribution Functions

Hypothetical Rank and Distribution Syntax

Linear Regression Functions

REGR_COUNT Function

REGR_AVGY and REGR_AVGX Functions

REGR_SLOPE and REGR_INTERCEPT Functions

REGR_R2 Function

REGR_SXX, REGR_SYY, and REGR_SXY Functions

Linear Regression Statistics Examples

Sample Linear Regression Calculation

Frequent Itemsets

Other Statistical Functions

Descriptive Statistics

Hypothesis Testing - Parametric Tests

Crosstab Statistics

Hypothesis Testing - Non-Parametric Tests

Non-Parametric Correlation

WIDTH_BUCKET Function

WIDTH_BUCKET Syntax

User-Defined Aggregate Functions

CASE Expressions

Creating Histograms With User-Defined Buckets

Data Densification for Reporting

Partition Join Syntax

Sample of Sparse Data

Filling Gaps in Data

Filling Gaps in Two Dimensions

Filling Gaps in an Inventory Table

Computing Data Values to Fill Gaps

Time Series Calculations on Densified Data

Period-to-Period Comparison for One Time Level: Example

Period-to-Period Comparison for Multiple Time Levels: Example

Creating a Custom Member in a Dimension: Example

22 SQL for Modeling

Overview of SQL Modeling

How Data is Processed in a SQL Model

Why Use SQL Modeling?

SQL Modeling Capabilities

Basic Topics in SQL Modeling

MODEL Clause Syntax

Keywords in SQL Modeling

Assigning Values and Null Handling

Calculation Definition

Cell Referencing

Symbolic Dimension References

Positional Dimension References

Single Cell References on the Right Side

Multi-Cell References

Single Cell References

Multi-Cell References on the Right Side

Multi-Cell References on the Left Side

Use of the ANY Wildcard

Nested Cell References

Order of Evaluation of Rules

Differences Between Update and Upsert

Treatment of NULLs and Missing Cells

Distinguishing Missing Cells from NULLs

Use Defaults for Missing Cells and NULLs

Qualifying NULLs for a Dimension

Reference Models

Advanced Topics in SQL Modeling

Iterative Models

Rule Dependency in AUTOMATIC ORDER Models

Unique Dimensions Versus Unique Single References

Rules and Restrictions when Using SQL for Modeling

Performance Considerations with SQL Modeling

Parallel Execution

Aggregate Computation

Using EXPLAIN PLAN to Understand Model Queries

Using ORDERED FAST: Example

Using ORDERED: Example

Using ACYCLIC FAST: Example

Using ACYCLIC: Example

Using CYCLIC: Example

Examples of SQL Modeling

23 OLAP and Data Mining

Benefits of OLAP and RDBMS Integration

Backup and Recovery

Oracle Data Mining Overview

Enabling Data Mining Applications

Data Mining in the Database

Data Preparation

Model Evaluation

Model Apply (Scoring)

ODM Programmatic Interfaces

ODM PL/SQL Packages

ODM Sequence Similarity Search (BLAST)

24 Using Parallel Execution

Introduction to Parallel Execution Tuning

When to Implement Parallel Execution

When Not to Implement Parallel Execution

Operations That Can Be Parallelized

How Parallel Execution Works

Degree of Parallelism

The Parallel Execution Server Pool

Variations in the Number of Parallel Execution Servers

Processing Without Enough Parallel Execution Servers

How Parallel Execution Servers Communicate

Parallelizing SQL Statements

Dividing Work Among Parallel Execution Servers

Parallelism Between Operations

Producer Operations

Types of Parallelism

Parallel Queries on Index-Organized Tables

Nonpartitioned Index-Organized Tables

Partitioned Index-Organized Tables

Parallel Queries on Object Types

DDL Statements That Can Be Parallelized

CREATE TABLE ... AS SELECT in Parallel

Recoverability and Parallel DDL

Space Management for Parallel DDL

Storage Space When Using Dictionary-Managed Tablespaces

Free Space and Parallel DDL

Advantages of Parallel DML over Manual Parallelism

When to Use Parallel DML

Enabling Parallel DML

Transaction Restrictions for Parallel DML

Rollback Segments

Recovery for Parallel DML

Space Considerations for Parallel DML

Lock and Enqueue Resources for Parallel DML

Restrictions on Parallel DML

Data Integrity Restrictions

Trigger Restrictions

Distributed Transaction Restrictions

Examples of Distributed Transaction Parallelization

Parallel Execution of Functions

Functions in Parallel Queries

Functions in Parallel DML and DDL Statements

Other Types of Parallelism

Initializing and Tuning Parameters for Parallel Execution

Using Default Parameter Settings

Setting the Degree of Parallelism for Parallel Execution

How Oracle Determines the Degree of Parallelism for Operations

Hints and Degree of Parallelism

Table and Index Definitions

Default Degree of Parallelism

Adaptive Multiuser Algorithm

Minimum Number of Parallel Execution Servers

Limiting the Number of Available Instances

Balancing the Workload

Parallelization Rules for SQL Statements

Rules for Parallelizing Queries

Rules for UPDATE, MERGE, and DELETE

Rules for INSERT ... SELECT

Rules for DDL Statements

Rules for [CREATE | REBUILD] INDEX or [MOVE | SPLIT] PARTITION

Rules for CREATE TABLE AS SELECT

Summary of Parallelization Rules

Enabling Parallelism for Tables and Queries

Degree of Parallelism and Adaptive Multiuser: How They Interact

How the Adaptive Multiuser Algorithm Works

Forcing Parallel Execution for a Session

Controlling Performance with the Degree of Parallelism

Tuning General Parameters for Parallel Execution

Parameters Establishing Resource Limits for Parallel Operations

PARALLEL_MAX_SERVERS

Increasing the Number of Concurrent Users

Limiting the Number of Resources for a User

PARALLEL_MIN_SERVERS

SHARED_POOL_SIZE

Computing Additional Memory Requirements for Message Buffers

Adjusting Memory After Processing Begins

PARALLEL_MIN_PERCENT

Parameters Affecting Resource Consumption

PGA_AGGREGATE_TARGET

PARALLEL_EXECUTION_MESSAGE_SIZE

Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL

Parameters Related to I/O

DB_FILE_MULTIBLOCK_READ_COUNT

DISK_ASYNCH_IO and TAPE_ASYNCH_IO

Monitoring and Diagnosing Parallel Execution Performance

Is There Regression?

Is There a Plan Change?

Is There a Parallel Plan?

Is There a Serial Plan?

Is There Parallel Execution?

Is the Workload Evenly Distributed?

Monitoring Parallel Execution Performance with Dynamic Performance Views

V$PX_BUFFER_ADVICE

V$PX_PROCESS_SYSSTAT

V$SESSTAT and V$SYSSTAT

Monitoring Session Statistics

Monitoring System Statistics

Monitoring Operating System Statistics

Affinity and Parallel Operations

Affinity and Parallel Queries

Affinity and Parallel DML

Miscellaneous Parallel Execution Tuning Tips

Setting Buffer Cache Size for Parallel Operations

Overriding the Default Degree of Parallelism

Rewriting SQL Statements

Creating and Populating Tables in Parallel

Creating Temporary Tablespaces for Parallel Sort and Hash Join

Size of Temporary Extents

Executing Parallel SQL Statements

Using EXPLAIN PLAN to Show Parallel Operations Plans

Additional Considerations for Parallel DML

PDML and Direct-Path Restrictions

Limitation on the Degree of Parallelism

Using Local and Global Striping

Increasing INITRANS

Limitation on Available Number of Transaction Free Lists for Segments

Using Multiple Archivers

Database Writer Process (DBWn) Workload

[NO]LOGGING Clause

Creating Indexes in Parallel

Parallel DML Tips

Parallel DML Tip 1: INSERT

Parallel DML Tip 2: Direct-Path INSERT

Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDATE, and DELETE

Incremental Data Loading in Parallel

Updating the Table in Parallel

Inserting the New Rows into the Table in Parallel

Merging in Parallel

Using Hints with Query Optimization

FIRST_ROWS(n) Hint

Enabling Dynamic Sampling

Glossary

Index