Course Content
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