Oracle 19C Database Tuning is an intermediate level course for Oracle database experienced attendees that explores core tuning skills such as Database parameters, SQL Tuning Advisor, SQL Access Advisor, Adaptive SQL plans and more.

starstarstarstarstar_outline

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

Learning Objectives

Working in a hands-on learning environment led by our expert facilitator, you’ll explore:
The Oracle Database Architecture
Query Optimizer
Tuning Container Databases and Pluggable Databases
Oracle 19c Tuning features
Evaluating Execution Plans
Oracle Tuning Tools
Using Automatic Workload Repository
Join Types
AWR Using Baselines
Additional AWR performance tools
Optimizer Statistics
Monitoring a Service
Bind Variables and database parameters
Oracle's Real Application Testing (RAT)
SQL Tuning Advisor
Automatic Sql Tuning
Sql Plan Management
Shared Pool Tuning
Tuning the database buffer cache
Tuning the PGA (Program Global Area)
Automatic Memory Management (AMM)
Tuning Segment Space Utilization (ASSM)
Automatic Storage Management

1

  • OVERVIEW ORACLE DATABASE ARCHITECTURE

  • Instance Definition

    Define SGA

    Define Background Processes

    Datafile Definition

2

  • QUERY OPTIMIZER

  • SQL Parsing

    Optimizing Terms

    Optimizing Methods

    Query Plan Generation

    Query Plan Control

3

  • TUNING CONTAINER DATABASES AND PLUGGABLE DATABASES

  • Pluggable tuning parameters

    Define Container tuning structure

    Using PDB$SEED

    Create a new PDB

    Plug and unplug a PDB

4

  • ORACLE 12C TUNING FEATURES

  • Identifying and Using Oracle's Heat Map

    12c Compression Levels and Types

5

  • EVALUATING EXECUTION PLANS

  • Defining SQL execution plans

    Automatic Workload Repository

    Reading execution plans

6

  • ORACLE TUNING TOOLS

  • Monitoring tools overview

    Enterprise Manager

    Dynamic Performance Views

    Automatic Workload Repository

    Automatic Database Diagnostic Monitor

    Sql Tuning Advisor

    SQL Access Advisor

    Sql Access Advisor

    DB operation Tuning

    DB operation Active Reporting

7

  • USING AUTOMATIC WORKLOAD REPOSITORY

  • Defining AWR

    AWR Settings

    Creating AWR Baselines

8

  • METRICS, ALERTS, AND THRESHOLDS

  • Defining Metrics

    Setting Alerts

    Setting Corrective Actions

    User Defined Metrics

    Metric Dynamic Views

9

  • JOIN TYPES

  • Nested Loops Join

    Sort Merge join

    Hash Join and Cartesian Join

    Equijoins and Nonequijoins

    Outer Joins

    Semijoins

10

  • AWR USING BASELINES

  • Creating AWR baselines

    Creating AWR Repeating baselines

    Moving Window Baseline

11

  • ADDITIONAL AWR PERFORMANCE TOOLS

  • Automatic Maintenance Tasks

    Segment Advisor

    Statistics Gathering

    Automatic Tuning Optimizer

    Automatic Database Diagnostic Monitor

    Active Session History (ASH)

12

  • OPTIMIZER STATISTICS

  • Optimizer Statistics Overview

    Table and Index Statistics

    Statistic Preferences

    Statistics Gathering

    e) Locking Statistics, Export/Import Statistics

    Pending and published statistics

    Optimizer Hints

    Optimizer Paths

    Cost Base Optimization

13

  • MONITORING A SERVICE

  • Overview of what is an Oracle Service

    Creating an Oracle Service for Single instance and RAC

    Monitoring a Service

    Resource Management and a Service

    Enterprise Manager and a Service

14

  • BIND VARIABLES AND DATABASE PARAMETERS

  • Bind variable definition

    Cursor_sharing parameter

    Adaptive Cursor Sharing

15

  • ORACLE'S REAL APPLICATION TESTING (RAT)

  • Sql Performance Analyzer overview

    Sql Performance Analyzer Options

    Database Parameter changes

    Database version changes

    Creating SQL Tuning Sets

    Database Replay Overview

    Database Replay Configuration

    Database Replay Options

16

  • SQL TUNING ADVISOR

  • SQL Tuning Advisor: Overview

    SQL Tuning Advisor Limited Mode

    Sql Tuning Advisor Comprehensive mode

    Sql Tuning Profiles

17

  • SQL ACCESS ADVISOR

  • SQL Access Advisor: Overview

    Sql Access Advisor options

    SQL Access Advisor and Sql Tuning Sets

    Sql Access Advisor and AWR

    Results and Implementation

18

  • AUTOMATIC SQL TUNING

  • Automatic Sql Tuning Maintenance Task

    Automatic Tuning Optimization implementation(ATO)

    Automatic Tuning Optimization Results

    Enable/Disable Automatic Tuning Optimization

19

  • SQL PLAN MANAGEMENT

  • Sql plan Management and baseline overview

    Enable sql plan management

    Loading Sql Plan baselines into the SGA

    Adaptive plan management

20

  • SHARED POOL TUNING

  • Shared pool architecture

    Shared pool parameters

    Library Cache

    Dictionary cache

    Large pool considerations and contents

21

  • TUNING THE DATABASE BUFFER CACHE

  • Database buffer cache overview

    Database buffer cache parameters

    Oracle and Dirty reads and writes

    Automatic Shared Memory Management (ASMM)

    Buffer Cache goals and responsibility

    Buffer Cache pools

22

  • TUNING THE PGA (PROGRAM GLOBAL AREA)

  • PGA Overview

    PGA Database Parameters

    Temporary Segments

    Temporary Tablespace

    Sizing the PGA

23

  • AUTOMATIC MEMORY MANAGEMENT (AMM)

  • Oracle's Automatic Memory Management Overview

    Database Auto-tuned Parameters

    Database Non Auto-tuned Parameters

    Automatic Memory Management Hints and Sizing suggestions

    AMM versus ASMM

24

  • TUNING SEGMENT SPACE UTILIZATION (ASSM)

  • Overview of Automatic Segment Space Management

    Defining the DB_BLOCK_SIZE

    Defining DB_nk_CACHE_SIZE parameter

    The DB_BLOCK_SIZE Parameter

    Overview of table compression, block chaining, and block migration

25

  • AUTOMATIC STORAGE MANAGEMENT

  • Overview of ASM

    Definition of Grid Infrastructure

    ASM Instance

    ASM Diskgroups

    ASM Diskgroup parameters and templates

    ASMCMD

Audience

Language

English

Prerequisites

This intermediate-level course requires students have incoming experience working with Oracle Database 18 or higher.

$2,695

Length: 5.0 days (40 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
30
Oct
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Loading...