Oracle Database 11g Release 2 DBA Performance & Tuning

Course Code: O11GDBAPT

Duration: 5 days

 
 

Oracle Database 11g Release 2 DBA Performance & Tuning Course Overview

This Oracle Database 11g Release 2 DBA Performance & Tuning course introduces the DBA to the main concepts of Oracle Database performance tuning and management. DBAs will gain practical experience in analyzing and tuning database performance.

The Oracle Database 11g Release 2 DBA Performance & Tuning course is suitable for users of Oracle Database 11g and Oracle Database 12c.

Delegates on the Oracle Database 11g Release 2 DBA Performance & Tuning course will learn to identify tuning contentions and how to take corrective action. By the end of the course, delegates will be able to:

  • Use tuning goals and methodologies
  • Detect and tune common database performance problems
  • Use the database advisors to proactively tune a database
  • Use the AWR and ADDM tools to tune a database
  • Use Enterprise Manager to monitor an Oracle database

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Course Objectives

The objective of the Oracle Database 11g Release 2 DBA Performance & Tuning course is to provide the skills needed to monitor and tune an Oracle Database 12c or Oracle Database 11g.

Who will the Course Benefit?

The Oracle Database 11g Release 2 DBA Performance & Tuning course is suitable for database administrators and technical support staff who are required to monitor and tune an Oracle Database 12c or Oracle Database 11g.

Skills Gained

The delegate will learn and acquire skills as follows:

  • Tuning database applications for optimal performance
  • Identifying and resolving common tuning problems
  • Using diagnostic sources
  • Managing database statistics
  • Identifying and correcting problem SQL statements
  • Examining the execution plan of a SQL statement using Explain Plan
  • Examining the efficiency of SQL statements using SQL trace and autotrace
  • Using tools for monitoring and diagnosing SQL performance issues
  • Tuning memory components
  • Tuning the SGA and Shared Pool
  • Tuning the data block buffers
  • Using Automatic Memory Management
  • Setting the PGA and temporary space
  • Managing disk I/O
  • Tuning block space
  • Monitoring statistics and wait events
  • Using metrics, alerts and baselines
  • Using AWR and ADDM
  • Using Database Replay

Oracle Database 11g Release 2 DBA Performance & Tuning Training Course

Course Introduction

  • Administration and Course Materials
  • Course Structure and Agenda
  • Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

  • Tuning Overview of Oracle Database Tuning
  • Application Developer Tuning Responsibilities
  • Oracle DBA Tuning Responsibilities
  • Oracle Tuning Process
  • Plan a Routine Monitoring Regime
  • Setting Suitable Goals

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

  • Overview of SQL Statement Tuning
  • Tools to Assist in SQL Tuning
  • Use Explain Plan, Autotrace and SQL Trace to Examine the Execution of a SQL Statement
  • Interpreting a SQL Trace

Session 3: THE SQL OPTIMIZER

  • The SQL Optimizer
  • Statement Transformation
  • The Optimizer_Mode Initialization Parameter
  • Cost Based Optimizer
  • Managing Statistics with DBMS_STATS
  • Automatic Statistics Gathering
  • Dynamic Statistics
  • Online Statistics Gathering for Bulk Loads

Session 4: SORTS

  • How Oracle Processes Sorts
  • Temporary Disk Space Assignment
  • SQL Operations that Use Sorts

Session 5: INDEXES

  • Index Overview
  • Selecting Suitable Columns for an Index
  • B*Tree Indexes
  • Rebuild an Index
  • Composite Indexes
  • Descending Indexes
  • Access Paths with Indexes
  • Index Scans
  • Conditions That Stop Indexes Being Used
  • Parameters that Affect Optimizer Index Choice

Oracle Database 11g Release 2 DBA Performance & Tuning Training Course

Session 6: ADVANCED INDEXES

  • Bitmap Indexes
  • Key Compressed Indexes
  • Index Organized Tables
  • Function Based Indexes
  • Invisible Indexes

Session 7: JOIN OPERATIONS

  • Understand Access Paths
  • Joining Tables
  • Nested Loops Join
  • Merge Join
  • Cluster Join
  • Hash Join
  • Anti Join and Semi Join
  • Outer Joins
  • Star Join
  • Improve Optimization with Different Access Paths

Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER

  • Overview of the DBMS_SQL_TUNE Package
  • Using the SQL Tuning Advisor with SQL Developer

Session 9: SEQUENCES AND VIEWS

  • Sequence Caching
  • Views
  • View Merging
  • Inline Views

Session 10: USING HINTS

  • Using Hints to Influence Execution Plan
  • Optimization Mode and Goals
  • Access Methods
  • Query Transformations
  • Join Orders
  • Join Operations
  • Hint Examples

Session 11: MISCELLANEOUS

  • Tips for Avoiding Problematic Queries
  • Array Size
  • The Shared Pool
  • The WITH Clause
  • Bind Variable Usage
  • Result Caching
  • Some Miscellaneous PL/SQL Issues

Oracle Database 11g Release 2 DBA Performance & Tuning Training Course

Session 12: BASIC TUNING DIAGNOSTICS

  • Performance Tuning Diagnostics, Features, and Tools
  • DB Time
  • CPU and Wait Time Tuning Dimensions
  • Time Model
  • Dynamic Performance Views
  • Statistics
  • Wait Events
  • Log Files and Trace Files

Session 13: REDUCE THE COST OF SQL OPERATIONS

  • Maintain Tables
  • Reorganize Tables
  • Manage Extents
  • Compress Tables
  • Row Chaining and Row Migration
  • Segment Shrink

Session 14: THE SQL PERFORMANCE ANALYZER

  • An Overview of the SQL Performance Analyzer
  • Usage of the SQL Performance Analyzer
  • Capture a SQL Workload
  • Create a SQL Performance Analyzer Task
  • Generate Comparison Reports
  • Tune Regressed Statements
  • Guided Workflow Analysis
  • SQL Performance Analyzer Views

Session 15: SQL PERFORMANCE MANAGEMENT

  • Maintenance of the Optimizer Statistics
  • Gather Statistics Options
  • Defer Publishing Statistics
  • Automatic SQL Tuning
  • The SQL Tuning Advisor
  • The SQL Access Advisor
  • SQL Plan Management

Session 16: TUNE THE SHARED POOL

  • Overview of the Shared Pool Architecture
  • Latches and Mutexes
  • Diagnostic Tools for Tuning the Shared Pool
  • Avoiding Hard Parses
  • Size the Shared Pool
  • Avoiding Fragmentation of the Shared Pool
  • The Data Dictionary Cache
  • The SQL Query Result Cache

Oracle Database 11g Release 2 DBA Performance & Tuning Training Course

Session 17: TUNE THE BUFFER CACHE

  • Overview of the Database Buffer Cache Architecture
  • Buffer Cache Tuning Goals and Techniques
  • Buffer Cache Performance Symptoms and Solutions
  • Buffer Cache Advisor
  • Database Smart Flash Cache
  • Full Database Caching
  • When to Flush the Buffer Cache

Session 18: TUNE PGA AND TEMPORARY SPACE

  • Overview of the PGA
  • SQL Memory Usage
  • Automatic PGA Memory Mode Configuration
  • PGA Target Advice Statistics and Histograms
  • Automatic PGA and AWR Reports
  • Temporary Tablespace Management
  • Temporary Tablespace Group
  • Multiple Temporary Tablespaces
  • Monitoring Temporary Tablespaces
  • Temporary Tablespace Shrink
  • Data Dictionary Views and Sort Segments

Session 19: AUTOMATIC MEMORY MANAGEMENT

  • Overview of Automatic Shared Memory Management
  • Dynamic SGA
  • Parameters for Sizing the SGA
  • Enable and Disable Automatic Shared Memory Management
  • Use the SGA Advisor
  • Overview of Automatic Memory Management
  • Enable and Disable Automatic Memory Management
  • Monitor Automatic Memory Management

Session 20: CREATE AND USE SNAPSHOTS AND BASELINES WITH THE AUTOMATIC WORKLOAD REPOSITORY

  • An Overview of the Automatic Workload Repository
  • AWR Data
  • Enterprise Manager and the AWR
  • Create and Compare Snapshots
  • Examine AWR Reports
  • Create and Compare Baselines
  • Moving Window Baseline
  • Baseline Templates
  • Baselines Views
  • Performance Monitoring and Baselines
  • Define Alert Thresholds Using a Static Baseline
  • Configure Adaptive Thresholds

Session 21: DEFINE THE SCOPE OF PERFORMANCE ISSUES

  • An Overview of Monitoring and Tuning Tools
  • Common Tuning Problems
  • Define the Problem and Limiting the Scope
  • Set the Priority of a Performance Issue
  • View the Top SQL Reports
  • Tune During the Life Cycle of a problem
  • Generate and Analyze ADDM Reports
  • Performance Tuning Resources

Session 22: USE METRICS AND ALERTS

  • An Overview of Metrics and Alerts
  • The Benefits and Limitations of Metrics and Alerts
  • System Generated, Threshold Generated and Event Based Alerts
  • Set Thresholds
  • View Metric History Information
  • View Histograms
  • Metric and Alert Views

Oracle Database 11g Release 2 DBA Performance & Tuning Training Course

Session 23: USE AWR-BASED TOOLS

  • Automatic Maintenance Tasks
  • ADDM Performance Monitoring
  • Active Session History
  • Generate an ASH Report
  • Real-time ADDM

Session 24: REAL TIME DATABASE OPERATION MONITORING

  • Overview of Real Time Database Operation Monitoring
  • Database Operation Concepts
  • Define a Database Operation
  • Enable the Monitoring of Database Operations
  • Identify, Start and Complete a Database Operation
  • Monitor the Progress of a Database Operation
  • Database Operation Views
  • Database Operation Tuning

Session 25: MONITOR APPLICATIONS

  • The Attributes and Types of Services
  • Create Services
  • Use Services with Client Applications
  • Use Services with the Resource Manager
  • Use Services the Oracle Scheduler
  • Services and Metric Thresholds
  • Use Aggregation of Services with Tracing
  • The Top Services Performance Page

Session 26: DATABASE REPLAY

  • Overview of Database Replay Architecture
  • Use Enterprise Manager to Capture and Replay a Workload
  • Use the Database Replay Packages to Capture and Replay a Workload
  • Capture a Workload
  • Pre-process the Captured Workload
  • Configure Replay Clients
  • Replay the Workload
  • Replay Workload Options
  • Analyze the Replayed Workload
  • Database Replay Views

Session 27: TUNE DATABASE I/O

  • An Overview of Database I/O Management
  • I/O Architecture and Modes
  • Important I/O Metrics for Oracle Databases
  • I/O Calibration
  • I/O Statistics and Diagnostics
  • Database I/O Tuning
  • Automatic Storage Management (ASM)

Session 28: A SUMMARY OF ORACLE PERFORMANCE TUNING

  • The Potential Impact of Initialization Parameters on Performance
  • Initially Size Memory for a Database
  • Recommended Best Practices for Different Types of Tablespaces
  • Determine and Use Block Sizes
  • Size the Redo Log Buffer and the Redo Log Files
  • Configure Automatic Statistics Gathering
Notes:
  • Course technical content is subject to change without notice.
  • Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Requirements

A working knowledge of SQL, PL/SQL and Oracle Database administration is required. These skills can be obtained by attendance on the pre-requisite Oracle SQL, Oracle PL/SQL and Oracle Database 11g Release 2 DBA - Part I courses.

This course is run on a Linux operating system, a basic knowledge of UNIX/Linux is recommended but is not essential.

Pre-Requisite Courses

Course Reviews

Tutor extremely knowledgeable and experienced. Inspiring!

Simona - OSS Engineer - Communications


This is my third course attended in 1 year ! Attending 4th one next month.

Imrane - Police


Course tutor was very flexible with time and allowed me to ask as many questions as I needed to. Throughout the course, she kept referring to my questions or linking my questions with current topic. This is the first course that I haven't fallen sleep!

Thang - Oracle DBA - Communications


Working with the practical's at the time of learning really helped to understand the subject. Also like the conversations referring to my own current environments.

Michael - IT Consultant


Course was good. Alan was spot on and precise.

Kalveer - Oracle DBA


I enjoyed the course. It was very imformative and well presented and will be very useful in my day to day work.

Gary - Database Administrator


Public Scheduled Events

Classroom & Live Virtual Instructor-Led Training

Duration: 5 days

Price: £2,250.00 exc. VAT 

Please contact us to review your requirements and schedule a date.

 

Live Virtual Classroom

 
Join live instructor-led classroom training from the comfort of your home or office.
All the convenience and benefits of the classroom experience without the hassle and costs of travel and accommodation.
 
 



Our Customers Include

 
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
 
 



Our Course Curriculum

 
 
 
 
Facebook LinkedIn Twitter Instagram E-Mail
 
+44 (0)20 7600 6116
Enquiries@StayAhead.com
Copyright © 2021 StayAhead Training Ltd
Cookies   /   Privacy Policy