Skip to main content
Oracle Help Center

Sign Out

Sign In

  • Table of Contents
  • Search
  • Print
  • Download
  1. Database
  2. Oracle
  3. Oracle Database
  4. Release 19

Database Performance Tuning Guide

  • Table of Contents
  • Search
  • Print
  • Download

Table of Contents

Expand AllCollapse All
  • Title and Copyright Information
  • Preface
    • Audience
    • Documentation Accessibility
    • Related Documents
    • Conventions
  • Changes in This Release for Oracle Database Performance Tuning Guide
    • Changes in Oracle Database Release 19c, Version 19.1
      • New Features
      • Desupported Features
    • Changes in Oracle Database Release 18c, Version 18.1
      • New Features
    • Changes in Oracle Database 12c Release 2 (12.2)
      • New Features
    • Changes in Oracle Database 12c Release 1 (12.1.0.2)
      • New Features
    • Changes in Oracle Database 12c Release 1 (12.1.0.1)
      • New Features
      • Other Changes
  • Part I Database Performance Fundamentals
    • 1 Performance Tuning Overview
      • 1.1 Introduction to Performance Tuning
        • 1.1.1 Performance Planning
        • 1.1.2 Instance Tuning
          • 1.1.2.1 Performance Principles
          • 1.1.2.2 Baselines
          • 1.1.2.3 The Symptoms and the Problems
          • 1.1.2.4 When to Tune
            • 1.1.2.4.1 Proactive Monitoring
            • 1.1.2.4.2 Bottleneck Elimination
        • 1.1.3 SQL Tuning
          • 1.1.3.1 Query Optimizer and Execution Plans
      • 1.2 Introduction to Performance Tuning Features and Tools
        • 1.2.1 Automatic Performance Tuning Features
        • 1.2.2 Additional Oracle Database Tools
          • 1.2.2.1 V$ Performance Views
    • 2 Designing and Developing for Performance
      • 2.1 Oracle Methodology
      • 2.2 Understanding Investment Options
      • 2.3 Understanding Scalability
        • 2.3.1 What is Scalability?
        • 2.3.2 System Scalability
        • 2.3.3 Factors Preventing Scalability
      • 2.4 System Architecture
        • 2.4.1 Hardware and Software Components
          • 2.4.1.1 Hardware Components
          • 2.4.1.2 Software Components
        • 2.4.2 Configuring the Right System Architecture for Your Requirements
      • 2.5 Application Design Principles
        • 2.5.1 Simplicity In Application Design
        • 2.5.2 Data Modeling
        • 2.5.3 Table and Index Design
          • 2.5.3.1 Appending Columns to an Index or Using Index-Organized Tables
          • 2.5.3.2 Using a Different Index Type
          • 2.5.3.3 Finding the Cost of an Index
          • 2.5.3.4 Serializing within Indexes
          • 2.5.3.5 Ordering Columns in an Index
        • 2.5.4 Using Views
        • 2.5.5 SQL Execution Efficiency
        • 2.5.6 Implementing the Application
        • 2.5.7 Trends in Application Development
      • 2.6 Workload Testing, Modeling, and Implementation
        • 2.6.1 Sizing Data
        • 2.6.2 Estimating Workloads
        • 2.6.3 Application Modeling
        • 2.6.4 Testing, Debugging, and Validating a Design
      • 2.7 Deploying New Applications
        • 2.7.1 Rollout Strategies
        • 2.7.2 Performance Checklist
    • 3 Performance Improvement Methods
      • 3.1 The Oracle Performance Improvement Method
        • 3.1.1 Steps in the Oracle Performance Improvement Method
        • 3.1.2 A Sample Decision Process for Performance Conceptual Modeling
        • 3.1.3 Top Ten Mistakes Found in Oracle Systems
      • 3.2 Emergency Performance Methods
        • 3.2.1 Steps in the Emergency Performance Method
    • 4 Configuring a Database for Performance
      • 4.1 Performance Considerations for Initial Instance Configuration
        • 4.1.1 Initialization Parameters
        • 4.1.2 Undo Space
        • 4.1.3 Redo Log Files
        • 4.1.4 Tablespaces
      • 4.2 Creating and Maintaining Tables for Optimal Performance
        • 4.2.1 Table Compression
        • 4.2.2 Reclaiming Unused Space
        • 4.2.3 Indexing Data
      • 4.3 Performance Considerations for Shared Servers
        • 4.3.1 Identifying and Reducing Contention Using the Dispatcher-Specific Views
        • 4.3.2 Identifying Contention for Shared Servers
      • 4.4 Improved Client Connection Performance Due to Prespawned Processes
  • Part II Diagnosing and Tuning Database Performance
    • 5 Measuring Database Performance
      • 5.1 About Database Statistics
        • 5.1.1 Time Model Statistics
        • 5.1.2 Active Session History Statistics
        • 5.1.3 Wait Events Statistics
        • 5.1.4 Session and System Statistics
      • 5.2 Interpreting Database Statistics
        • 5.2.1 Using Hit Ratios
        • 5.2.2 Using Wait Events with Timed Statistics
        • 5.2.3 Using Wait Events without Timed Statistics
        • 5.2.4 Using Idle Wait Events
        • 5.2.5 Comparing Database Statistics with Other Factors
        • 5.2.6 Using Computed Statistics
    • 6 Gathering Database Statistics
      • 6.1 About Gathering Database Statistics
        • 6.1.1 Automatic Workload Repository
        • 6.1.2 Snapshots
        • 6.1.3 Baselines
          • 6.1.3.1 Fixed Baselines
          • 6.1.3.2 Moving Window Baselines
          • 6.1.3.3 Baseline Templates
            • 6.1.3.3.1 Single Baseline Templates
            • 6.1.3.3.2 Repeating Baseline Templates
        • 6.1.4 Space Consumption
        • 6.1.5 Adaptive Thresholds
          • 6.1.5.1 Percentage of Maximum Thresholds
          • 6.1.5.2 Significance Level Thresholds
      • 6.2 Managing the Automatic Workload Repository
        • 6.2.1 Enabling the Automatic Workload Repository
        • 6.2.2 Managing Snapshots
          • 6.2.2.1 User Interfaces for Managing Snapshots
          • 6.2.2.2 Creating Snapshots
          • 6.2.2.3 Dropping Snapshots
          • 6.2.2.4 Modifying Snapshot Settings
        • 6.2.3 Managing Baselines
          • 6.2.3.1 User Interface for Managing Baselines
          • 6.2.3.2 Creating a Baseline
          • 6.2.3.3 Dropping a Baseline
          • 6.2.3.4 Renaming a Baseline
          • 6.2.3.5 Displaying Baseline Metrics
          • 6.2.3.6 Resizing the Default Moving Window Baseline
        • 6.2.4 Managing Baseline Templates
          • 6.2.4.1 User Interfaces for Managing Baseline Templates
          • 6.2.4.2 Creating a Single Baseline Template
          • 6.2.4.3 Creating a Repeating Baseline Template
          • 6.2.4.4 Dropping a Baseline Template
        • 6.2.5 Transporting Automatic Workload Repository Data to Another System
          • 6.2.5.1 Exporting AWR Data
          • 6.2.5.2 Importing AWR Data
        • 6.2.6 Using Automatic Workload Repository Views
        • 6.2.7 Managing Automatic Workload Repository in a Multitenant Environment
          • 6.2.7.1 Categorization of AWR Data in a Multitenant Environment
          • 6.2.7.2 AWR Data Storage and Retrieval in a Multitenant Environment
          • 6.2.7.3 Viewing AWR Data in a Multitenant Environment
        • 6.2.8 Managing Automatic Workload Repository in Active Data Guard Standby Databases
          • 6.2.8.1 Configuring the Remote Management Framework (RMF)
          • 6.2.8.2 Managing Snapshots for Active Data Guard Standby Databases
          • 6.2.8.3 Viewing AWR Data in Active Data Guard Standby Databases
      • 6.3 Generating Automatic Workload Repository Reports
        • 6.3.1 User Interface for Generating an AWR Report
        • 6.3.2 Generating an AWR Report Using the Command-Line Interface
          • 6.3.2.1 Generating an AWR Report for the Local Database
          • 6.3.2.2 Generating an AWR Report for a Specific Database
          • 6.3.2.3 Generating an AWR Report for the Local Database in Oracle RAC
          • 6.3.2.4 Generating an AWR Report for a Specific Database in Oracle RAC
          • 6.3.2.5 Generating an AWR Report for a SQL Statement on the Local Database
          • 6.3.2.6 Generating an AWR Report for a SQL Statement on a Specific Database
      • 6.4 Generating Performance Hub Active Report
        • 6.4.1 Overview of Performance Hub Active Report
          • 6.4.1.1 About Performance Hub Active Report Tabs
          • 6.4.1.2 About Performance Hub Active Report Types
        • 6.4.2 Command-Line User Interface for Generating a Performance Hub Active Report
        • 6.4.3 Generating a Performance Hub Active Report Using a SQL Script
    • 7 Automatic Performance Diagnostics
      • 7.1 Overview of the Automatic Database Diagnostic Monitor
        • 7.1.1 ADDM Analysis
        • 7.1.2 Using ADDM with Oracle Real Application Clusters
        • 7.1.3 Using ADDM in a Multitenant Environment
          • 7.1.3.1 Enabling ADDM in a Pluggable Database
        • 7.1.4 Real-Time ADDM Analysis
          • 7.1.4.1 Real-Time ADDM Connection Modes
          • 7.1.4.2 Real-Time ADDM Triggers
          • 7.1.4.3 Real-Time ADDM Trigger Controls
        • 7.1.5 ADDM Analysis Results
        • 7.1.6 Reviewing ADDM Analysis Results: Example
      • 7.2 Setting Up ADDM
      • 7.3 Diagnosing Database Performance Problems with ADDM
        • 7.3.1 Running ADDM in Database Mode
        • 7.3.2 Running ADDM in Instance Mode
        • 7.3.3 Running ADDM in Partial Mode
        • 7.3.4 Displaying an ADDM Report
      • 7.4 ADDM Views
    • 8 Comparing Database Performance Over Time
      • 8.1 About Automatic Workload Repository Compare Periods Reports
      • 8.2 Generating Automatic Workload Repository Compare Periods Reports
        • 8.2.1 User Interfaces for Generating AWR Compare Periods Reports
        • 8.2.2 Generating an AWR Compare Periods Report Using the Command-Line Interface
          • 8.2.2.1 Generating an AWR Compare Periods Report for the Local Database
          • 8.2.2.2 Generating an AWR Compare Periods Report for a Specific Database
          • 8.2.2.3 Generating an Oracle RAC AWR Compare Periods Report for the Local Database
          • 8.2.2.4 Generating an Oracle RAC AWR Compare Periods Report for a Specific Database
      • 8.3 Interpreting Automatic Workload Repository Compare Periods Reports
        • 8.3.1 Summary of the AWR Compare Periods Report
          • 8.3.1.1 Snapshot Sets
          • 8.3.1.2 Host Configuration Comparison
          • 8.3.1.3 System Configuration Comparison
          • 8.3.1.4 Load Profile
          • 8.3.1.5 Top 5 Timed Events
        • 8.3.2 Details of the AWR Compare Periods Report
          • 8.3.2.1 Time Model Statistics
          • 8.3.2.2 Operating System Statistics
          • 8.3.2.3 Wait Events
          • 8.3.2.4 Service Statistics
          • 8.3.2.5 SQL Statistics
            • 8.3.2.5.1 Top 10 SQL Comparison by Execution Time
            • 8.3.2.5.2 Top 10 SQL Comparison by CPU Time
            • 8.3.2.5.3 Top 10 SQL Comparison by Buffer Gets
            • 8.3.2.5.4 Top 10 SQL Comparison by Physical Reads
            • 8.3.2.5.5 Top 10 SQL Comparison by Executions
            • 8.3.2.5.6 Top 10 SQL Comparison by Parse Calls
            • 8.3.2.5.7 Complete List of SQL Text
          • 8.3.2.6 Instance Activity Statistics
            • 8.3.2.6.1 Key Instance Activity Statistics
            • 8.3.2.6.2 Other Instance Activity Statistics
          • 8.3.2.7 I/O Statistics
            • 8.3.2.7.1 Tablespace I/O Statistics
            • 8.3.2.7.2 Top 10 File Comparison by I/O
            • 8.3.2.7.3 Top 10 File Comparison by Read Time
            • 8.3.2.7.4 Top 10 File Comparison by Buffer Waits
          • 8.3.2.8 Advisory Statistics
            • 8.3.2.8.1 PGA Aggregate Summary
            • 8.3.2.8.2 PGA Aggregate Target Statistics
          • 8.3.2.9 Wait Statistics
            • 8.3.2.9.1 Buffer Wait Statistics
            • 8.3.2.9.2 Enqueue Activity
          • 8.3.2.10 Undo Segment Summary
          • 8.3.2.11 Latch Statistics
          • 8.3.2.12 Segment Statistics
            • 8.3.2.12.1 Top 5 Segments Comparison by Logical Reads
              • 8.3.2.12.1.1 Top 5 Segments Comparison by Physical Reads
              • 8.3.2.12.1.2 Top 5 Segments Comparison by Row Lock Waits
          • 8.3.2.13 In-Memory Segment Statistics
          • 8.3.2.14 Dictionary Cache Statistics
          • 8.3.2.15 Library Cache Statistics
          • 8.3.2.16 Memory Statistics
            • 8.3.2.16.1 Process Memory Summary
            • 8.3.2.16.2 SGA Memory Summary
            • 8.3.2.16.3 SGA Breakdown Difference
          • 8.3.2.17 Advanced Queuing Statistics
        • 8.3.3 Supplemental Information in the AWR Compare Periods Report
          • 8.3.3.1 init.ora Parameters
          • 8.3.3.2 Complete List of SQL Text
    • 9 Analyzing Sampled Data
      • 9.1 About Active Session History
      • 9.2 Generating Active Session History Reports
        • 9.2.1 User Interfaces for Generating ASH Reports
        • 9.2.2 Generating an ASH Report Using the Command-Line Interface
          • 9.2.2.1 Generating an ASH Report on the Local Database Instance
          • 9.2.2.2 Generating an ASH Report on a Specific Database Instance
          • 9.2.2.3 Generating an ASH Report for Oracle RAC
      • 9.3 Interpreting Results from Active Session History Reports
        • 9.3.1 Top Events
          • 9.3.1.1 Top User Events
          • 9.3.1.2 Top Background Events
          • 9.3.1.3 Top Event P1/P2/P3
        • 9.3.2 Load Profile
          • 9.3.2.1 Top Service/Module
          • 9.3.2.2 Top Client IDs
          • 9.3.2.3 Top SQL Command Types
          • 9.3.2.4 Top Phases of Execution
        • 9.3.3 Top SQL
          • 9.3.3.1 Top SQL with Top Events
          • 9.3.3.2 Top SQL with Top Row Sources
          • 9.3.3.3 Top SQL Using Literals
          • 9.3.3.4 Top Parsing Module/Action
          • 9.3.3.5 Complete List of SQL Text
        • 9.3.4 Top PL/SQL
        • 9.3.5 Top Java
        • 9.3.6 Top Sessions
          • 9.3.6.1 Top Sessions
          • 9.3.6.2 Top Blocking Sessions
          • 9.3.6.3 Top Sessions Running PQs
        • 9.3.7 Top Objects/Files/Latches
          • 9.3.7.1 Top DB Objects
          • 9.3.7.2 Top DB Files
          • 9.3.7.3 Top Latches
        • 9.3.8 Activity Over Time
    • 10 Instance Tuning Using Performance Views
      • 10.1 Instance Tuning Steps
        • 10.1.1 Define the Problem
        • 10.1.2 Examine the Host System
          • 10.1.2.1 CPU Usage
            • 10.1.2.1.1 Non-Oracle Processes
            • 10.1.2.1.2 Oracle Processes
            • 10.1.2.1.3 Oracle Database CPU Statistics
            • 10.1.2.1.4 Interpreting CPU Statistics
          • 10.1.2.2 Identifying I/O Problems
            • 10.1.2.2.1 Identifying I/O Problems Using V$ Views
            • 10.1.2.2.2 Identifying I/O Problems Using Operating System Monitoring Tools
          • 10.1.2.3 Identifying Network Issues
        • 10.1.3 Examine the Oracle Database Statistics
          • 10.1.3.1 Setting the Level of Statistics Collection
          • 10.1.3.2 Wait Events
          • 10.1.3.3 Dynamic Performance Views Containing Wait Event Statistics
          • 10.1.3.4 System Statistics
          • 10.1.3.5 Segment-Level Statistics
        • 10.1.4 Implement and Measure Change
      • 10.2 Interpreting Oracle Database Statistics
        • 10.2.1 Examine Load
        • 10.2.2 Using Wait Event Statistics to Drill Down to Bottlenecks
        • 10.2.3 Table of Wait Events and Potential Causes
        • 10.2.4 Additional Statistics
      • 10.3 Wait Events Statistics
        • 10.3.1 Changes to Wait Event Statistics from Past Releases
        • 10.3.2 buffer busy waits
        • 10.3.3 db file scattered read
        • 10.3.4 db file sequential read
        • 10.3.5 direct path read and direct path read temp
        • 10.3.6 direct path write and direct path write temp
        • 10.3.7 enqueue (enq:) waits
        • 10.3.8 events in wait class other
        • 10.3.9 free buffer waits
        • 10.3.10 Idle Wait Events
        • 10.3.11 latch events
        • 10.3.12 log file parallel write
        • 10.3.13 library cache pin
        • 10.3.14 library cache lock
        • 10.3.15 log buffer space
        • 10.3.16 log file switch
        • 10.3.17 log file sync
        • 10.3.18 rdbms ipc reply
        • 10.3.19 SQL*Net Events
      • 10.4 Tuning Instance Recovery Performance: Fast-Start Fault Recovery
        • 10.4.1 About Instance Recovery
          • 10.4.1.1 Cache Recovery (Rolling Forward)
          • 10.4.1.2 Transaction Recovery (Rolling Back)
          • 10.4.1.3 Checkpoints and Cache Recovery
        • 10.4.2 Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
          • 10.4.2.1 Practical Values for FAST_START_MTTR_TARGET
          • 10.4.2.2 Reducing Checkpoint Frequency to Optimize Run-Time Performance
          • 10.4.2.3 Monitoring Cache Recovery with V$INSTANCE_RECOVERY
        • 10.4.3 Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
          • 10.4.3.1 Calibrate the FAST_START_MTTR_TARGET
          • 10.4.3.2 Determine the Practical Range for FAST_START_MTTR_TARGET
            • 10.4.3.2.1 Determining Lower Bound for FAST_START_MTTR_TARGET: Scenario
            • 10.4.3.2.2 Determining Upper Bound for FAST_START_MTTR_TARGET
            • 10.4.3.2.3 Selecting Preliminary Value for FAST_START_MTTR_TARGET
          • 10.4.3.3 Evaluate Different Target Values with MTTR Advisor
            • 10.4.3.3.1 Enabling MTTR Advisor
            • 10.4.3.3.2 Using MTTR Advisor
            • 10.4.3.3.3 Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVICE
          • 10.4.3.4 Determine the Optimal Size for Redo Logs
  • Part III Tuning Database Memory
    • 11 Database Memory Allocation
      • 11.1 About Database Memory Caches and Other Memory Structures
      • 11.2 Database Memory Management Methods
        • 11.2.1 Automatic Memory Management
        • 11.2.2 Automatic Shared Memory Management
        • 11.2.3 Manual Shared Memory Management
        • 11.2.4 Automatic PGA Memory Management
        • 11.2.5 Manual PGA Memory Management
      • 11.3 Using Automatic Memory Management
      • 11.4 Monitoring Memory Management
    • 12 Tuning the System Global Area
      • 12.1 Using Automatic Shared Memory Management
        • 12.1.1 User Interfaces for Setting the SGA_TARGET Parameter
          • 12.1.1.1 Setting the SGA_TARGET Parameter in Oracle Enterprise Manager Cloud Control
          • 12.1.1.2 Setting the SGA_TARGET Parameter in the Command-Line Interface
        • 12.1.2 Setting the SGA_TARGET Parameter
          • 12.1.2.1 Enabling Automatic Shared Memory Management
          • 12.1.2.2 Disabling Automatic Shared Memory Management
      • 12.2 Sizing the SGA Components Manually
        • 12.2.1 SGA Sizing Unit
        • 12.2.2 Maximum Size of the SGA
        • 12.2.3 Application Considerations
        • 12.2.4 Operating System Memory Use
          • 12.2.4.1 Reduce Paging
          • 12.2.4.2 Fit the SGA into Main Memory
            • 12.2.4.2.1 Viewing SGA Memory Allocation
            • 12.2.4.2.2 Locking the SGA into Physical Memory
          • 12.2.4.3 Allow Adequate Memory to Individual Users
        • 12.2.5 Iteration During Configuration
      • 12.3 Monitoring Shared Memory Management
      • 12.4 Improving Query Performance with the In-Memory Column Store
      • 12.5 Enabling High Performance Data Streaming with the Memoptimized Rowstore
        • 12.5.1 About the Memoptimized Rowstore
        • 12.5.2 Using Fast Ingest
          • 12.5.2.1 Enabling a Table for Fast Ingest
          • 12.5.2.2 Specifying a Hint for Using Fast Ingest for Data Inserts
          • 12.5.2.3 Disabling a Table for Fast Ingest
          • 12.5.2.4 Managing Fast Ingest Data in the Large Pool
        • 12.5.3 Using Fast Lookup
          • 12.5.3.1 Enabling the Memoptimize Pool
          • 12.5.3.2 Enabling a Table for Fast Lookup
          • 12.5.3.3 Disabling a Table for Fast Lookup
          • 12.5.3.4 Managing Fast Lookup Data in the Memoptimize Pool
    • 13 Tuning the Database Buffer Cache
      • 13.1 About the Database Buffer Cache
      • 13.2 Configuring the Database Buffer Cache
        • 13.2.1 Using the V$DB_CACHE_ADVICE View
        • 13.2.2 Calculating the Buffer Cache Hit Ratio
        • 13.2.3 Interpreting the Buffer Cache Hit Ratio
        • 13.2.4 Increasing Memory Allocated to the Database Buffer Cache
        • 13.2.5 Reducing Memory Allocated to the Database Buffer Cache
      • 13.3 Configuring Multiple Buffer Pools
        • 13.3.1 Considerations for Using Multiple Buffer Pools
          • 13.3.1.1 Random Access to Large Segments
          • 13.3.1.2 Oracle Real Application Cluster Instances
        • 13.3.2 Using Multiple Buffer Pools
        • 13.3.3 Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools
        • 13.3.4 Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools
        • 13.3.5 Examining the Buffer Cache Usage Pattern
          • 13.3.5.1 Examining the Buffer Cache Usage Pattern for All Segments
          • 13.3.5.2 Examining the Buffer Cache Usage Pattern for a Specific Segment
        • 13.3.6 Configuring the KEEP Pool
        • 13.3.7 Configuring the RECYCLE Pool
      • 13.4 Configuring the Redo Log Buffer
        • 13.4.1 Sizing the Redo Log Buffer
        • 13.4.2 Using Redo Log Buffer Statistics
      • 13.5 Configuring the Database Caching Mode
        • 13.5.1 Default Database Caching Mode
        • 13.5.2 Force Full Database Caching Mode
        • 13.5.3 Determining When to Use Force Full Database Caching Mode
        • 13.5.4 Verifying the Database Caching Mode
    • 14 Tuning the Shared Pool and the Large Pool
      • 14.1 About the Shared Pool
        • 14.1.1 Benefits of Using the Shared Pool
        • 14.1.2 Shared Pool Concepts
          • 14.1.2.1 Library Cache Concepts
          • 14.1.2.2 Data Dictionary Cache Concepts
          • 14.1.2.3 SQL Sharing Criteria
      • 14.2 Using the Shared Pool
        • 14.2.1 Use Shared Cursors
        • 14.2.2 Use Single-User Logon and Qualified Table Reference
        • 14.2.3 Use PL/SQL
        • 14.2.4 Avoid Performing DDL Operations
        • 14.2.5 Cache Sequence Numbers
        • 14.2.6 Control Cursor Access
          • 14.2.6.1 Controlling Cursor Access Using OCI
          • 14.2.6.2 Controlling Cursor Access Using Oracle Precompilers
          • 14.2.6.3 Controlling Cursor Access Using SQLJ
          • 14.2.6.4 Controlling Cursor Access Using JDBC
          • 14.2.6.5 Controlling Cursor Access Using Oracle Forms
        • 14.2.7 Maintain Persistent Connections
      • 14.3 Configuring the Shared Pool
        • 14.3.1 Sizing the Shared Pool
          • 14.3.1.1 Using Library Cache Statistics
            • 14.3.1.1.1 Using the V$LIBRARYCACHE View
            • 14.3.1.1.2 Calculating the Library Cache Hit Ratio
            • 14.3.1.1.3 Viewing the Amount of Free Memory in the Shared Pool
          • 14.3.1.2 Using Shared Pool Advisory Statistics
            • 14.3.1.2.1 About the V$SHARED_POOL_ADVICE View
            • 14.3.1.2.2 About the V$LIBRARY_CACHE_MEMORY View
            • 14.3.1.2.3 About V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_CACHE_MEMORY Views
          • 14.3.1.3 Using Dictionary Cache Statistics
          • 14.3.1.4 Increasing Memory Allocated to the Shared Pool
          • 14.3.1.5 Reducing Memory Allocated to the Shared Pool
        • 14.3.2 Deallocating Cursors
        • 14.3.3 Caching Session Cursors
          • 14.3.3.1 About the Session Cursor Cache
          • 14.3.3.2 Enabling the Session Cursor Cache
          • 14.3.3.3 Sizing the Session Cursor Cache
        • 14.3.4 Sharing Cursors
          • 14.3.4.1 About Cursor Sharing
          • 14.3.4.2 Forcing Cursor Sharing
        • 14.3.5 Keeping Large Objects to Prevent Aging
        • 14.3.6 Configuring the Reserved Pool
          • 14.3.6.1 Sizing the Reserved Pool
          • 14.3.6.2 Increasing Memory Allocated to the Reserved Pool
          • 14.3.6.3 Reducing Memory Allocated to the Reserved Pool
      • 14.4 Configuring the Large Pool
        • 14.4.1 Configuring the Large Pool for Shared Server Architecture
        • 14.4.2 Configuring the Large Pool for Parallel Query
        • 14.4.3 Sizing the Large Pool
        • 14.4.4 Limiting Memory Use for User Sessions
        • 14.4.5 Reducing Memory Use Using Three-Tier Connections
    • 15 Tuning the Result Cache
      • 15.1 About the Result Cache
        • 15.1.1 Server Result Cache Concepts
          • 15.1.1.1 Benefits of Using the Server Result Cache
          • 15.1.1.2 Understanding How the Server Result Cache Works
            • 15.1.1.2.1 How Results are Retrieved in a Query
            • 15.1.1.2.2 How Results are Retrieved in a View
        • 15.1.2 Client Result Cache Concepts
          • 15.1.2.1 Benefits of Using the Client Result Cache
          • 15.1.2.2 Understanding How the Client Result Cache Works
      • 15.2 Configuring the Result Cache
        • 15.2.1 Configuring the Server Result Cache
          • 15.2.1.1 Sizing the Server Result Cache Using Initialization Parameters
          • 15.2.1.2 Managing the Server Result Cache Using DBMS_RESULT_CACHE
            • 15.2.1.2.1 Viewing Memory Usage Statistics for the Server Result Cache
            • 15.2.1.2.2 Flushing the Server Result Cache
        • 15.2.2 Configuring the Client Result Cache
        • 15.2.3 Setting the Result Cache Mode
        • 15.2.4 Requirements for the Result Cache
          • 15.2.4.1 Read Consistency Requirements
          • 15.2.4.2 Query Parameter Requirements
          • 15.2.4.3 Restrictions for the Result Cache
      • 15.3 Specifying Queries for Result Caching
        • 15.3.1 Using SQL Result Cache Hints
          • 15.3.1.1 Using the RESULT_CACHE Hint
          • 15.3.1.2 Using the NO_RESULT_CACHE Hint
          • 15.3.1.3 Using the RESULT_CACHE Hint in Views
        • 15.3.2 Using Result Cache Table Annotations
          • 15.3.2.1 Using the DEFAULT Table Annotation
          • 15.3.2.2 Using the FORCE Table Annotation
      • 15.4 Monitoring the Result Cache
    • 16 Tuning the Program Global Area
      • 16.1 About the Program Global Area
        • 16.1.1 Work Area Sizes
      • 16.2 Sizing the Program Global Area Using Automatic Memory Management
        • 16.2.1 Configuring Automatic PGA Memory Management
        • 16.2.2 Setting the Initial Value for PGA_AGGREGATE_TARGET
        • 16.2.3 Monitoring Automatic PGA Memory Management
          • 16.2.3.1 Using the V$PGASTAT View
          • 16.2.3.2 Using the V$PROCESS View
          • 16.2.3.3 Using the V$PROCESS_MEMORY View
          • 16.2.3.4 Using the V$SQL_WORKAREA_HISTOGRAM View
          • 16.2.3.5 Using the V$WORKAREA_ACTIVE View
          • 16.2.3.6 Using the V$SQL_WORKAREA View
        • 16.2.4 Tuning PGA_AGGREGATE_TARGET
          • 16.2.4.1 Enabling Automatic Generation of PGA Performance Advisory Views
          • 16.2.4.2 Using the V$PGA_TARGET_ADVICE View
          • 16.2.4.3 Using the V$PGA_TARGET_ADVICE_HISTOGRAM View
          • 16.2.4.4 Using the V$SYSSTAT and V$SESSTAT Views
          • 16.2.4.5 Tutorial: How to Tune PGA_AGGREGATE_TARGET
      • 16.3 Sizing the Program Global Area by Specifying an Absolute Limit
        • 16.3.1 Sizing the Program Global Area Using the PGA_AGGREGATE_LIMIT Parameter
        • 16.3.2 Sizing the Program Global Area Using the Resource Manager
  • Part IV Managing System Resources
    • 17 I/O Configuration and Design
      • 17.1 About I/O
      • 17.2 I/O Configuration
        • 17.2.1 Lay Out the Files Using Operating System or Hardware Striping
          • 17.2.1.1 Requested I/O Size
          • 17.2.1.2 Concurrency of I/O Requests
          • 17.2.1.3 Alignment of Physical Stripe Boundaries with Block Size Boundaries
          • 17.2.1.4 Manageability of the Proposed System
        • 17.2.2 Manually Distributing I/O
        • 17.2.3 When to Separate Files
          • 17.2.3.1 Tables, Indexes, and TEMP Tablespaces
          • 17.2.3.2 Redo Log Files
          • 17.2.3.3 Archived Redo Logs
        • 17.2.4 Three Sample Configurations
          • 17.2.4.1 Stripe Everything Across Every Disk
          • 17.2.4.2 Move Archive Logs to Different Disks
          • 17.2.4.3 Move Redo Logs to Separate Disks
        • 17.2.5 Oracle Managed Files
        • 17.2.6 Choosing Data Block Size
          • 17.2.6.1 Reads
          • 17.2.6.2 Writes
          • 17.2.6.3 Block Size Advantages and Disadvantages
      • 17.3 I/O Calibration Inside the Database
        • 17.3.1 Prerequisites for I/O Calibration
        • 17.3.2 Running I/O Calibration
      • 17.4 I/O Calibration with the Oracle Orion Calibration Tool
        • 17.4.1 Introduction to the Oracle Orion Calibration Tool
          • 17.4.1.1 Orion Test Targets
          • 17.4.1.2 Orion for Oracle Administrators
        • 17.4.2 Getting Started with Orion
        • 17.4.3 Orion Input Files
        • 17.4.4 Orion Parameters
          • 17.4.4.1 Orion Required Parameter
          • 17.4.4.2 Orion Optional Parameters
          • 17.4.4.3 Orion Command Line Samples
        • 17.4.5 Orion Output Files
          • 17.4.5.1 Orion Sample Output Files
        • 17.4.6 Orion Troubleshooting
    • 18 Managing Operating System Resources
      • 18.1 Understanding Operating System Performance Issues
        • 18.1.1 Using Operating System Caches
          • 18.1.1.1 Asynchronous I/O
          • 18.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter
          • 18.1.1.3 Limiting Asynchronous I/O in NFS Server Environments
          • 18.1.1.4 Improving I/O Performance Using Direct NFS Client
        • 18.1.2 Memory Usage
          • 18.1.2.1 Buffer Cache Limits
          • 18.1.2.2 Parameters Affecting Memory Usage
        • 18.1.3 Using Operating System Resource Managers
      • 18.2 Resolving Operating System Issues
        • 18.2.1 Performance Hints on UNIX-Based Systems
        • 18.2.2 Performance Hints on Windows Systems
        • 18.2.3 Performance Hints on HP OpenVMS Systems
      • 18.3 Understanding CPU
      • 18.4 Resolving CPU Issues
        • 18.4.1 Finding and Tuning CPU Utilization
          • 18.4.1.1 Checking Memory Management
            • 18.4.1.1.1 Paging and Swapping
            • 18.4.1.1.2 Oversize Page Tables
          • 18.4.1.2 Checking I/O Management
          • 18.4.1.3 Checking Network Management
          • 18.4.1.4 Checking Process Management
            • 18.4.1.4.1 Scheduling and Switching
            • 18.4.1.4.2 Context Switching
            • 18.4.1.4.3 Starting New Operating System Processes
        • 18.4.2 Managing CPU Resources Using Oracle Database Resource Manager
        • 18.4.3 Managing CPU Resources Using Instance Caging
  • Index

Search

Print

Download

PDF for offline and print

  • Previous
  • Next
  1. Database Performance Tuning Guide
  2. Tuning Database Memory

Part III Tuning Database Memory

This part contains the following chapters:

  • Database Memory Allocation

  • Tuning the System Global Area

  • Tuning the Database Buffer Cache

  • Tuning the Shared Pool and the Large Pool

  • Tuning the Result Cache

  • Tuning the Program Global Area

  • Previous
  • Next
Back to main content
  • About Oracle
  • Contact Us
  • Terms of Use and Privacy
  • Cookie Preferences
Copyright © 2007, 2019, Oracle and/or its affiliates.
  • Previous
  • Next
Video