In the Oracle Database 12c: Performance Management and Tuning course, learn about the performance analysis and tuning tasks expected of a DBA: proactive management through built-in performance analysis features and tools, diagnosis and tuning of the Oracle Database instance components, and diagnosis and tuning of SQL-related performance issues. In this course, you will be introduced to Oracle Database Cloud Service.

starstarstarstarstar_outline

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

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
Gain an understanding of the Oracle Database Cloud Service

1

  • Introduction

  • Course Objectives
    Course Organization
    Course Agenda
    Topics Not Included in the Course
    Who Tunes?
    What Does the DBA Tune?
    How to Tune
    Tuning Methodology
2

  • Basic Tuning Diagnostics

  • Performance Tuning Diagnostics
    Performance Tuning Tools
    Tuning Objectives
    Top Timed Events
    DB Time
    CPU and Wait Time Tuning Dimensions
    Time Model
    Dynamic Performance Views
3

  • Using Automatic Workload Repository

  • Automatic Workload Repository Overview
    Automatic Workload Repository Data
    Enterprise Manager Cloud Control and AWR
    Snapshots
    Reports
    Compare Periods
4

  • Defining the Scope of Performance Issues

  • Defining the Problem
    Limiting the Scope
    Setting the Priority
    Top SQL Reports
    Common Tuning Problems
    Tuning During the Life Cycle
    ADDM Tuning Session
    Performance Versus Business Requirements
5

  • Using Metrics and Alerts

  • Metrics and Alerts Overview
    Limitation of Base Statistics
    Benefits of Metrics
    Viewing Metric History Information
    Viewing Histograms
    Server-Generated Alerts
    Setting Thresholds
    Metrics and Alerts Views
6

  • Using Baselines

  • Comparative Performance Analysis with AWR Baselines
    Automatic Workload Repository Baselines
    Moving Window Baseline
    Baselines in Performance Page Settings
    Baseline Templates
    AWR Baseslines
    Creating AWR Baselines
    Managing Baselines with PL/SQL
7

  • Using AWR-Based Tools

  • Automatic Maintenance Tasks
    ADDM Performance Monitoring
    Using Compare Periods ADDM
    Active Session History
    New or Enhanced Automatic Workload Repository Views
    Emergency Monitoring
    Real-time ADDM
8

  • Real-Time Database Operation Monitoring

  • Overview
    Use Cases
    Defining a Database Operation
    Scope of a Composite Database Operation
    Database Operation Concepts
    Identifying a Database Operation
    Enabling Monitoring of Database Operations
    Identifying, Starting, and Completing a Database Operation
9

  • Monitoring Applications

  • What is a Service?
    Service Attributes
    Service Types
    Creating Services
    Managing Services in a Single-Instance Environment
    Where are Services Used?
    Using Services with Client Applications
    Services and Pluggable Databases
10

  • Identifying Problem SQL Statements

  • SQL Statement Processing Phases
    Role of the Oracle Optimizer
    Identifying Bad SQL
    Top SQL Reports
    SQL Monitoring
    What is an Execution Plan?
    Methods for Viewing Execution Plans
    Uses of Execution Plans
11

  • Influencing the Optimizer

  • Functions of the Query Optimizer
    Selectivity
    Cardinality and Cost
    Changing Optimizer Behavior
    Optimizer Statistics
    Extended Statistics
    Controlling the Behavior of the Optimizer with Parameters
    Enabling Query Optimizer Features
12

  • Reducing the Cost of SQL Operations

  • Reducing the Cost
    Index Maintenance
    SQL Access Advisor
    Table Maintenance for Performance
    Table Reorganization Methods
    Space Management
    Extent Management
    Data Storage
13

  • Using SQL Performance Analyzer

  • Real Application Testing: Overview
    Real Application Testing: Use Cases
    SQL Performance Analyzer: Process
    Capturing the SQL Workload
    Creating a SQL Performance Analyzer Task
    SQL Performance Analyzer: Tasks
    Parameter Change
    SQL Performance Analyzer Task Page
14

  • SQL Performance Management

  • Maintaining SQL Performance
    Maintaining Optimizer Statistics
    Automated Maintenance Tasks
    Statistic Gathering Options
    Setting Statistic Preferences
    Restore Statistics
    Deferred Statistics Publishing
    Automatic SQL Tuning
15

  • Using Database Replay

  • Using Database Replay
    The Big Picture
    System Architecture
    Capture Considerations
    Replay Considerations: Preparation
    Replay Considerations
    Replay Options
    Replay Analysis
16

  • Tuning the Shared Pool

  • Shared Pool Architecture
    Shared Pool Operation
    The Library Cache
    Latch and Mutex
    Diagnostic Tools for Tuning the Shared Pool
    Avoiding Hard Parses
    Reducing the Cost of Soft Parses
    Sizing the Shared Pool
17

  • Tuning the Buffer Cache

  • Oracle Database Architecture: Buffer Cache
    Buffer Cache: Highlights
    Database Buffers
    Buffer Hash Table for Lookups
    Working Sets
    Buffer Cache Tuning Goals and Techniques
    Buffer Cache Performance Symptoms
    Buffer Cache Performance Solutions
18

  • Tuning PGA and Temporary Space

  • SQL Memory Usage
    Performance Impact
    Automatic PGA Memory
    SQL Memory Manager
    Configuring Automatic PGA Memory
    Setting PGA_AGGREGATE_TARGET Initially
    Limiting the size of the Program Global Area (PGA)
    SQL Memory Usage
19

  • Automatic Memory

  • Oracle Database Architecture
    Dynamic SGA
    Granule
    Memory Advisories
    Manually Adding Granules to Components
    Increasing the Size of an SGA Component
    Automatic Shared Memory Management: Overview
    SGA Sizing Parameters: Overview
20

  • Performance Tuning Summary with Waits

  • Commonly Observed Wait Events
    Additional Statistics
    Top 10 Mistakes Found in Customer Systems
    Symptoms
21

  • Oracle Database Cloud Service: Overview

  • Database as a Service Architecture, Features and Tooling
    Software Editions: Included Database Options and Management Packs
    Accessing the Oracle Database Cloud Service Console Automated Database Provisioning
    Managing the Compute Node Associated With a Database Deployment
    Managing Network Access to Database as a Service Scaling a Database Deployment
    Performance Management in the Database Cloud Environment
    Performance Monitoring and Tuning
    What Can be Tuned in a DBCS Environment?

Audience

This course is for those whose job could be Data Warehouse Administrator or Database Administrator.

Language

English

Prerequisites

While there are no prerequisites for this course, please ensure you have the right level of experience to be successful in this training.

$3,675

Length: 5.0 days (40 hours)

Level:

Not Your Location? Change

Course Schedule:

To request a custom delivery, please chat with an expert.

Loading...