Go to main content
1/27
Contents
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 12c Release 2 (12.2.0.1)
New Features
Changes in Oracle Database 12c Release 1 (12.1.0.2)
New Features
Changes in Oracle Database 12
c
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
6.2.5.1
Extracting AWR Data
6.2.5.2
Loading 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
Real-Time ADDM Analysis
7.1.3.1
Real-Time ADDM Connection Modes
7.1.3.2
Real-Time ADDM Triggers
7.1.3.3
Real-Time ADDM Trigger Controls
7.1.4
ADDM Analysis Results
7.1.5
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
Views with ADDM Information
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.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
Streams 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
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
Scripting on this page enhances content navigation, but does not change the content in any way.