Oracle Performance Tuning
Exploring the Oracle Database Architecture
Introduction to Performance Tuning
• List the architectural components of Oracle Database
• 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
• 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.
• CPU.
• Memory
• I/O Subsystem.
• Network
• Software Components.
• Managing the User Interface
• Implementing Business Logic
• Managing User Requests and Resource Allocation
• Managing Data and Transactions
• Configuring the Right System Architecture for Your Requirements.
Application Design Principles • Simplicity In Application Design.
• Data Modelling
• Table and Index Design.
• Appending Columns to an Index or Using Index-Organized Tab
• Using a Different Index Type.
• B-Tree Indexes.
• Bitmap Indexes.
• Function-based Indexes
• Partitioned Indexes
• Reverse Key Indexes.
• 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, Modelling, and Implementation • Sizing Data.
• Estimating Workloads.
• Extrapolating from a Similar System
• Benchmarking.
• Application Modelling
• 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 Modelling.
• 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.
• Configuring Undo Space
• Sizing Redo Log Files
• Creating Subsequent Tablespaces
• Creating Permanent Tablespaces – Automatic Segment-Space
• Creating Temporary Tablespaces.
Creating and Maintaining Tables for Optimal Performance • Table Compression
• Estimating the Compression Factor
• Tuning to Achieve a Better Compression Ratio
• Using Attribute-Clustered Tables
• Reclaiming Unused Space.
• Indexing Data.
• Specifying Memory for Sorting Data.
Performance Considerations for Shared Servers • Identifying Contention Using the Dispatcher-Specific Views
• Reducing Contention for Dispatcher Processes.
• Identifying Contention for Shared Servers.
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.
• Creating Snapshots Using the Command-Line Interface.
• Dropping Snapshots
• Dropping Snapshots Using the Command-Line Interface
• Modifying Snapshot Settings.
• Modifying Snapshot Settings Using the Command-Line Interface
• Managing Baselines.
• User Interface for Managing Baselines.
• Creating a Baseline.
• Creating a Baseline Using the Command-Line Interface.
• Dropping a Baseline
• Dropping a Baseline Using the Command-Line Interface
• Renaming a Baseline
• Renaming a Baseline Using the Command-Line Interface
• Displaying Baseline Metrics
• Displaying Baseline Metrics Using the Command-Line Interface
• Resizing the Default Moving Window Baseline.
• Resizing the Default Moving Window Using the Command
• Managing Baseline Templates.
• User Interfaces for Managing Baseline Templates.
• Creating a Single Baseline Template
• Creating a Single Baseline Template Using the Command-Line Interface
• Creating a Repeating Baseline Template.
• Creating a Repeating Baseline Template Using the Command-Line Interface
• Dropping a Baseline Template
• Dropping a Baseline Template Using the Command-Line Interface
• Transporting Automatic Workload Repository Data.