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 and Tuning During the Life Cycle
- ADDM Tuning Session
- Performance Versus Business Requirements
- Performance Tuning Resources and Filing a Performance Service Request
- Monitoring and Tuning Tools: Overview
Using Metrics and Alerts
- Metrics, Alerts, and Baselines
- Limitation of Base Statistics and Typical Delta Tools
- Oracle Database 11g Solution: Metrics
- Benefits of Metrics
- Viewing Metric History Information and Vsing EM to View Metric Details
- Statistic Histograms & Histogram Views
- Database Control Usage Model and Setting Thresholds
- Server-Generated Alerts, Creating and Testing an Alert and Metric and Alert Views
Using Baselines
- Comparative Performance Analysis with AWR Baselines
- Automatic Workload Repository Baselines
- Moving Window Baseline
- Baselines in Performance Page Settings and Baseline Templates
- AWR Baselines and Creating AWR Baselines
- Managing Baselines with PL/SQL and 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 and Service Types
- Creating Services and Managing Services in a Single-Instance Environment
- Everything Switches to Services.
- Using Services with Client Applications and 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 and Service Aggregation Configuration.
- Client Identifier Aggregation and Tracing & Service Performance Views
Identifying Problem SQL Statements
- SQL Statement Processing Phases and 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 & Uses of Execution Plans
- DBMS_XPLAN Package: Overview and EXPLAIN PLAN Command
- Reading an Execution Plan, Using the V$SQL_PLAN View and 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 and Changing Optimizer Behavior
- Using Hints, Optimizer Statistics and Extended Statistics
- Controlling the Behavior of the Optimizer with Parameters
- Enabling Query Optimizer Features and Influencing the Optimizer Approach
- Optimizing SQL Statements, Access Paths & Choosing an Access Path
- Join and Sort Operations
- How the Query Optimizer Chooses Execution Plans for Joins
- Reducing the Cost
Using SQL Performance Analyzer
- Real Application Testing: Overview and Use Cases
- SQL Performance Analyzer: Process and Capturing the SQL Workload
- Creating a SQL Performance Analyzer Task and SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
- Optimizer Upgrade Simulation and SQL Performance Analyzer Task Page
- Comparison Report and Comparison Report SQL Detail
- Tuning Regressing Statements and Preventing Regressions
- Parameter Change Analysis andGuided Workflow Analysis
- SQL Performance Analyzer: PL/SQL Example and Data Dictionary Views
SQL Performance Management
- Maintaining SQL Performance and Optimizer Statistics and Automated Maintenance Tasks
- Statistic Gathering Options and 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 and 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
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 and Avoiding Fragmentation
- Data Dictionary Cache & SQL Query Result Cache
- UGA and Oracle Shared Server
- Large Pool and 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 and Solutions
- Automatically Tuned Multiblock Reads
- Flushing the Buffer Cache (for Testing Only)
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 & 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 and Tablespace Option for Creating Temporary Table
Automatic Memory Management
- Oracle Database Architecture, Dynamic SGA and Memory Advisories
- Granule and 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 TunedSGA Parameters
- Using the V$PARAMETER View and Resizing SGA_TARGET
- Disabling, Configuring & Monitoring Automatic Shared Memory Management (ASMM)
- Automatic Memory Management
Tuning Segment Space Usage
- Space and Extent Management and Locally Managed Extents
- How Table Data Is Stored and 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: Overview
Tuning I/O
- I/O Architecture, File System Characteristics, I/O Modes & Direct I/O
- Bandwidth Versus Size and 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
