Database 19c: Performance Tuning

As an Oracle database administrator (DBA), you are responsible for the performance of your Oracle database. Tuning a database to reach the desired performance level may be a daunting task, especially for DBAs who are new to Oracle Database. Oracle Database Performance Tuning course is a quick start guide that describes how to perform day-to-day database performance tuning tasks using features provided by Oracle Diagnostics Pack, Oracle Tuning Pack, and Oracle Enterprise Manager Cloud Control (Cloud Control).

Course Overview

Course Objective

What will you learn?

  • 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 to performance problems
  • Use Enterprise Manager performance-related pages to monitor an Oracle Database
  • Gain an understanding of the Oracle Database Cloud Service

Who Should Learn

Who should go for this training?
The following professionals can go for this course:

  • Administrator
  • Architect
  • Database Administrator

Prerequisite

What are the pre-requisites for this Course?

  • Oracle DBA

Course Content

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

3. Using Automatic Workload Repository

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

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

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

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

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

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

15. Using Database Replay

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

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

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

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

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

6. Using Baseline

  • 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

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

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

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

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

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

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

20. Performance Tuning Summary with Waits

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

Modes of Training

Classroom Training

Live interactive sessions delivered in our classroom by our expert trainers with real-time scenarios.

Online Training

Learn from anywhere over the internet, joining the live sessions delivered by our expert trainers.

Self-Pace Training

Learn through pre-recorded video sessions delivered by experts at your own pace and timing.

Frequently Asked Questions

Who are the trainers?

Our trainer is an OCP & OCM certified consultant and has a significant amount of experience in working with the technology, having 18yrs of experience.

How do I join the live sessions?

Once you get registered, our back-end team will share you the details to join the session live over an online portal which can be accessed through a browser.

What if I miss a class?

Each of our live sessions is recorded. In case if you miss any, you can request us to share the link to that particular session.

How will I execute the practical?

For practical execution, our trainer/technical team will provide server access details to the student

Will you help us in building our own Lab Setup in our local Desktop/Laptop?

Yes. We do provide the step-by-step document which you can follow and if required our technical team will assist you.

What is the difference between live online and pre-recorded video training?

Live-Online training is where you can have a live session with the trainer and clarify queries parallelly.

Pre-recorded sessions are the recorded videos that will be provided to you that you can see, listen, and learn anytime at ur feasible place. For doubts in the videos, you can mail the trainer regarding the same.

What if I have queries after completion of the class?

You can contact our support team, or just drop an email to help@sarinfotechindia.com with your queries.

Is the course material accessible after completion of the course?

The course material and recorded videos which are provided during the course period. You can download it anytime.

Any offer/discount that I can avail?

Visit our website regularly to check discounts offers from time to time. However, we provide a discount for single participants & special discounts for 2 or more participants.

Will I get the refund if I cancel my enrollment?

* If the request for cancelation is made within 2 days of enrolment for class, 100% refunded.

* If the request made after 2 days, then Refund is made after deduction of the administration fee.