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 & Monit
