Oracle Database 11g: Performance Tuning DBA Release 2 (11gR2-PERFORM)成都常州济南长沙武汉重庆

Oracle Database 11g: Performance Tuning DBA Releas
Oracle Database 11g: Performance Tuning DBA Release 2 (11gR2-PERFORM)培训
培训大纲：
1. Basic Tuning Tools
Monitoring Tools
Enterprise Manager
V$ Views, Statistics, and Metrics
Wait Events
2. Using Automatic Workload Repository
Managing the Automatic Workload Repository
Create AWR Snapshots
Real-Time SQL Monitoring (a 11.1 feature - new lesson in NF L-15)
3. 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 vs. Business Requirements
Performance Tuning Resources
Filing a Performance Service Request
Monitoring and Tuning Tools
4. 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
5. Using Baselines
Comparative Performance Analysis with AWR Baselines
AWR 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
6. Using AWR Based Tools
Automatic Maintenance Tasks
ADDM Performance Monitoring
Active Session History
7. Monitoring an Application
What Is a Service?
Service Attributes and 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 and Metric Thresholds
Service Aggregation and Tracing
Service Aggregation Configuration
Client Identifier Aggregation and Tracing
Service Performance Views
8. Identifying Problem SQL Statements
SQL Statement Processing Phases and the Role of the Oracle Optimizer
Identifying Bad SQL Real-Time SQL Monitoring (a 11.1 feature - new lesson in NF L-15), and TOP SQL Reports
What Is an Execution Plan? Methods for Viewing Execution Plans and Uses of Execution Plans
DBMS_XPLAN Package
EXPLAIN PLAN Command
Reading an Execution Plan, Using the V$SQL_PLAN View, and Querying the AWR
SQL*Plus AUTOTRACE and SQL Trace Facility
How to Use the SQL Trace Facility
Generate an Optimizer Trace
9. Influencing the Optimizer
Functions of the Query Optimizer, Selectivity, Cardinality and Cost
Changing Optimizer Behavior
Using Hints, Optimizer Statistics, and Extended Statistics
Controlling the Behavior of the Optimizer with Parameters
Enabling Query Optimizer Features
Influencing the Optimizer Approach
Optimizing SQL Statements, Access Paths, and Choosing an Access Path
Join and Sort Operations
How the Query Optimizer Chooses Execution Plans for Joins
Reducing the Cost
10. Using SQL Performance Analyzer
Real Application Testing
SQL Performance Analyzer: Process Capturing the SQL Workload
Creating a SQL Performance Analyzer and 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
11. SQL Performance Management
Maintaining SQL Performance and Optimizer Statistics
Automated Maintenance Tasks
Statistic Gathering Options
Setting Statistic Preferences
Restore Statistics
Deferred Statistics Publishing
Automatic SQL Tuning
SQL Tuning Advisor
Using the SQL Access Advisor
SQL Plan Management
12. Using Database Replay
The Big Picture
System Architecture
Capture and Replay Considerations
Replay Options and Analysis
Database Replay Workflow in Enterprise Manager
Packages and Procedures
Data Dictionary Views: Database Replay
Database Replay: PL/SQL Example
Calibrating Replay Clients
13. Tuning the Shared Pool
Shared Pool Architecture and Operation
The Library Cache, Latch, and Mutex
Diagnostic Tools for Tuning the Shared Pool
Avoiding Hard and 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
14. 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 and Solutions
Automatically Tuned Multiblock Reads
Flushing the Buffer Cache (for Testing Only)
15. Tuning PGA and Temporary Space
SQL Memory Usage and Performance Impact
SQL Memory Manager
Configuring Automatic PGA Memory and Setting PGA_AGGREGATE_TARGET Initially
Monitoring and Tuning SQL Memory Usage
PGA Target Advice Statistics and Histograms
Automatic PGA and Enterprise Manager
Automatic PGA and AWR Reports
Temporary Tablespace Management
Temporary Tablespace Shrink Tablespace Option for Creating Temporary Table
16. Automatic Memory Management
Oracle Database Architecture, Dynamic SGA, and Memory Advisories
Granule
Manually Adding Granules to Components
Increasing the Size of an SGA Component, SGA Sizing Parameters, and Manually Resizing Dynamic SGA Parameters
Automatic Shared Memory Management
Memory Broker Architecture
Behavior of Auto-Tuned and Manually Tuned SGA Parameters
Using the V$PARAMETER View
Resizing SGA_TARGET
Disabling, Configuring, and Monitoring Automatic Shared Memory Management (ASMM)
Automatic Memory Management
17. 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 and Large Block Size: Considerations
Block Allocation, Free Lists, and Block Space Management with Free Lists
Automatic Segment Space Management
Migration and Chaining, Shrinking Segments, and Table Compression
18. Tuning I/O
I/O Architecture, File System Characteristics, I/O Modes, and Direct I/O
Bandwidth vs. 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?
19. Performance Tuning Summary
Best Practices
Performance Tuning Methodology
20. 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