1/481

Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Related Documents
• Conventions

Changes in This Release for Oracle Database VLDB and Partitioning Guide

• Changes for Very Large Databases and Partitioning in Oracle Database 12c Release 1 (12.1.0.2)
  • New Features
    • Automatic Big Table Caching
    • In-Memory Column Store
    • Force Full Database Caching Mode
    • Attribute Clustering
    • Zone Maps
    • Advanced Index Compression
    • Oracle XML DB and Domain Index Support of Hash Partitioned Tables
    • Range Partitioning for Hash Clusters
• Changes for Very Large Databases and Partitioning in Oracle Database 12c Release 1 (12.1.0.1)
  • New Features
    • Partition Maintenance Operations on Multiple Partitions
    • Heat Map
    • Automatic Data Optimization
    • In-Database Archiving and Temporal Validity
    • Concurrent Execution of Union All
    • Enhancements to Incremental Statistics
    • Parallel Statement Queuing Enhancements
    • Partial Indexes for Partitioned Tables
    • Asynchronous Global Index Maintenance for DROP and TRUNCATE PARTITION
    • Interval-Reference Partitioning
    • Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION
    • Online Move Partition

1 Introduction to Very Large Databases

• Introduction to Partitioning
• VLDB and Partitioning
• Partitioning As the Foundation for Information Lifecycle Management
• Partitioning for All Databases

2 Partitioning Concepts

• Partitioning Overview
  • Basics of Partitioning
  • Partitioning Key
  • Partitioned Tables
    • When to Partition a Table
    • When to Partition an Index
  • Partitioned Index-Organized Tables
  • System Partitioning
  • Partitioning for Information Lifecycle Management
  • Range Partitioning for Hash Clusters
  • Partitioning and LOB Data
  • Collections in XMLType and Object Data
• Benefits of Partitioning
  • Partitioning for Performance
    • Partition Pruning
    • Partition-Wise Joins
  • Partitioning for Manageability
  • Partitioning for Availability
• Partitioning Strategies
  • Single-Level Partitioning
    • Range Partitioning
    • Hash Partitioning
    • List Partitioning
  • Composite Partitioning
    • Composite Range-Range Partitioning
    • Composite Range-Hash Partitioning
    • Composite Range-List Partitioning
    • Composite List-Range Partitioning
    • Composite List-Hash Partitioning
    • Composite List-List Partitioning
    • Composite Hash-Hash Partitioning
    • Composite Hash-List Partitioning
    • Composite Hash-Range Partitioning
• Partitioning Extensions
  • Manageability Extensions
    • Interval Partitioning
    • Partition Advisor
  • Partitioning Key Extensions
    • Reference Partitioning
    • Virtual Column-Based Partitioning
• Indexing on Partitioned Tables
  • Deciding on the Type of Partitioned Index to Use
  • Local Partitioned Indexes
  • Global Partitioned Indexes
    • Global Range Partitioned Indexes
    • Global Hash Partitioned Indexes
    • Maintenance of Global Partitioned Indexes
  • Global Nonpartitioned Indexes
  • Miscellaneous Information about Creating Indexes on Partitioned Tables
  • Partial Indexes for Partitioned Tables
  • Partitioned Indexes on Composite Partitions

3 Partitioning for Availability, Manageability, and Performance

• Partition Pruning
  • Benefits of Partition Pruning
  • Information That Can Be Used for Partition Pruning
  • How to Identify Whether Partition Pruning Has Been Used
  • Static Partition Pruning
  • Dynamic Partition Pruning
    • Dynamic Pruning with Bind Variables
    • Dynamic Pruning with Subqueries
    • Dynamic Pruning with Star Transformation
    • Dynamic Pruning with Nested Loop Joins
  • Partition Pruning with Zone Maps
  • Partition Pruning Tips
    • Data Type Conversions
    • Function Calls
    • Collection Tables
• Partition-Wise Joins
  • Full Partition-Wise Joins
    • Querying a Full Partition-Wise Join
    • Full Partition-Wise Joins: Single-Level - Single-Level
    • Full Partition-Wise Joins: Composite - Single-Level
    • Full Partition-Wise Joins: Composite - Composite
  • Partial Partition-Wise Joins
    • Partial Partition-Wise Joins: Single-Level Partitioning
    • Partial Partition-Wise Joins: Composite
• Index Partitioning
  • Local Partitioned Indexes
    • Local Prefixed Indexes
    • Local Nonprefixed Indexes
  • Global Partitioned Indexes
    • Prefixed and Nonprefixed Global Partitioned Indexes
    • Management of Global Partitioned Indexes
  • Summary of Partitioned Index Types
  • The Importance of Nonprefixed Indexes
  • Performance Implications of Prefixed and Nonprefixed Indexes
  • Advanced Index Compression With Partitioned Indexes
  • Guidelines for Partitioning Indexes
  • Physical Attributes of Index Partitions
• Partitioning and Table Compression
  • Table Compression and Bitmap Indexes
  • Example of Table Compression and Partitioning
• Recommendations for Choosing a Partitioning Strategy
  • When to Use Range or Interval Partitioning
  • When to Use Hash Partitioning
  • When to Use List Partitioning
  • When to Use Composite Partitioning
    • When to Use Composite Range-Hash Partitioning
    • When to Use Composite Range-List Partitioning
    • When to Use Composite Range-Range Partitioning
    • When to Use Composite List-Hash Partitioning
    • When to Use Composite List-List Partitioning
    • When to Use Composite List-Range Partitioning
  • When to Use Interval Partitioning
  • When to Use Reference Partitioning
  • When to Partition on Virtual Columns
  • Considerations When Using Read-Only Tablespaces

4 Partition Administration

• Specifying Partitioning When Creating Tables and Indexes
  • About Creating Range-Partitioned Tables and Global Indexes
    • Creating a Range-Partitioned Table
    • Creating a Range-Partitioned Table With More Complexity
    • Creating a Range-Partitioned Global Index
  • Creating Interval-Partitioned Tables
  • Specifying Hash Partitioning When Creating Tables and Global Indexes
    • Creating a Hash Partitioned Table
    • Creating a Hash Partitioned Global Index
  • About Creating List-Partitioned Tables
    • Creating a List-Partitioned Table
    • Creating a List-Partitioned Table With a Default Partition
  • Creating Reference-Partitioned Tables
  • Creating Interval-Reference Partitioned Tables
  • Specifying Composite Partitioning When Creating Tables
    • About Creating Composite Range-Hash Partitioned Tables
      • Creating a Composite Range-Hash Partitioned Table With the Same Tablespaces
      • Creating a Composite Range-Hash Partitioned Table With Varying Tablespaces
      • Creating a Local Index Across Multiple Tablespaces
    • About Creating Composite Range-List Partitioned Tables
      • Creating a Composite Range-List Partitioned Table
      • Creating a Composite Range-List Partitioned Table Specifying Tablespaces
    • Creating Composite Range-Range Partitioned Tables
    • Creating Composite List-* Partitioned Tables
      • Creating Composite List-Hash Partitioned Tables
      • Creating Composite List-List Partitioned Tables
      • Creating Composite List-Range Partitioned Tables
    • Creating Composite Interval-* Partitioned Tables
      • Creating Composite Interval-Hash Partitioned Tables
      • Creating Composite Interval-List Partitioned Tables
      • Creating Composite Interval-Range Partitioned Tables
  • Creating a Table Using In-Memory Column Store With Partitioning
  • Specifying Subpartition Templates to Describe Composite Partitioned Tables
    • Specifying a Subpartition Template for a *-Hash Partitioned Table
    • Specifying a Subpartition Template for a *-List Partitioned Table
  • Specifying Partitioning on Key Columns
    • Creating a Multicolumn Range-Partitioned Table By Date
    • Creating a Multicolumn Range-Partitioned Table to Enforce Equal-Sized Partitions
  • Using Virtual Column-Based Partitioning
  • Using Table Compression with Partitioned Tables
  • Using Key Compression with Partitioned Indexes
  • Specifying Partitioning with Segments
    • Deferred Segment Creation for Partitioning
    • Truncating Segments That Are Empty
    • Maintenance Procedures for Segment Creation on Demand
  • Specifying Partitioning When Creating Index-Organized Tables
    • Creating Range-Partitioned Index-Organized Tables
    • Creating Hash Partitioned Index-Organized Tables
    • Creating List-Partitioned Index-Organized Tables
  • Partitioning Restrictions for Multiple Block Sizes
  • Partitioning of Collections in XMLType and Objects
    • Performing PMOs on Partitions that Contain Collection Tables
    • Partitioning of XMLIndex for Binary XML Tables
• Maintenance Operations for Partitioned Tables and Indexes
  • Maintenance Operations on Partitions That Can Be Performed
  • Updating Indexes Automatically
  • Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
  • About Adding Partitions and Subpartitions
    • Adding a Partition to a Range-Partitioned Table
    • Adding a Partition to a Hash Partitioned Table
    • Adding a Partition to a List-Partitioned Table
    • Adding a Partition to an Interval-Partitioned Table
    • About Adding Partitions to a Composite *-Hash Partitioned Table
      • Adding a Partition to a *-Hash Partitioned Table
      • Adding a Subpartition to a *-Hash Partitioned Table
    • About Adding Partitions to a Composite *-List Partitioned Table
      • Adding a Partition to a *-List Partitioned Table
      • Adding a Subpartition to a *-List Partitioned Table
    • About Adding Partitions to a Composite *-Range Partitioned Table
      • Adding a Partition to a *-Range Partitioned Table
      • Adding a Subpartition to a *-Range Partitioned Table
    • About Adding a Partition or Subpartition to a Reference-Partitioned Table
    • Adding Index Partitions
    • Adding Multiple Partitions
  • About Coalescing Partitions and Subpartitions
    • Coalescing a Partition in a Hash Partitioned Table
    • Coalescing a Subpartition in a *-Hash Partitioned Table
    • Coalescing Hash Partitioned Global Indexes
  • About Dropping Partitions and Subpartitions
    • Dropping Table Partitions
      • Dropping a Partition from a Table that Contains Data and Global Indexes
      • Dropping a Partition Containing Data and Referential Integrity Constraints
    • Dropping Interval Partitions
    • Dropping Index Partitions
    • Dropping Multiple Partitions
  • About Exchanging Partitions and Subpartitions
    • Exchanging a Range, Hash, or List Partition
    • Exchanging a Partition of an Interval Partitioned Table
    • Exchanging a Partition of a Reference-Partitioned Table
    • About Exchanging a Partition of a Table with Virtual Columns
    • Exchanging a Hash Partitioned Table with a *-Hash Partition
    • Exchanging a Subpartition of a *-Hash Partitioned Table
    • Exchanging a List-Partitioned Table with a *-List Partition
    • About Exchanging a Subpartition of a *-List Partitioned Table
    • Exchanging a Range-Partitioned Table with a *-Range Partition
    • About Exchanging a Subpartition of a *-Range Partitioned Table
    • About Exchanging a Partition with the Cascade Option
  • About Merging Partitions and Subpartitions
    • Merging Range Partitions
    • Merging Interval Partitions
    • Merging List Partitions
    • Merging *-Hash Partitions
    • About Merging *-List Partitions
      • Merging Partitions in a *-List Partitioned Table
      • Merging Subpartitions in a *-List Partitioned Table
    • About Merging *-Range Partitions
      • Merging Partitions in a *-Range Partitioned Table
    • Merging Multiple Partitions
  • About Modifying Default Attributes
    • Modifying Default Attributes of a Table
    • Modifying Default Attributes of a Partition
    • Modifying Default Attributes of Index Partitions
  • About Modifying Real Attributes of Partitions
    • Modifying Real Attributes for a Range or List Partition
    • Modifying Real Attributes for a Hash Partition
    • Modifying Real Attributes of a Subpartition
    • Modifying Real Attributes of Index Partitions
  • About Modifying List Partitions: Adding Values
    • Adding Values for a List Partition
    • Adding Values for a List Subpartition
  • About Modifying List Partitions: Dropping Values
    • Dropping Values from a List Partition
    • Dropping Values from a List Subpartition
  • Modifying a Subpartition Template
  • About Moving Partitions and Subpartitions
    • Moving Table Partitions
    • Moving Subpartitions
    • Moving Index Partitions
  • About Rebuilding Index Partitions
    • About Rebuilding Global Index Partitions
    • About Rebuilding Local Index Partitions
      • Using ALTER INDEX to Rebuild a Partition
      • Using ALTER TABLE to Rebuild an Index Partition
  • About Renaming Partitions and Subpartitions
    • Renaming a Table Partition
    • Renaming a Table Subpartition
    • About Renaming Index Partitions
      • Renaming an Index Partition
      • Renaming an Index Subpartition
  • About Splitting Partitions and Subpartitions
    • Splitting a Partition of a Range-Partitioned Table
    • Splitting a Partition of a List-Partitioned Table
    • Splitting a Partition of an Interval-Partitioned Table
    • Splitting a *-Hash Partition
    • Splitting Partitions in a *-List Partitioned Table
      • Splitting a *-List Partition
      • Splitting a *-List Subpartition
    • Splitting a *-Range Partition
      • Splitting a *-Range Subpartition
    • Splitting Index Partitions
    • Splitting into Multiple Partitions
    • Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations
  • About Truncating Partitions and Subpartitions
    • About Truncating a Table Partition
      • Truncating Table Partitions Containing Data and Global Indexes
      • Truncating a Partition Containing Data and Referential Integrity Constraints
    • Truncating a Subpartition
    • Truncating a Partition with the Cascade Option
    • Truncating Multiple Partitions
• About Dropping Partitioned Tables
• Evolving a Nonpartitioned Table into a Partitioned Table
  • Using Online Redefinition to Partition Collection Tables
• Viewing Information About Partitioned Tables and Indexes

5 Managing and Maintaining Time-Based Information

• Managing Data in Oracle Database With ILM
  • About Oracle Database for ILM
    • Oracle Database Manages All Types of Data
    • Regulatory Requirements
    • The Benefits of an Online Archive
  • Implementing ILM Using Oracle Database
    • Step 1: Define the Data Classes
      • Partitioning
      • The Lifecycle of Data
    • Step 2: Create Storage Tiers for the Data Classes
      • Assigning Classes to Storage Tiers
      • The Costs Savings of Using Tiered Storage
    • Step 3: Create Data Access and Migration Policies
      • Controlling Access to Data
      • Moving Data using Partitioning
    • Step 4: Define and Enforce Compliance Policies
      • Data Retention
      • Immutability
      • Privacy
      • Auditing
      • Expiration
• Implementing an ILM Strategy With Heat Map and ADO
  • Using Heat Map
    • Enabling and Disabling Heat Map
    • Displaying Heat Map Tracking Data With Views
    • Managing Heat Map Data With DBMS_HEAT_MAP Subprograms
  • Using Automatic Data Optimization
    • Managing Policies for Automatic Data Optimization
    • Creating a Table With an ILM ADO Policy
    • Adding ILM ADO Policies
    • Disabling and Deleting ILM ADO Policies
    • Specifying Segment-Level Compression and Storage Tiering With ADO
    • Specifying Row-Level Compression Tiering With ADO
    • Managing ILM ADO Parameters
    • Using PL/SQL Functions for Policy Management
    • Using Views to Monitor Policies for ADO
  • Limitations and Restrictions With ADO and Heat Map
• Controlling the Validity and Visibility of Data in Oracle Database
  • Using In-Database Archiving
  • Using Temporal Validity
  • Creating a Table With Temporal Validity
  • Limitations and Restrictions With In-Database Archiving and Temporal Validity
• Implementing an ILM System Manually Using Partitioning
• Managing ILM Heat Map and ADO with Oracle Enterprise Manager
  • Accessing the Database Administration Menu
  • Viewing Automatic Data Optimization Activity at the Tablespace Level
  • Viewing the Segment Activity Details of Any Tablespace
  • Viewing the Segment Activity Details of Any Object
  • Viewing the Segment Activity History of Any Object
  • Searching Segment Activity in Automatic Data Optimization
  • Viewing Policies for a Segment
  • Disabling Background Activity
  • Changing Execution Frequency of Background Automatic Data Optimization
  • Viewing Policy Executions In the Last 24 Hours
  • Viewing Objects Moved In Last 24 Hours
  • Viewing Policy Details
  • Viewing Objects Associated With a Policy
  • Evaluating a Policy Before Execution
  • Executing a Single Policy
  • Stopping a Policy Execution
  • Viewing Policy Execution History

6 Using Partitioning in a Data Warehouse Environment

• What Is a Data Warehouse?
• Scalability
  • Bigger Databases
  • Bigger Individual Tables: More Rows in Tables
  • More Users Querying the System
  • More Complex Queries
• Performance
  • Partition Pruning
    • Basic Partition Pruning Techniques
    • Advanced Partition Pruning Techniques
  • Partition-Wise Joins
    • Full Partition-Wise Joins
    • Partial Partition-Wise Joins
    • Benefits of Partition-Wise Joins
      • Reduction of Communications Overhead
      • Reduction of Memory Requirements
    • Performance Considerations for Parallel Partition-Wise Joins
  • Indexes and Partitioned Indexes
    • Local Partitioned Indexes
    • Nonpartitioned Indexes
    • Global Partitioned Indexes
  • Materialized Views and Partitioning
    • Partitioned Materialized Views
• Manageability
  • Partition Exchange Load
  • Partitioning and Indexes
  • Removing Data from Tables
  • Partitioning and Data Compression

7 Using Partitioning in an Online Transaction Processing Environment

• What Is an OLTP System?
• Performance
  • Deciding Whether to Partition Indexes
    • How to Use Partitioning on Index-Organized Tables
• Manageability
  • Impact of a Partition Maintenance Operation on a Partitioned Table with Local Indexes
  • Impact of a Partition Maintenance Operation on Global Indexes
  • Common Partition Maintenance Operations in OLTP Environments
    • Removing (Purging) Old Data
    • Moving or Merging Older Partitions to a Low-Cost Storage Tier Device

8 Using Parallel Execution

• Introduction to Parallel Execution
  • When to Implement Parallel Execution
  • When Not to Implement Parallel Execution
  • Fundamental Hardware Requirements
• How Parallel Execution Works
  • Parallel Execution of SQL Statements
    • Dividing Work Among Parallel Execution Servers
    • Parallelism Between Operations
    • Producer or Consumer Operations
  • How Parallel Execution Servers Communicate
  • Degree of Parallelism
    • Manually Specifying the Degree of Parallelism
      • Default Parallelism
    • Automatic Degree of Parallelism
      • Determining Degree of Parallelism
    • Controlling Automatic Degree of Parallelism
      • Setting Automatic Degree of Parallelism Using ALTER SESSION Statements
      • Setting Automatic Degree of Parallelism Using Hints
    • In-Memory Parallel Execution
    • Adaptive Parallelism
    • Controlling Automatic DOP, Parallel Statement Queuing, and In-Memory Parallel Execution
  • About Parallel Statement Queuing
    • About Managing Parallel Statement Queuing with Oracle Database Resource Manager
      • About Managing the Order of the Parallel Statement Queue
      • About Limiting the Parallel Server Resources for a Consumer Group
      • Specifying a Parallel Statement Queue Timeout for Each Consumer Group
      • Specifying a Degree of Parallelism Limit for Consumer Groups
      • Critical Parallel Statement Prioritization
      • About Managing Parallel Statement Queuing for CDBs and PDBs
      • A Sample Scenario for Managing Statements in the Parallel Queue
    • Grouping Parallel Statements with BEGIN_SQL_BLOCK END_SQL_BLOCK
    • About Managing Parallel Statement Queuing with Hints
  • Parallel Execution Server Pool
    • Processing without Enough Parallel Execution Servers
  • Granules of Parallelism
    • Block Range Granules
    • Partition Granules
  • Balancing the Workload to Optimize Performance
  • Parallel Execution Using Oracle RAC
    • Limiting the Number of Available Instances
• Types of Parallelism
  • About Parallel Queries
    • Parallel Queries on Index-Organized Tables
    • Nonpartitioned Index-Organized Tables
    • Partitioned Index-Organized Tables
    • Parallel Queries on Object Types
    • Rules for Parallelizing Queries
      • Decision to Parallelize
      • Degree of Parallelism
  • About Parallel DDL Statements
    • DDL Statements That Can Be Parallelized
    • About Using 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
    • Rules for DDL Statements
      • Decision to Parallelize
      • Degree of Parallelism
    • Rules for Creating or Rebuilding an Index and Moving or Splitting a Partition
      • Parallel CREATE INDEX or ALTER INDEX REBUILD
      • Parallel MOVE PARTITION or SPLIT PARTITION
    • Rules for CREATE TABLE AS SELECT
      • Decision to Parallelize (Query Part)
      • Degree of Parallelism (Query Part)
      • Decision to Parallelize (CREATE Part)
      • Degree of Parallelism (CREATE Part)
  • About Parallel DML Operations
    • When to Use Parallel DML
      • Refreshing Tables in a Data Warehouse System
      • Creating Intermediate Summary Tables
      • Using Scoring Tables
      • Updating Historical Tables
      • Running Batch Jobs
    • Enable Parallel DML Mode
    • Rules for UPDATE, MERGE, and DELETE
      • Decision to Parallelize
      • Degree of Parallelism
    • Rules for INSERT SELECT
      • Decision to Parallelize
      • Degree of Parallelism
    • Transaction Restrictions for Parallel DML
    • Rollback Segments
    • Recovery for Parallel DML
      • Transaction Recovery for User-Issued Rollback
      • Process Recovery
      • System Recovery
    • Space Considerations for Parallel DML
    • Restrictions on Parallel DML
      • Partitioning Key Restriction
      • Function Restrictions
    • Data Integrity Restrictions
      • NOT NULL and CHECK
      • UNIQUE and PRIMARY KEY
      • FOREIGN KEY (Referential Integrity)
      • Delete Cascade
      • Self-Referential Integrity
      • Deferrable Integrity Constraints
    • Trigger Restrictions
    • Distributed Transaction Restrictions
    • Examples of Distributed Transaction Parallelization
    • Concurrent Execution of Union All
  • About Parallel Execution of Functions
    • Functions in Parallel Queries
    • Functions in Parallel DML and DDL Statements
  • About Other Types of Parallelism
    • Summary of Parallelization Rules
• About Initializing and Tuning Parameters for Parallel Execution
  • Default Parameter Settings
  • Forcing Parallel Execution for a Session
• Tuning General Parameters for Parallel Execution
  • Parameters Establishing Resource Limits for Parallel Operations
    • PARALLEL_FORCE_LOCAL
    • PARALLEL_MAX_SERVERS
      • When Users Have Too Many Processes
      • When to Limit the Number of Resources for a User using a Consumer Group
    • PARALLEL_MIN_PERCENT
    • PARALLEL_MIN_SERVERS
    • PARALLEL_MIN_TIME_THRESHOLD
    • PARALLEL_SERVERS_TARGET
    • SHARED_POOL_SIZE
    • Additional Memory Requirements for Message Buffers
      • Required Memory for Message Buffers
      • Additional Memory for Cursors
    • Monitor Memory Usage After Processing Begins
  • Parameters Affecting Resource Consumption
    • PGA_AGGREGATE_TARGET
      • HASH_AREA_SIZE
      • SORT_AREA_SIZE
    • PARALLEL_EXECUTION_MESSAGE_SIZE
    • Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL
      • TRANSACTIONS
      • FAST_START_PARALLEL_ROLLBACK
      • DML_LOCKS
  • Parameters Related to I/O
    • DB_CACHE_SIZE
    • DB_BLOCK_SIZE
    • DB_FILE_MULTIBLOCK_READ_COUNT
    • DISK_ASYNCH_IO and TAPE_ASYNCH_IO
• Monitoring Parallel Execution Performance
  • Monitoring Parallel Execution Performance with Dynamic Performance Views
    • V$PX_BUFFER_ADVICE
    • V$PX_SESSION
    • V$PX_SESSTAT
    • V$PX_PROCESS
    • V$PX_PROCESS_SYSSTAT
    • V$PQ_SESSTAT
    • V$PQ_TQSTAT
    • V$RSRC_CONS_GROUP_HISTORY
    • V$RSRC_CONSUMER_GROUP
    • V$RSRC_PLAN
    • V$RSRC_PLAN_HISTORY
    • V$RSRC_SESSION_INFO
    • V$RSRCMGRMETRIC
  • Monitoring Session Statistics
  • Monitoring System Statistics
  • Monitoring Operating System Statistics
• Miscellaneous Parallel Execution Tuning Tips
  • Optimizing Performance by Creating and Populating Tables in Parallel
  • Using EXPLAIN PLAN to Show Parallel Operations Plans
    • Example: Using EXPLAIN PLAN to Show Parallel Operations
  • Additional Considerations for Parallel DML
    • Parallel DML and Direct-Path Restrictions
    • Limitation on the Degree of Parallelism
    • When to Increase INITRANS
    • Limitation on Available Number of Transaction Free Lists for Segments
    • Multiple Archivers for Large Numbers of Redo Logs
    • Database Writer Process (DBWn) Workload
    • [NO]LOGGING Clause
  • Optimizing Performance by 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
      • Parallelizing INSERT SELECT
      • Parallelizing UPDATE and DELETE
  • Incremental Data Loading in Parallel
    • Optimizing Performance for Updating the Table in Parallel
    • Efficiently Inserting the New Rows into the Table in Parallel
    • Optimizing Performance by Merging in Parallel
• Automatic Big Table Caching

9 Backing Up and Recovering VLDBs

• Data Warehouses
  • Data Warehouse Characteristics
• Oracle Backup and Recovery
  • Physical Database Structures Used in Recovering Data
    • Data files
    • Redo Logs
    • Control Files
  • Backup Type
  • Backup Tools
    • Oracle Recovery Manager (RMAN)
    • Oracle Data Pump
    • User-Managed Backups
• Data Warehouse Backup and Recovery
  • Recovery Time Objective (RTO)
  • Recovery Point Objective (RPO)
    • More Data Means a Longer Backup Window
    • Divide and Conquer
• The Data Warehouse Recovery Methodology
  • Best Practice 1: Use ARCHIVELOG Mode
    • Is Downtime Acceptable?
  • Best Practice 2: Use RMAN
  • Best Practice 3: Use Block Change Tracking
  • Best Practice 4: Use RMAN Multisection Backups
  • Best Practice 5: Leverage Read-Only Tablespaces
  • Best Practice 6: Plan for NOLOGGING Operations in Your Backup/Recovery Strategy
    • Extract, Transform, and Load
    • The Extract, Transform, and Load Strategy
    • Incremental Backup
    • The Incremental Approach
    • Flashback Database and Guaranteed Restore Points
  • Best Practice 7: Not All Tablespaces Should Be Treated Equally

10 Storage Management for VLDBs

• High Availability
  • Hardware-Based Mirroring
    • RAID 1 Mirroring
    • RAID 5 Mirroring
  • Mirroring Using Oracle ASM
• Performance
  • Hardware-Based Striping
    • RAID 0 Striping
    • RAID 5 Striping
  • Striping Using Oracle ASM
  • Information Lifecycle Management
  • Partition Placement
  • Bigfile Tablespaces
  • Oracle Database File System (DBFS)
• Scalability and Manageability
  • Stripe and Mirror Everything (SAME)
  • SAME and Manageability
• Oracle ASM Settings Specific to VLDBs

Index