Oracle Database: Performance Management and Tuning

Course Details
Code: OUDATA-PMT
Tuition (USD): $3,860.00 • Virtual (5 days)
$4,070.00 • Classroom (5 days)

The course starts with an unknown database that requires tuning. The lessons will proceed through the steps a DBA will perform to acquire the information needed to identify problem areas, to diagnose common problems, and remedy those problems. The methodology used in the practices is primarily reactive. After configuring monitoring tools, and reviewing the available reports, the student will be presented with the Oracle architecture based on the SQL statement processing of SELECT and DML.

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. The DBA will be introduced to various methods of identifying the SQL statements that require tuning, and the diagnostic tools needed to find ways to change the performance. This will include the use of statistics, and profiles to influence the optimizer, adding and rebuilding indexes, and using the SQL Advisors. A major task of DBA’s is to maintain SQL performance across changes. This course introduces the DB Replay, and SQL Performance Analyzer tools to help the DBA test and minimize the impact of change.

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 course 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 the Oracle documentation.

The last lesson of this course is a recap of the best practices discovered in the previous lessons, and miscellaneous recommendations. The goal is to finish the course with a best practices list for students to take away.

Learn To:

  • Use the Oracle tuning methodology
  • Use Oracle-supplied tools for monitoring and diagnosing SQL and instance tuning issues
  • Use database advisors to practively correct performance problems
  • Identify problem SQL statements
  • Tune SQL performance problems
  • Monitor instance performance by using Enterprise Manager
  • Tune instance components by primarily using instance parameters

Skills Gained

  • Use the Oracle Database tuning methodology appropriate to the available tools
  • Utilize database advisors to proactively tune an Oracle Database Instance
  • Use the tools based on the Automatic Workload Repository to tune the database
  • Diagnose and tune common SQL related performance problems
  • Diagnose and tune common Instance related performance problems
  • Use Enterprise Manager performance-related pages to monitor an Oracle Database

Prerequisites

  • Basic knowledge of Linux operating system
  • Familiarity with Oracle Database installation
  • A working knowledge of SQL and PL/SQL packages
  • Familiarity with Oracle Database configuration concepts
  • Basic understanding of Oracle Database architecture
  • Familiarity with basic database monitoring procedures

Course Details

Introduction

  • Types of Tuning
  • Tuning Methodology
  • Effective Tuning Goals
  • General Tuning Session

Basic Tuning Diagnostics

  • Performance Tuning Features and Tools
  • Top Timed Events
  • DB Time
  • CPU and Wait Time Tuning Dimensions
  • Time Model
  • Statistic Levels
  • Wait Events
  • Alert Log and Trace Files

Using Automatic Workload Repository

  • Automatic Workload Repository Data
  • AWR Administration
  • AWR in a Multitenant Architecture Database
  • Snapshots
  • Reports
  • Compare Periods
  • PDB-Level Snapshot Views

Defining the Scope of Performance Issues

  • Determining Tuning Priorities
  • Top SQL Repors
  • Common Tuning Problems
  • Tuning During the Life Cycle
  • ADDM Tuning Session
  • Performance Versus Business Requirements
  • Performance Tuning Resources

Using Metrics and Alerts

  • Typical Delta Tools
  • Oracle Database Metrics
  • Statistic Histograms
  • Server-Generated Alerts
  • Alert Usage Model
  • Setting Thresholds

Using Baselines

  • Comparative Performance Analysis with AWR Baselines
  • Types of Baselines
  • Creating AWR Baselines
  • Managing Baselines by Using the DBMS_WORKLOAD_REPOSITORY PL/SQL Package
  • Performance Monitoring and Baselines
  • Defining Alert Thresholds Using a Static Baseline
  • Configuring a Basic Set of Thresholds

Using AWR-Based Tools

  • Automatic Maintenance Tasks
  • ADDM Performance Monitoring
  • AWR and ADDM Behavior in a Multitenant Architecture Database
  • Using Compare Periods ADDM
  • Active Session History
  • Emergency Monitoring
  • Real-time ADDM

Real-Time Database Operation Monitoring

  • Scope of a Composite Database Operation
  • Database Operation Concepts
  • Identifying a Database Operation
  • Enabling Monitoring of Database Operations
  • Monitoring Load Database Operations
  • Reporting Database Operations by Using Functions
  • Database Operation Tuning

Monitoring Applications

  • Creating Services
  • Using Services with Client Applications
  • Using Services with the Resource Manager
  • Using Enterprise Manager to Manage Consumer Group Mappings
  • Using Services with Metric Thresholds
  • Service Aggregation and Tracing
  • Client Identifier Aggregation and Tracing
  • trcsess Utility

Identifying Problem SQL Statements

  • SQL Statement Processing Phases
  • Role of the Oracle Optimizer
  • Identifying Bad SQL
  • Uses of Execution Plans
  • Reading an Execution Plan
  • SQL Trace Facility
  • Generate an Optimizer Trace

Influencing the Optimizer

  • Functions of the Query Optimizer
  • Selectivity,Cardinality and Cost
  • Optimizer Statistics
  • Extended Statistics
  • Adaptive Execution Plans
  • Using Hints
  • Access Paths
  • Join Operations and Sort Operations

Reducing the Cost of SQL Operations

  • Index Maintenance
  • SQL Access Advisor
  • Table Reorganization Methods
  • Space Management
  • Extent Management
  • Migration and Chaining
  • Shrinking Segments
  • Data Compression

Using SQL Performance Analyzer

  • Capturing the SQL Workload
  • Creating a SQL Performance Analyzer Task
  • Comparison Report
  • Tuning Regressing Statements
  • SQL Tuning Recommendations
  • Preventing Regressions
  • Tuning Regressed SQL Statements

SQL Performance Management

  • Maintaining Optimizer Statistics
  • Setting Statistic Preferences
  • Using the Optimizer Statistics Advisor
  • Deferred Statistics Publishing
  • Automatic SQL Tuning
  • SQL Tuning Advisor
  • Using the SQL Access Advisor
  • SQL Plan Management

Using Database Replay

  • Capture Considerations
  • Replay Considerations
  • Replay Analysis
  • Database Replay Packages
  • Calibrating Replay Clients

Tuning the Shared Pool

  • Shared Pool Architecture
  • The Library Cache
  • Latch and Mutex
  • Diagnostic Tools for Tuning the Shared Pool
  • Sizing the Shared Pool
  • Avoiding Fragmentation
  • Data Dictionary Cache
  • SQL Query Result Cache

Tuning the Buffer Cache

  • Database Buffers
  • Symptoms of a Buffer Cache Issue
  • Solutions for Buffer Cache Issues
  • Memoptimized Rowstore
  • Automatically Tuned Multiblock Reads
  • Database Smart Flash Cache
  • Force Full Database Caching
  • Flushing the Buffer Cache

Tuning PGA and Temporary Space

  • SQL memory usage
  • SQL Memory Manager
  • Configuring Automatic PGA Memory
  • Managing PGA for PDBs
  • PGA Target Advice Statistics and Histograms
  • Temporary Tablespace Management
  • Temporary Tablespace Group
  • Shrinking a Temporary Tablespace

Automatic Memory

  • Dynamic SGA
  • Memory Advisories
  • Automatic Shared Memory Management
  • Memory Broker Architecture
  • Memory Management for CDBs
  • Managing SGA for PDBs
  • SGA Advisor
  • Automatic Memory Management

Performance Tuning Summary with Waits

  • Automatic Checkpoint Tuning
  • Sizing the Redo Log Buffer
  • Sizing Redo Log Files
  • Increasing the Performance of Archiving
  • General Tablespace and Undo Tablespace Best Practices
  • SQL Execution Related Waits
  • Internal Fragmentation Considerations
  • I/O Modes