Adresa
:
[go:
nahoru
,
domu
]
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 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
Introduction to Performance Tuning
Performance Planning
Instance Tuning
Performance Principles
Baselines
The Symptoms and the Problems
When to Tune
Proactive Monitoring
Bottleneck Elimination
SQL Tuning
Query Optimizer and Execution Plans
Introduction to Performance Tuning Features and Tools
Automatic Performance Tuning Features
Additional Oracle Database Tools
V$ Performance Views
2
Designing and Developing for Performance
Oracle Methodology
Understanding Investment Options
Understanding Scalability
What is Scalability?
System Scalability
Factors Preventing Scalability
System Architecture
Hardware and Software Components
Hardware Components
Software Components
Configuring the Right System Architecture for Your Requirements
Application Design Principles
Simplicity In Application Design
Data Modeling
Table and Index Design
Appending Columns to an Index or Using Index-Organized Tables
Using a Different Index Type
Finding the Cost of an Index
Serializing within Indexes
Ordering Columns in an Index
Using Views
SQL Execution Efficiency
Implementing the Application
Trends in Application Development
Workload Testing, Modeling, and Implementation
Sizing Data
Estimating Workloads
Application Modeling
Testing, Debugging, and Validating a Design
Deploying New Applications
Rollout Strategies
Performance Checklist
3
Performance Improvement Methods
The Oracle Performance Improvement Method
Steps in the Oracle Performance Improvement Method
A Sample Decision Process for Performance Conceptual Modeling
Top Ten Mistakes Found in Oracle Systems
Emergency Performance Methods
Steps in the Emergency Performance Method
4
Configuring a Database for Performance
Performance Considerations for Initial Instance Configuration
Initialization Parameters
Undo Space
Redo Log Files
Tablespaces
Creating and Maintaining Tables for Optimal Performance
Table Compression
Reclaiming Unused Space
Indexing Data
Performance Considerations for Shared Servers
Identifying and Reducing Contention Using the Dispatcher-Specific Views
Identifying Contention for Shared Servers
Part II Diagnosing and Tuning Database Performance
5
Measuring Database Performance
About Database Statistics
Time Model Statistics
Active Session History Statistics
Wait Events Statistics
Session and System Statistics
Interpreting Database Statistics
Using Hit Ratios
Using Wait Events with Timed Statistics
Using Wait Events without Timed Statistics
Using Idle Wait Events
Comparing Database Statistics with Other Factors
Using Computed Statistics
6
Gathering Database Statistics
About Gathering Database Statistics
Automatic Workload Repository
Snapshots
Baselines
Fixed Baselines
Moving Window Baselines
Baseline Templates
Single Baseline Templates
Repeating Baseline Templates
Space Consumption
Adaptive Thresholds
Percentage of Maximum Thresholds
Significance Level Thresholds
Managing the Automatic Workload Repository
Enabling the Automatic Workload Repository
Managing Snapshots
User Interfaces for Managing Snapshots
Creating Snapshots
Dropping Snapshots
Modifying Snapshot Settings
Managing Baselines
User Interface for Managing Baselines
Creating a Baseline
Dropping a Baseline
Renaming a Baseline
Displaying Baseline Metrics
Resizing the Default Moving Window Baseline
Managing Baseline Templates
User Interfaces for Managing Baseline Templates
Creating a Single Baseline Template
Creating a Repeating Baseline Template
Dropping a Baseline Template
Transporting Automatic Workload Repository Data
Extracting AWR Data
Loading AWR Data
Using Automatic Workload Repository Views
Generating Automatic Workload Repository Reports
User Interface for Generating an AWR Report
Generating an AWR Report Using the Command-Line Interface
Generating an AWR Report for the Local Database
Generating an AWR Report for a Specific Database
Generating an Oracle RAC AWR Report for the Local Database
Generating an Oracle RAC AWR Report for a Specific Database
Generating an AWR Report for a SQL Statement on the Local Database
Generating an AWR Report for a SQL Statement on a Specific Database
Generating Performance Hub Active Report
Overview of Performance Hub Active Report
About Performance Hub Active Report Tabs
About Performance Hub Active Report Types
Command-Line User Interface for Generating a Performance Hub Active Report
Generating a Performance Hub Active Report Using a SQL Script
7
Automatic Performance Diagnostics
Overview of the Automatic Database Diagnostic Monitor
ADDM Analysis
Using ADDM with Oracle Real Application Clusters
Real-Time ADDM Analysis
Real-Time ADDM Connection Modes
Real-Time ADDM Triggers
Real-Time ADDM Trigger Controls
ADDM Analysis Results
Reviewing ADDM Analysis Results: Example
Setting Up ADDM
Diagnosing Database Performance Problems with ADDM
Running ADDM in Database Mode
Running ADDM in Instance Mode
Running ADDM in Partial Mode
Displaying an ADDM Report
Views with ADDM Information
8
Comparing Database Performance Over Time
About Automatic Workload Repository Compare Periods Reports
Generating Automatic Workload Repository Compare Periods Reports
User Interfaces for Generating AWR Compare Periods Reports
Generating an AWR Compare Periods Report Using the Command-Line Interface
Generating an AWR Compare Periods Report for the Local Database
Generating an AWR Compare Periods Report for a Specific Database
Generating an Oracle RAC AWR Compare Periods Report for the Local Database
Generating an Oracle RAC AWR Compare Periods Report for a Specific Database
Interpreting Automatic Workload Repository Compare Periods Reports
Summary of the AWR Compare Periods Report
Snapshot Sets
Host Configuration Comparison
System Configuration Comparison
Load Profile
Top 5 Timed Events
Details of the AWR Compare Periods Report
Time Model Statistics
Operating System Statistics
Wait Events
Service Statistics
SQL Statistics
Top 10 SQL Comparison by Execution Time
Top 10 SQL Comparison by CPU Time
Top 10 SQL Comparison by Buffer Gets
Top 10 SQL Comparison by Physical Reads
Top 10 SQL Comparison by Executions
Top 10 SQL Comparison by Parse Calls
Complete List of SQL Text
Instance Activity Statistics
Key Instance Activity Statistics
Other Instance Activity Statistics
I/O Statistics
Tablespace I/O Statistics
Top 10 File Comparison by I/O
Top 10 File Comparison by Read Time
Top 10 File Comparison by Buffer Waits
Advisory Statistics
PGA Aggregate Summary
PGA Aggregate Target Statistics
Wait Statistics
Buffer Wait Statistics
Enqueue Activity
Undo Segment Summary
Latch Statistics
Segment Statistics
Top 5 Segments Comparison by Logical Reads
In-Memory Segment Statistics
Dictionary Cache Statistics
Library Cache Statistics
Memory Statistics
Process Memory Summary
SGA Memory Summary
SGA Breakdown Difference
Streams Statistics
Supplemental Information in the AWR Compare Periods Report
init.ora Parameters
Complete List of SQL Text
9
Analyzing Sampled Data
About Active Session History
Generating Active Session History Reports
User Interfaces for Generating ASH Reports
Generating an ASH Report Using the Command-Line Interface
Generating an ASH Report on the Local Database Instance
Generating an ASH Report on a Specific Database Instance
Generating an ASH Report for Oracle RAC
Interpreting Results from Active Session History Reports
Top Events
Top User Events
Top Background Events
Top Event P1/P2/P3
Load Profile
Top Service/Module
Top Client IDs
Top SQL Command Types
Top Phases of Execution
Top SQL
Top SQL with Top Events
Top SQL with Top Row Sources
Top SQL Using Literals
Top Parsing Module/Action
Complete List of SQL Text
Top PL/SQL
Top Java
Top Sessions
Top Sessions
Top Blocking Sessions
Top Sessions Running PQs
Top Objects/Files/Latches
Top DB Objects
Top DB Files
Top Latches
Activity Over Time
10
Instance Tuning Using Performance Views
Instance Tuning Steps
Define the Problem
Examine the Host System
CPU Usage
Non-Oracle Processes
Oracle Processes
Oracle Database CPU Statistics
Interpreting CPU Statistics
Identifying I/O Problems
Identifying I/O Problems Using V$ Views
Identifying I/O Problems Using Operating System Monitoring Tools
Identifying Network Issues
Examine the Oracle Database Statistics
Setting the Level of Statistics Collection
Wait Events
Dynamic Performance Views Containing Wait Event Statistics
System Statistics
Segment-Level Statistics
Implement and Measure Change
Interpreting Oracle Database Statistics
Examine Load
Using Wait Event Statistics to Drill Down to Bottlenecks
Table of Wait Events and Potential Causes
Additional Statistics
Wait Events Statistics
Changes to Wait Event Statistics from Past Releases
buffer busy waits
db file scattered read
db file sequential read
direct path read and direct path read temp
direct path write and direct path write temp
enqueue (enq:) waits
events in wait class other
free buffer waits
Idle Wait Events
latch events
log file parallel write
library cache pin
library cache lock
log buffer space
log file switch
log file sync
rdbms ipc reply
SQL*Net Events
Tuning Instance Recovery Performance: Fast-Start Fault Recovery
About Instance Recovery
Cache Recovery (Rolling Forward)
Transaction Recovery (Rolling Back)
Checkpoints and Cache Recovery
Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
Practical Values for FAST_START_MTTR_TARGET
Reducing Checkpoint Frequency to Optimize Run-Time Performance
Monitoring Cache Recovery with V$INSTANCE_RECOVERY
Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
Calibrate the FAST_START_MTTR_TARGET
Determine the Practical Range for FAST_START_MTTR_TARGET
Determining Lower Bound for FAST_START_MTTR_TARGET: Scenario
Determining Upper Bound for FAST_START_MTTR_TARGET
Selecting Preliminary Value for FAST_START_MTTR_TARGET
Evaluate Different Target Values with MTTR Advisor
Enabling MTTR Advisor
Using MTTR Advisor
Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVICE
Determine the Optimal Size for Redo Logs
Part III Tuning Database Memory
11
Database Memory Allocation
About Database Memory Caches and Other Memory Structures
Database Memory Management Methods
Automatic Memory Management
Automatic Shared Memory Management
Manual Shared Memory Management
Automatic PGA Memory Management
Manual PGA Memory Management
Using Automatic Memory Management
Monitoring Memory Management
12
Tuning the System Global Area
Using Automatic Shared Memory Management
User Interfaces for Setting the SGA_TARGET Parameter
Setting the SGA_TARGET Parameter in Oracle Enterprise Manager
Setting the SGA_TARGET Parameter in the Command-Line Interface
Setting the SGA_TARGET Parameter
Enabling Automatic Shared Memory Management
Disabling Automatic Shared Memory Management
Sizing the SGA Components Manually
SGA Sizing Unit
Maximum Size of the SGA
Application Considerations
Operating System Memory Use
Reduce Paging
Fit the SGA into Main Memory
Viewing SGA Memory Allocation
Locking the SGA into Physical Memory
Allow Adequate Memory to Individual Users
Iteration During Configuration
Monitoring Shared Memory Management
Configuring the In-Memory Column Store
About the In-Memory Column Store
Performance Benefits of Using the In-Memory Column Store
Estimating the Required Size of the In-Memory Column Store
Sizing the In-Memory Column Store
13
Tuning the Database Buffer Cache
About the Database Buffer Cache
Configuring the Database Buffer Cache
Using the V$DB_CACHE_ADVICE View
Calculating the Buffer Cache Hit Ratio
Interpreting the Buffer Cache Hit Ratio
Increasing Memory Allocated to the Database Buffer Cache
Reducing Memory Allocated to the Database Buffer Cache
Configuring Multiple Buffer Pools
Considerations for Using Multiple Buffer Pools
Random Access to Large Segments
Oracle Real Application Cluster Instances
Using Multiple Buffer Pools
Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools
Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools
Examining the Buffer Cache Usage Pattern
Examining the Buffer Cache Usage Pattern for All Segments
Examining the Buffer Cache Usage Pattern for a Specific Segment
Configuring the KEEP Pool
Configuring the RECYCLE Pool
Configuring the Redo Log Buffer
Sizing the Redo Log Buffer
Using Redo Log Buffer Statistics
Configuring the Database Caching Mode
Default Database Caching Mode
Force Full Database Caching Mode
Determining When to Use Force Full Database Caching Mode
Verifying the Database Caching Mode
14
Tuning the Shared Pool and the Large Pool
About the Shared Pool
Benefits of Using the Shared Pool
Shared Pool Concepts
Library Cache Concepts
Data Dictionary Cache Concepts
SQL Sharing Criteria
Using the Shared Pool
Use Shared Cursors
Use Single-User Logon and Qualified Table Reference
Use PL/SQL
Avoid Performing DDL Operations
Cache Sequence Numbers
Control Cursor Access
Controlling Cursor Access Using OCI
Controlling Cursor Access Using Oracle Precompilers
Controlling Cursor Access Using SQLJ
Controlling Cursor Access Using JDBC
Controlling Cursor Access Using Oracle Forms
Maintain Persistent Connections
Configuring the Shared Pool
Sizing the Shared Pool
Using Library Cache Statistics
Using the V$LIBRARYCACHE View
Calculating the Library Cache Hit Ratio
Viewing the Amount of Free Memory in the Shared Pool
Using Shared Pool Advisory Statistics
About the V$SHARED_POOL_ADVICE View
About the V$LIBRARY_CACHE_MEMORY View
About V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_CACHE_MEMORY
Using Dictionary Cache Statistics
Increasing Memory Allocated to the Shared Pool
Reducing Memory Allocated to the Shared Pool
Deallocating Cursors
Caching Session Cursors
About the Session Cursor Cache
Enabling the Session Cursor Cache
Sizing the Session Cursor Cache
Sharing Cursors
About Cursor Sharing
Forcing Cursor Sharing
Keeping Large Objects to Prevent Aging
Configuring the Reserved Pool
Sizing the Reserved Pool
Increasing Memory Allocated to the Reserved Pool
Reducing Memory Allocated to the Reserved Pool
Configuring the Large Pool
Configuring the Large Pool for Shared Server Architecture
Configuring the Large Pool for Parallel Query
Sizing the Large Pool
Limiting Memory Use for User Sessions
Reducing Memory Use Using Three-Tier Connections
15
Tuning the Result Cache
About the Result Cache
Server Result Cache Concepts
Benefits of Using the Server Result Cache
Understanding How the Server Result Cache Works
How Results are Retrieved in a Query
How Results are Retrieved in a View
Client Result Cache Concepts
Benefits of Using the Client Result Cache
Understanding How the Client Result Cache Works
Configuring the Result Cache
Configuring the Server Result Cache
Sizing the Server Result Cache Using Initialization Parameters
Managing the Server Result Cache Using DBMS_RESULT_CACHE
Viewing Memory Usage Statistics for the Server Result Cache
Flushing the Server Result Cache
Configuring the Client Result Cache
Setting the Result Cache Mode
Requirements for the Result Cache
Read Consistency Requirements
Query Parameter Requirements
Restrictions for the Result Cache
Specifying Queries for Result Caching
Using SQL Result Cache Hints
Using the RESULT_CACHE Hint
Using the NO_RESULT_CACHE Hint
Using the RESULT_CACHE Hint in Views
Using Result Cache Table Annotations
Using the DEFAULT Table Annotation
Using the FORCE Table Annotation
Monitoring the Result Cache
16
Tuning the Program Global Area
About the Program Global Area
Work Area Sizes
Sizing the Program Global Area
Configuring Automatic PGA Memory Management
Setting the Initial Value for PGA_AGGREGATE_TARGET
Monitoring Automatic PGA Memory Management
Using the V$PGASTAT View
Using the V$PROCESS View
Using the V$PROCESS_MEMORY View
Using the V$SQL_WORKAREA_HISTOGRAM View
Using the V$WORKAREA_ACTIVE View
Using the V$SQL_WORKAREA View
Tuning PGA_AGGREGATE_TARGET
Enabling Automatic Generation of PGA Performance Advisory Views
Using the V$PGA_TARGET_ADVICE View
Using the V$PGA_TARGET_ADVICE_HISTOGRAM View
Using the V$SYSSTAT and V$SESSTAT Views
Tutorial: How to Tune PGA_AGGREGATE_TARGET
Limiting the Size of the Program Global Area
About PGA_AGGREGATE_LIMIT
Setting PGA_AGGREGATE_LIMIT
Part IV Managing System Resources
17
I/O Configuration and Design
About I/O
I/O Configuration
Lay Out the Files Using Operating System or Hardware Striping
Requested I/O Size
Concurrency of I/O Requests
Alignment of Physical Stripe Boundaries with Block Size Boundaries
Manageability of the Proposed System
Manually Distributing I/O
When to Separate Files
Tables, Indexes, and TEMP Tablespaces
Redo Log Files
Archived Redo Logs
Three Sample Configurations
Stripe Everything Across Every Disk
Move Archive Logs to Different Disks
Move Redo Logs to Separate Disks
Oracle Managed Files
Choosing Data Block Size
Reads
Writes
Block Size Advantages and Disadvantages
I/O Calibration Inside the Database
Prerequisites for I/O Calibration
Running I/O Calibration
I/O Calibration with the Oracle Orion Calibration Tool
Introduction to the Oracle Orion Calibration Tool
Orion Test Targets
Orion for Oracle Administrators
Getting Started with Orion
Orion Input Files
Orion Parameters
Orion Required Parameter
Orion Optional Parameters
Orion Command Line Samples
Orion Output Files
Orion Sample Output Files
Orion Troubleshooting
18
Managing Operating System Resources
Understanding Operating System Performance Issues
Using Operating System Caches
Asynchronous I/O
FILESYSTEMIO_OPTIONS Initialization Parameter
Limiting Asynchronous I/O in NFS Server Environments
Memory Usage
Buffer Cache Limits
Parameters Affecting Memory Usage
Using Operating System Resource Managers
Resolving Operating System Issues
Performance Hints on UNIX-Based Systems
Performance Hints on Windows Systems
Performance Hints on HP OpenVMS Systems
Understanding CPU
Resolving CPU Issues
Finding and Tuning CPU Utilization
Checking Memory Management
Paging and Swapping
Oversize Page Tables
Checking I/O Management
Checking Network Management
Checking Process Management
Scheduling and Switching
Context Switching
Starting New Operating System Processes
Managing CPU Resources Using Oracle Database Resource Manager
Managing CPU Resources Using Instance Caging
Index
Scripting on this page enhances content navigation, but does not change the content in any way.