This Oracle Database 11g Performance Tuning training starts with an unknown database that requires tuning. You'll then learn the steps a
DBA performs to identify problem areas, diagnose common problems and fix them.
Tune instance components, primarily using Instance parameters.
Benefits to YouEnsure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling smooth and rapid consolidation within your
Datacenter.
Course Methodology
The methodology used in the practices is primarily reactive. After
configuring monitoring tools and reviewing the available reports, you'll be presented with the Oracle architecture based on the SQL statement processing of SELECT and DML.
SQL Tuning
The SQL tuning section assumes that the DBA has little or no ability to
change the code. The DBA will influence the SQL performance with available tools, and will be introduced to various methods of
identifying the SQL statements that require tuning (as well as the diagnostic tools needed to find ways to change the performance).
This will include the use of statistics, outlines and profiles to influence the optimizer, adding and rebuilding indexes and using the SQL Advisors. This course introduces the DB Replay and SQL Performance Analyzer tools to help you test and minimize the impact of change.
Instance Tuning
Instance tuning uses the same general method of observing a problem,
diagnosing the problem and implementing a solution. The instance tuning lessons cover the details of major tunable components and
describe how you can influence the instance behavior. For each lesson, we will examine the relevant components of the architecture.
The class only discusses the architecture to the level required to understand the symptoms and solutions. More detailed explanations are left to other courses, reference material and Oracle documentation.
Final Lessons The last lesson of this course is a recap of the best practices
discovered in the previous lessons, along with miscellaneous recommendations. The goal is to finish the course with a best
practices list for students to take away.
Required Prerequisite
- Oracle Database 11g: Administration Workshop I Release 2
- Oracle Database 11g: Administration Workshop II Release 2
Audience
Course Topics
Introduction - This lesson introduces the Performance Tuning course objectives and agenda
Basic Tuning Tools- Monitoring tools overview
- Enterprise Manager
- Wait Events
- V$ Views, Statistics and Metrics
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)
Defining Problems- Performance Versus Business Requirements
- Limit the Scope & Setting the Priority
- Defining the Problem
- Monitoring and Tuning Tools: Overview
- Top
SQL Reports
- Common Tuning Problems & Tuning During the Life Cycle
- Performance Tuning Resources & Filing a Performance Service Request
- ADDM Tuning Session
Using Metrics and Alerts- 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
- Benefits of Metrics
- Oracle Database 11g Solution: Metrics
- Metrics,
Alerts, and Baselines
- Limitation of Base Statistics & Typical Delta Tools
Using Baselines- Comparative Performance Analysis with AWR Baselines
- Moving Window Baseline
- Managing Baselines with PL/SQL & Baseline Views
- Using EM to Quickly Configure & Changing Adaptive Threshold Settings
- Automatic Workload Repository Baselines
- Baselines in Performance Page Settings & Baseline Templates
- AWR
Baselines & Creating AWR Baselines
- Performance Monitoring and Baselines & Defining Alert Thresholds Using a
Static Baseline
Using AWR Based Tools- ADDM Performance Monitoring
- Active Session History: Overview
- Automatic Maintenance Tasks
Monitoring an Application - What Is a Service? Service Attributes & Service Types
- Creating Services & Managing Services in a Single-Instance Environment
- Using Services with Client Applications & Using Services with the Resource Manager
- Service Aggregation and Tracing & Service Aggregation Configuration.
- Services and Resource Manager with EM & Using Services with the Scheduler
- Using Services with Parallel Operations & Metric Thresholds
- Everything Switches to Services.
- Client Identifier Aggregation and Tracing & Service Performance Views
Identifying Problem SQL Statements- Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
- What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
- DBMS_XPLAN
Package: Overview & EXPLAIN PLAN Command
- 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
- How to Use the SQL Trace Facility
- SQL*Plus
AUTOTRACE & SQL Trace Facility
- Generate an Optimizer Trace
Influencing the Optimizer- Join & Sort Operations
- Enabling Query Optimizer Features & Influencing the Optimizer Approach
- Using Hints, Optimizer Statistics & Extended Statistics
- Controlling the Behavior of the Optimizer with Parameters
- Functions
of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
- How the Query Optimizer Chooses Execution Plans for Joins
- Reducing the Cost
- Optimizing SQL Statements, Access Paths & Choosing an Access Path
Using SQL Performance Analyzer- SQL Performance Analyzer: Process & Capturing the SQL Workload
- Comparison Report & Comparison Report SQL Detail
- SQL Performance Analyzer: PL/SQL Example & Data Dictionary Views
- Tuning Regressing Statements & Preventing Regressions
- Parameter Change Analysis & Guided Workflow Analysis
- Real Application Testing: Overview & Use Cases
- Creating a SQL Performance Analyzer Task & SPA (NF Lesson 9)
DBMS_SQLTUNE.CREATE_TUNING_TASK
- Optimizer Upgrade Simulation & SQL Performance Analyzer Task Page
SQL Performance Management- Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
- Statistic Gathering Options & Setting Statistic Preferences
- SQL Plan Management: Overview
- Automatic SQL Tuning: Overview
- Deferred Statistics Publishing: Overview & Example
- Using the SQL Access Advisor
- Restore Statistics
- SQL Tuning Advisor: Overview
Using Database Replay- The Big Picture & System Architecture
- Database Replay Workflow in Enterprise Manager
- Data
Dictionary Views: Database Replay
- Calibrating Replay Clients
- Database Replay: PL/SQL Example
- Replay
Options & Analysis
- Capture & Replay Considerations
- Packages and Procedures
Tuning the Shared Pool- Diagnostic Tools for Tuning the Shared Pool
- Shared Pool Architecture & Operation
- Sizing the Shared Pool & Avoiding Fragmentation
- Avoiding Hard & Soft Parses
- UGA
and Oracle Shared Server
- The Library Cache & Latch and Mutex
- Large Pool & Tuning the Large Pool
- Data
Dictionary Cache & SQL Query Result Cache
Tuning the Buffer Cache- Flushing the Buffer Cache (for Testing Only)
- Buffer
Cache Tuning Goals and Techniques
- Oracle Database Architecture: Buffer Cache
- Automatically
Tuned Multiblock Reads
- Working Sets
- Buffer Cache Performance Symptoms & Solutions
- Buffer
Hash Table for Lookups
- Database Buffers
Tuning PGA and Temporary Space- Monitoring & Tuning SQL Memory Usage
- PGA
Target Advice Statistics & Histograms
- Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
- SQL Memory Usage & Performance Impact
- Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
- Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table
- Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
- SQL Memory Manager
Automatic Memory Management- Oracle Database Architecture, Dynamic SGA & Memory Advisories
- Increasing the Size of an SGA Component, SGA Sizing Parameters & Manually Resizing Dynamic SGA Parameters
- Automatic Memory Management
- Disabling, Configuring & Monitoring Automatic Shared Memory Management (ASMM)
- Automatic Shared Memory Management & Memory Broker Architecture
- Behavior of Auto-Tuned & Manually TunedSGA Parameters
- Using the V$PARAMETER View & Resizing SGA_TARGET
- Granule & Manually Adding Granules to Components
Tuning Segment Space Usage- Space and Extent Management & Locally Managed Extents
- Block Allocation, Free Lists & Block Space Management with Free Lists
- The DB_BLOCK_SIZE Parameter
- Automatic Segment Space Management
- How
Table Data Is Stored & Anatomy of a Database Block
- Migration and Chaining, Shrinking Segments & Table Compression: Overview
- Minimize Block Visits
- Small & Large Block Size: Considerations
Tuning I/O- I/O Diagnostics
- 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
- What
Is Automatic Storage Management?
- I/O Architecture, File System Characteristics, I/O Modes & Direct
I/O
- Bandwidth Versus Size & Important I/O Metrics for Oracle Databases
- Database I/O Tuning
Performance Tuning Summary- Summarize the performance tuning methodology
- Best
practices identified throughout the course
Appendix B: Using Statspack- Statspack and AWR Reports
- Statspack Considerations
- Capturing Statspack Snapshots
- Statspack and AWR
- Installing Statspack
- Reading a Statspack Report
- Reporting with Statspack