Oracle 19c Database Tuning


*Looking for flexible schedule (after hours or weekend)? Please call or email us: 858-208-4141 or sales@ccslearningacademy.com.

Student financing options are available.

Transitioning military and Veterans, please contact us to sign up for a free consultation on training and hiring options.

Looking for group training? Contact Us

Download PDF of Course Details

Course Description:

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.








Course Topics

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We’ll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience needs and skill-level.

Overview Oracle Database Architecture

  • Instance Definition
  • Define SGA
  • Define Background Processes
  • Datafile Definition

Query Optimizer

  • SQL Parsing
  • Optimizing Terms
  • Optimizing Methods
  • Query Plan Generation
  • Query Plan Control

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

Oracle 12c Tuning features

  • Identifying and Using Oracle’s Heat Map
  • 12c Compression Levels and Types

Evaluating Execution Plans

  • Defining SQL execution plans
  • Automatic Workload Repository
  • Reading execution plans

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

Using Automatic Workload Repository

  • Defining AWR
  • AWR Settings
  • Creating AWR Baselines

Metrics, Alerts, and Thresholds

  • Defining Metrics
  • Setting Alerts
  • Setting Corrective Actions
  • User Defined Metrics
  • Metric Dynamic Views

Join Types

  • Nested Loops Join
  • Sort Merge join
  • Hash Join and Cartesian Join
  • Equijoins and Nonequijoins
  • Outer Joins
  • Semijoins

AWR Using Baselines

  • Creating AWR baselines
  • Creating AWR Repeating baselines
  • Moving Window Baseline

Additional AWR performance tools

  • Automatic Maintenance Tasks
  • Segment Advisor
  • Statistics Gathering
  • Automatic Tuning Optimizer
  • Automatic Database Diagnostic Monitor
  • Active Session History (ASH)

Optimizer Statistics

  • Optimizer Statistics Overview
  • Table and Index Statistics
  • Statistic Preferences
  • Statistics Gathering
  • Locking Statistics, Export/Import Statistics
  • Pending and published statistics
  • Optimizer Hints
  • Optimizer Paths
  • Cost Base Optimization

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

Bind Variables and database parameters

  • Bind variable definition
  • Cursor_sharing parameter
  • Adaptive Cursor Sharing

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

SQL Tuning Advisor

  • SQL Tuning Advisor: Overview
  • SQL Tuning Advisor Limited Mode
  • Sql Tuning Advisor Comprehensive mode
  • Sql Tuning Profiles

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

Automatic Sql Tuning

  • Automatic Sql Tuning Maintenance Task
  • Automatic Tuning Optimization implementation(ATO)
  • Automatic Tuning Optimization Results
  • Enable/Disable Automatic Tuning Optimization

Sql Plan Management

  • Sql plan Management and baseline overview
  • Enable sql plan management
  • Loading Sql Plan baselines into the SGA
  • Adaptive plan management

Shared Pool Tuning

  • Shared pool architecture
  • Shared pool parameters
  • Library Cache
  • Dictionary cache
  • Large pool considerations and contents

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

Tuning the PGA (Program Global Area)

  • PGA Overview
  • PGA Database Parameters
  • Temporary Segments
  • Temporary Tablespace
  • Sizing the PGA

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

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

Automatic Storage Management

  • Overview of ASM
  • Definition of Grid Infrastructure
  • ASM Instance
  • ASM Diskgroups
  • ASM Diskgroup parameters and templates

What You'll Learn

Our engaging instructors and mentors are highly-experienced practitioners who bring years of current “on-the-job” experience into every classroom. Working within in a hands-on learning environment, guided by our expert team, attendees will explore:

  • Overview Oracle Database Architecture
  • Query Optimizer
  • Tuning Container Databases and Pluggable Databases
  • Oracle 12c 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

Target Audience

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


With CCS Learning Academy, you’ll receive:

  • Certified Instructor-led training
  • Official Training Seminar Student Handbook
  • Collaboration with classmates (not currently available for self-paced course)
  • Real-world learning activities and scenarios
  • Exam scheduling support*
  • Enjoy job placement assistance for the first 12 months after course completion.
  • This course is eligible for CCS Learning Academy’s Learn and Earn Program: get a tuition fee refund of up to 50% if you are placed in a job through CCS Global Tech’s Placement Division*
  • Government and Private pricing available.*

*For more details call: 858-208-4141 or email: training@ccslearningacademy.com; sales@ccslearningacademy.com


Shopping Cart