EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

ORACLE DATABASE 19C- PERFORMANCE MANAGEMENT AND TUNING

Course outline

5 days
Discuss this programme →

Duration

5 days

WHAT YOU WILL LEARN

Demonstrate fluency in and strong understanding of Oracle Database 19c performance analysis, management, and tuning for optimal performance in a complex environment.

AUDIENCE

  • Administrator
  • Database Administrator

PREREQUISITES

Suggested Prerequisite

  • Oracle Database 12c: Install and Upgrade Workshop Ed 1

METHODOLOGY

This program will be conducted with interactive lectures, PowerPoint presentations, discussions, and practical exercises.

COURSE OBJECTIVES

In this course, you will learn to:

  • Describe Performance Management
  • Use AWR Performance Statistics
  • Use AWR Based Tools
  • Improve SQL Statement Performance
  • Influence the Optimizer.
  • Use Tuning Analysers and Advisors
  • Monitor Operations and applications
  • Manage Memory
  • Manage Database Memory
  • Use In-Memory Column Store Feature

Overview

  • Practice 1-1: Preparing the databases

Module 2: Defining the Scope of Performance Issues

  • Defining the scope of performances issues objectives
  • Tuning life cycle phases
  • Practice 2-1: using enterprise manager to identify OS issues.

Module 3: Using the time model to diagnose performance issues.

  • Using the time model to diagnose performance issues.
  • Practice 3-1: Viewing the top wait events and the time model.

Module 4: Using statistics and wait events to diagnose performance Issues.

  • Using statistics and wait events to diagnose performance issues.
  • Insurance activity and wait event statistics.
  • Practice 4-1: viewing system statistics and wait events.

Module 5: Using log and trace files to monitor performance.

  • Using log and trace files to monitor performance.
  • Practice 5-1: viewing performance information in the alert log.

Module 6: Using enterprise manager cloud control and SQL developer to monitor performance.

  • Using enterprise manager cloud control and SQL developer to monitor performance.
  • Practice 6-1: using enterprise manager to monitor performance.
  • Practice 6-2: using SQL developer to monitor performance part 1
  • Practice 6-2: using SQL developer to monitor performance part 2

Module 7: Using statspack to view performance data.

  • Using statspack to view performance data.
  • Practice 7-1: installing statspack.
  • Practice 7-2: creating snapshots.
  • Practice 7-3: generating statspack reports.
  • Practice 7-4: using statspack to examine segment statistics (optional).

Module 8: Using automatic workload repository.

  • Using automatic workload repository
  • Managing AWR data in a multitenant environment
  • Practice 1-1: creating and managing AWR snapshots.
  • Practice 1-2: generating and viewing and AWR report.
  • Practice 1-3: generating and viewing a compare period report.

Module 9: Using Metrics and Alerts

  • Using metrics and alerts
  • Practice 2-1: setting up and viewing server-generated alerts.

Module 10: Using Baselines

  • Using Baselines

Module 11: Managing Automated Maintenance Tasks

  • Managing Automated Maintenance Tasks
  • Practice 1-1: Configuring Automatic Maintenance Tasks (Part 1)
  • Practice 1-1: Configuring Automatic Maintenance Tasks (Part 2)

Module 12: Using ADDM to Analyze Performance

  • Using ADDM to Analyze Performance
  • Compare Periods ADDM: Analysis
  • Practice 2-1: Using AWR-Based Tools to Identify Performance Issues
  • Practice 2-2: Performing an ADDM Analysis of a PDB

Module 13: Using Active Session History Data for First Fault System Analysis

  • Using Active Session History Data for First Fault System Analysis
  • Practice 3-1: Generating and Reviewing an ASH Report to Identify Performance Issues

Module 14: Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues

  • Using Emergency Monitoring and Real-Time ADDM to Analyze Performance

Overview

  • Processing a DML Statement

Module 16: Maintaining Indexes

  • Maintaining Indexes
  • Automatic Indexing Task
  • Practice 2-1: Coalescing an Index

Module 17: Maintaining Tables

  • Maintaining Tables
  • Block Space Management with Free Lists
  • Shrinking Segments: Overview
  • Advanced Index Compression
  • Practice 3-1: Analyzing the Impact of Excess Blocks

Introduction

  • Using Initialization Parameters to Control Optimizer Behavior

Module 19: Understanding Execution Plans

  • Understanding Execution Plans
  • SQL*Plus AUTOTRACE
  • Practice 2-1: Using AUTOTRACE and EXPLAIN PLAN

Module 20: Viewing Execution Plans by Using SQL Trace and TKPROF

  • Viewing Execution Plans by Using SQL Trace and TKPROF
  • Practice 3-1: Using SQL TRACE and the TKPROF Utility

Module 21: Managing Optimizer Statistics

  • Managing Optimizer Statistics
  • Extended Statistics
  • Practice 4-1: Capturing Extended Statistics
  • Practice 4-2: Determining the Impact of Stale Statistics
  • Practice 4-3: Using the Optimizer Statistics Advisor to Improve Statistics Collection Quality

Module 22: Using Automatic SQL Tuning

  • Using Automatic SQL Tuning

Module 23: Using the SQL Plan Management Feature

  • Using the SQL Plan Management Feature
  • Practice 6-1: Using SQL Plan Management (Part 1)
  • Practice 6-1: Using SQL Plan Management (Part 2)

Module 24: Overview of the SQL Advisors

  • Overview of the SQL Advisors

Module 25: Using the SQL Tuning Advisor

  • Using the SQL Tuning Advisor
  • Practice 2-1: Using the SQL Tuning Advisor

Module 26: Using the SQL Access Advisor

  • Using the SQL Access Advisor
  • Practice 3-1: Using the SQL Access Advisor to Improve SQL Performance

Module 27: Overview of Real Application Testing Components

  • Overview of Real Application Testing Components

Module 28: Using SQL Performance Analyzer to Determine the Impact of Changes

  • Using SQL Performance Analyzer to Determine the Impact of Changes
  • Practice 2-1: Using SQL Performance Analyzer (Part 1)
  • Practice 2-1: Using SQL Performance Analyzer (Part 2)
  • Practice 2-2: Seeding SQL Plan Baselines from SQL Performance Analyzer

Module 29: Using Database Replay to Test System Performance

  • Using Database Replay to Test System Performance
  • Database Replay Packages
  • Practice 3-1: Configuring and Using Database Replay at the PDB Level

Module 30: Implementing Real-Time Database Operation Monitoring

  • Implementing Real-Time Database Operation Monitoring
  • Practice 1-1: Monitoring a Composite Database Operation
  • Practice 1-2: Monitoring a PL/SQL Operation

Module 31: Using Services to Monitor Applications

  • Using Services to Monitor Applications
  • Creating Services
  • Service Aggregation Configuration
  • Practice 2-1: Using Services in a Single-Instance Oracle Database (Part 1)
  • Practice 2-1: Using Services in a Single-Instance Oracle Database (Part 2)
  • Practice 2-2: Tracing Services in a Single-Instance Environment

Module 32: Overview of Memory Structures Overview

  • Practices for Lesson 1: Overview

Module 33: Managing Shared Pool Performance

  • Managing Shared Pool Performance
  • Avoid Hard Parses
  • Sizing the Shared Pool
  • Practice 2-1: Sizing the Shared Pool
  • Practice 2-2: Tuning a Hard-Parse Workload
  • Practice 2-3: Tuning a Soft-Parse Workload
  • Practice 2-4: Keeping Objects in the Shared Pool

Module 34: Managing Buffer Cache Performance

  • Managing Buffer Cache Performance
  • Buffer Cache Hit Ratio
  • Caching Tables
  • Multiple Block Sizes
  • Practice 3-1: Sizing the Buffer Cache
  • Practice 3-2: Using the Keep Pool
  • Practice 3-3: Using Force Full Database Caching

Module 35: Managing PGA and Temporary Space Performance

  • Managing PGA and Temporary Space Performance
  • Monitoring SQL Memory Usage
  • Practice 4-1: Adjusting the Value of PGA_AGGREGATE_TARGET

Module 36: Configuring the Large Pool

  • Configuring the Large Pool

Module 37: Using Automatic Shared Memory Management

  • Using Automatic Shared Memory Management
  • Using the V$SYSTEM_PARAMETER View
  • Practice 6-1: Enabling Automatic Shared Memory Management

Module 39: Configuring the In-Memory Column Store Feature

  • Configuring the In-Memory Column Store Feature
  • Practice 2-1: Configuring In-Memory Column Store
  • Practice 2-2: Configuring In-Memory Objects
  • Practice 2-3: Querying In-Memory Objects and Viewing Execution Plans

Module 40: Using In-Memory Column Store with Oracle Database Features

  • Using In-Memory Column Store with Oracle Database Features
  • Practice 4-1: Exporting and Importing In-Memory Objects

Programme scope, delivery arrangements and suitability are confirmed during enquiry.