Oracle Database 12c: Performance Management and Tuning Training NEW




    5 Day(s) Course


    Official Courseware


    Exam Voucher


    Certified Trainer


    1:1 Training Available on Demand


    Customize Your Batch



    This Oracle Database 12c: Performance Management and Tuning course explore the various functionalities of performance analysis and tuning tasks to be performed by a DBA, also including diagnosis and tuning of the Oracle Database instance components, proactive management through built-in performance analysis features and tools, and tuning of SQL-related performance issues. This course will also introduce participants to Oracle Database Cloud Service.

    Completion of this training course will enable candidates in:

    • Using the Oracle tuning methodology.
    • Using Oracle-supplied tools for monitoring SQL performance issues.
    • Using database advisors for proactive correction performance problems.
    • Identifying and tuning problem SQL statements.
    • Monitoring instance performance by using Enterprise Manager.
    • Gaining an understanding of the Oracle Database Cloud Service.
    • Tuning instance components.

    Course Benefits:

    The Database Administrator will be exposed to various methods to identify the SQL statements that require tuning and the diagnostic tools deployed in improving performance. All this will include the deployment of statistics, profiles to influence the optimizer, and deploying the SQL Advisors. 

    1. Introduction

    • Course Objectives Agenda, & Organization
    • Topics Not Included in the Course
    • Tuning Methodology
    • Who Tunes? What Does the DBA Tune?

    2. Basic Tuning Diagnostics

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

    3. Using Automatic Workload Repository

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

    4. Defining the Scope of Performance Issues

    • Define the Problem
    • Define the Scope & Set the Priority
    • Top SQL Reports
    • Common Tuning Problems
    • Tune During the Life Cycle
    • Performance Versus Business Requirements
    • ADDM Tuning Session

    5. Metrics and Alerts Usage

    • Metrics and Alerts Overview
    • Limitation of Base Statistics
    • Benefits of Metrics
    • View Metric History Information
    • View Histograms
    • Server-Generated Alerts
    • Set Thresholds
    • Metrics and Alerts Views

    6. Using Baselines

    • Comparative Performance Analysis with AWR Baselines
    • Automatic Workload Repository Baselines
    • Move Window Baseline
    • Baselines in Performance Page Settings
    • Baseline Templates
    • Create AWR Baselines
    • Manage Baselines with PL/SQL

    7. Using AWR-Based Tools

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

    8. Real-Time Database Operation Monitoring

    • Define a Database Operation
    • Database Operation Concepts
    • Scope of a Composite Database Operation
    • Identify a Database Operation
    • Enable Monitoring of Database Operations
    • Identify, Start, and Complete a Database Operation

    9. Monitoring Applications

    • Define a Service
    • Service Attributes
    • Create Services
    • Service Types
    • Manage Services in a Single-Instance Environment
    • Deployment of Services
    • Deploy Services with Client Applications
    • Services and Pluggable Databases

    10. Identifying Problem SQL Statements

    • Role of the Oracle Optimizer
    • Identify Bad SQL
    • Top SQL Reports
    • SQL Statement Processing Phases
    • SQL Monitoring
    • Define an Execution Plan
    • Methods to View Execution Plans
    • Uses of Execution Plans

    11. Influencing the Optimizer

    • Functions of the Query Optimizer
    • Cardinality and Cost
    • Changing Optimizer Behavior
    • Optimizer Statistics
    • Extended Statistics
    • Control the Behavior of the Optimizer with Parameters
    • Enable Query Optimizer Features

    12. Reducing the Cost of SQL Operations

    • Cost Reduction
    • Index Maintenance
    • SQL Access Advisor
    • Table Maintenance for Performance
    • Table Re-Organization Methods
    • Space Management & Extent Management
    • Data Storage

    13. Using SQL Performance Analyzer

    • Real Application Testing: Overview & Use Cases
    • SQL Performance Analyzer: Process
    • Capture the SQL Workload
    • Create a SQL Performance Analyzer Task
    • SQL Performance Analyzer: Tasks
    • Parameter Change
    • SQL Performance Analyzer Task Page

    14. SQL Performance Management

    • Maintain SQL Performance
    • Maintain Optimizer Statistics
    • Automated Maintenance Tasks
    • Statistic Gathering Options
    • Set Statistic Preferences
    • Restore Statistics
    • Deferred Statistics Publishing
    • Automated SQL Tuning

    15. Using Database Replay

    • Database Replay Usage
    • The Big Picture
    • System Architecture
    • Capture Considerations
    • Replay Considerations: Preparation, Options & Analysis

    16. Tuning the Shared Pool

    • Shared Pool Architecture & Operation
    • The Library Cache
    • Latch and Mutex
    • Diagnostic Tools for Tuning the Shared Pool
    • Avoid Hard Parses
    • Reduce the Cost of Soft Parses
    • Size the Shared Pool

    17. Tuning the Buffer Cache

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

    18. Tuning PGA and Temporary Space

    • SQL Memory Usage & Memory Manager
    • Performance Impact
    • Automatic PGA Memory
    • Configure Automatic PGA Memory
    • Set PGA_AGGREGATE_TARGET Initially
    • Limit the size of the Program Global Area (PGA)

    19. Automatic Memory

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

    20. Performance Tuning Summary with Waits

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

    21. Oracle Database Cloud Service: Overview

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

    This Oracle Database 12c Performance Tuning Training Course is ideal for those working with the profiles of:

    • Database Administrators
    • Data Warehouse Administrator


    Oracle Database 12c: Administration Workshop Ed 2

    Suggested Prerequisites:

    Oracle Database 12c: Install and Upgrade Workshop

    Name Code Fee



    What Our Trainees Say

    Mercury Solutions Ltd. is rated 4.6 stars by based on 18 reviews.