Detailed Course Outline
Introduction
- This lesson introduces the Performance Tuning course objectives and agenda
Basic Tuning Tools
- Monitoring tools overview
- Enterprise Manager
- V$ Views, Statistics and Metrics
- Wait Events
Using Automatic Workload Repository
- Managing the Automatic Workload RepositoryCreate AWR Snapshots
- Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15)
Defining Problems
- Defining the Problem
- Limit the Scope & Setting the Priority
- Top SQL Reports
- Common Tuning Problems & Tuning During the Life Cycle
- ADDM Tuning Session
- Performance Versus Business Requirements
- Performance Tuning Resources & Filing a Performance Service Request
- Monitoring and Tuning Tools: Overview
Using Metrics and Alerts
- Metrics, Alerts, and Baselines
- Limitation of Base Statistics & Typical Delta Tools
- Oracle Database 11g Solution: Metrics
- Benefits of Metrics
- Viewing Metric History Information & Vsing EM to View Metric Details
- Statistic Histograms & Histogram Views
- Database Control Usage Model & Setting Thresholds
- Server-Generated Alerts, Creating and Testing an Alert & Metric and Alert Views
Using Baselines
- Comparative Performance Analysis with AWR Baselines
- Automatic Workload Repository Baselines
- Moving Window Baseline
- Baselines in Performance Page Settings & Baseline Templates
- AWR Baselines & Creating AWR Baselines
- Managing Baselines with PL/SQL & Baseline Views
- Performance Monitoring and Baselines & Defining Alert Thresholds Using a Static Baseline
- Using EM to Quickly Configure & Changing Adaptive Threshold Settings
Using AWR Based Tools
- Automatic Maintenance Tasks
- ADDM Performance Monitoring
- Active Session History: Overview
Monitoring an Application
- What Is a Service? Service Attributes & Service Types
- Creating Services & Managing Services in a Single-Instance Environment
- Everything Switches to Services.
- Using Services with Client Applications & Using Services with the Resource Manager
- Services and Resource Manager with EM & Using Services with the Scheduler
- Using Services with Parallel Operations & Metric Thresholds
- Service Aggregation and Tracing & Service Aggregation Configuration.
- Client Identifier Aggregation and Tracing & Service Performance Views
Identifying Problem SQL Statements
- SQL Statement Processing Phases & Role of the Oracle Optimizer
- Identifying Bad SQL, Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15) & TOP SQL Reports
- What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
- DBMS_XPLAN Package: Overview & EXPLAIN PLAN Command
- Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
- SQL*Plus AUTOTRACE & SQL Trace Facility
- How to Use the SQL Trace Facility
- Generate an Optimizer Trace
Influencing the Optimizer
- Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
- Using Hints, Optimizer Statistics & Extended Statistics
- Controlling the Behavior of the Optimizer with Parameters
- Enabling Query Optimizer Features & Influencing the Optimizer Approach
- Optimizing SQL Statements, Access Paths & Choosing an Access Path
- Join & Sort Operations
- How the Query Optimizer Chooses Execution Plans for Joins
- Reducing the Cost
Using SQL Performance Analyzer
- Real Application Testing: Overview & Use Cases
- SQL Performance Analyzer: Process & Capturing the SQL Workload
- Creating a SQL Performance Analyzer Task & SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
- Optimizer Upgrade Simulation & SQL Performance Analyzer Task Page
- Comparison Report & Comparison Report SQL Detail
- Tuning Regressing Statements & Preventing Regressions
- Parameter Change Analysis & Guided Workflow Analysis
- SQL Performance Analyzer: PL/SQL Example & Data Dictionary Views
SQL Performance Management
- Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
- Statistic Gathering Options & Setting Statistic Preferences
- Restore Statistics
- Deferred Statistics Publishing: Overview & Example
- Automatic SQL Tuning: Overview
- SQL Tuning Advisor: Overview
- Using the SQL Access Advisor
- SQL Plan Management: Overview
Using Database Replay
- The Big Picture & System Architecture
- Capture & Replay Considerations
- Replay Options & Analysis
- Database Replay Workflow in Enterprise Manager
- Packages and Procedures
- Data Dictionary Views: Database Replay
- Database Replay: PL/SQL Example
- Calibrating Replay Clients
Tuning the Shared Pool
- Shared Pool Architecture & Operation
- The Library Cache & Latch and Mutex
- Diagnostic Tools for Tuning the Shared Pool
- Avoiding Hard & Soft Parses
- Sizing the Shared Pool & Avoiding Fragmentation
- Data Dictionary Cache & SQL Query Result Cache
- UGA and Oracle Shared Server
- Large Pool & Tuning the Large Pool
Tuning the Buffer Cache
- Oracle Database Architecture: Buffer Cache
- Database Buffers
- Buffer Hash Table for Lookups
- Working Sets
- Buffer Cache Tuning Goals and Techniques
- Buffer Cache Performance Symptoms & Solutions
- Automatically Tuned Multiblock Reads
- Flushing the Buffer Cache (for Testing Only)
Tuning PGA and Temporary Space
- SQL Memory Usage & Performance Impact
- SQL Memory Manager
- Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
- Monitoring & Tuning SQL Memory Usage
- PGA Target Advice Statistics & Histograms
- Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
- Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
- Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table
Automatic Memory Management
- Oracle Database Architecture, Dynamic SGA & Memory Advisories
- Granule & Manually Adding Granules to Components
- Increasing the Size of an SGA Component, SGA Sizing Parameters & Manually Resizing Dynamic SGA Parameters
- Automatic Shared Memory Management & Memory Broker Architecture
- Behavior of Auto-Tuned & Manually TunedSGA Parameters
- Using the V$PARAMETER View & Resizing SGA_TARGET
- Disabling, Configuring & Monitoring Automatic Shared Memory Management (ASMM)
- Automatic Memory Management
Tuning Segment Space Usage
- Space and Extent Management & Locally Managed Extents
- How Table Data Is Stored & Anatomy of a Database Block
- Minimize Block Visits
- The DB_BLOCK_SIZE Parameter
- Small & Large Block Size: Considerations
- Block Allocation, Free Lists & Block Space Management with Free Lists
- Automatic Segment Space Management
- Migration and Chaining, Shrinking Segments & Table Compression: Overview
Tuning I/O
- I/O Architecture, File System Characteristics, I/O Modes & Direct I/O
- Bandwidth Versus Size & Important I/O Metrics for Oracle Databases
- I/O Calibration and Enterprise Manager, I/O Calibration and the PL/SQL Interface & I/O Statistics and Enterprise Manager
- Stripe and Mirror Everything
- Using RAID
- I/O Diagnostics
- Database I/O Tuning
- What Is Automatic Storage Management?
Performance Tuning Summary
- Best practices identified throughout the course
- Summarize the performance tuning methodology
Appendix B: Using Statspack
- Installing Statspack
- Capturing Statspack Snapshots
- Reporting with Statspack
- Statspack Considerations
- Statspack and AWR Reports
- Reading a Statspack Report
- Statspack and AWR