Oracle 19c Database Tuning

Explore Database Tuning with Database Parameters, SQL Tuning Advisor, SQL Access Advisor, Adaptive SQL Plans and More

TTOR21519

Intermediate

5 Days

Course Overview

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.

This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises. 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

Need different skills or topics?  If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate.  We offer additional Oracle, database, scripting, administration and other related topics that may be blended with this course for a track that best suits your needs.

NOTE: This training is NOT Official Oracle University training. This is independent, adjustable content that aligns with current topics, skills and tools that participants need to excel in these areas.  This course is for 19c.

Course Objectives

This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises.  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

Need different skills or topics?  If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate.  We offer additional Oracle, database, scripting, administration and other related topics that may be blended with this course for a track that best suits your needs.

Course Prerequisites

This hands-on course is geared for experienced DBAs already working with Oracle. Incoming students should possess:

  • Knowledge of SQL programming basics
  • Knowledge of Oracle Database 18c or newer
  • Linux skills, including familiarity with command-line options such as ls, cd, cp, and su

Please see the Related Courses tab for specific Pre-Requisite courses, Related Courses that offer similar skills or topics, and next-step Learning Path recommendations.

Course Agenda

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.

  1. Overview Oracle Database Architecture
  • Instance Definition
  • Define SGA
  • Define Background Processes
  • Datafile Definition
  1. Query Optimizer
  • SQL Parsing
  • Optimizing Terms
  • Optimizing Methods
  • Query Plan Generation
  • Query Plan Control
  1. 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
  1. Oracle 12c Tuning features
  • Identifying and Using Oracle's Heat Map
  • 12c Compression Levels and Types
  1. Evaluating Execution Plans
  • Defining SQL execution plans
  • Automatic Workload Repository
  • Reading execution plans
  1. 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
  1. Using Automatic Workload Repository
  • Defining AWR
  • AWR Settings
  • Creating AWR Baselines
  1. Metrics, Alerts, and Thresholds
  • Defining Metrics
  • Setting Alerts
  • Setting Corrective Actions
  • User Defined Metrics
  • Metric Dynamic Views
  1. Join Types
  • Nested Loops Join
  • Sort Merge join
  • Hash Join and Cartesian Join
  • Equijoins and Nonequijoins
  • Outer Joins
  • Semijoins
  1. AWR Using Baselines
  • Creating AWR baselines
  • Creating AWR Repeating baselines
  • Moving Window Baseline
  1. Additional AWR performance tools
  • Automatic Maintenance Tasks
  • Segment Advisor
  • Statistics Gathering
  • Automatic Tuning Optimizer
  • Automatic Database Diagnostic Monitor
  • Active Session History (ASH)
  1. 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
  1. 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
  1. Bind Variables and database parameters
  • Bind variable definition
  • Cursor_sharing parameter
  • Adaptive Cursor Sharing
  1. 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
  1. SQL Tuning Advisor
  • SQL Tuning Advisor: Overview
  • SQL Tuning Advisor Limited Mode
  • Sql Tuning Advisor Comprehensive mode
  • Sql Tuning Profiles
  1. 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
  1. Automatic Sql Tuning
  • Automatic Sql Tuning Maintenance Task
  • Automatic Tuning Optimization implementation(ATO)
  • Automatic Tuning Optimization Results
  • Enable/Disable Automatic Tuning Optimization
  1. Sql Plan Management
  • Sql plan Management and baseline overview
  • Enable sql plan management
  • Loading Sql Plan baselines into the SGA
  • Adaptive plan management
  1. Shared Pool Tuning
  • Shared pool architecture
  • Shared pool parameters
  • Library Cache
  • Dictionary cache
  • Large pool considerations and contents
  1. 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
  1. Tuning the PGA (Program Global Area)
  • PGA Overview
  • PGA Database Parameters
  • Temporary Segments
  • Temporary Tablespace
  • Sizing the PGA
  1. 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
  1. 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
  1. Automatic Storage Management
  • Overview of ASM
  • Definition of Grid Infrastructure
  • ASM Instance
  • ASM Diskgroups
  • ASM Diskgroup parameters and templates
  • ASMCMD

Course Materials

Student Materials: Each participant will receive a digital Student Guide and/or Course Notes, code samples, software tutorials, step-by-step written lab instructions (as applicable), diagrams and related reference materials and resource links. Students will also receive the project files (or code, if applicable) and solutions required for the hands-on work.

Hands-On Setup Made Simple! Our dedicated tech team will work with you to ensure our ‘easy-access’ cloud-based course environment, or local installation, is accessible, fully-tested and verified as ready to go well in advance of the course start date, ensuring a smooth start to class and effective learning experience for all participants. In some cases we can also help you install this course locally if preferred. Please inquire for details and options.

Every-Course Extras = High-Value & Long-Term Learning Support! All Public Schedule courses include our unique EveryCourse Extras package (Course Recordings, Live Instructor Follow-on Support, Free *Live* Course Refresh Re-Takes, early access to Special Offers, Free Courses & more). Please inquire for details.

Raise the bar for advancing technology skills

Attend a Class!

Live scheduled classes are listed below or browse our full course catalog anytime

Special Offers

We regulary offer discounts for individuals, groups and corporate teams. Contact us

Custom Team Training

Check out custom training solutions planned around your unique needs and skills.

EveryCourse Extras

Exclusive materials, ongoing support and a free live course refresh with every class.

Attend a Course

Please see the current upcoming available open enrollment course dates posted below. Please feel free to Register Online below, or call 844-475-4559 toll free to connect with our Registrar for assistance. If you need additional date options, please contact us for scheduling.

Course Title Days Date Time Price
Oracle 19c Database Tuning 5 Days Jun 12 to Jun 16 10:00 AM to 06:00 PM EST $2,695.00 Enroll
Oracle 19c Database Tuning 5 Days Aug 21 to Aug 25 10:00 AM to 06:00 PM EST $2,695.00 Enroll
Oracle 19c Database Tuning 5 Days Oct 30 to Nov 3 10:00 AM to 06:00 PM EST $2,695.00 Enroll

Learning is Twice as Nice!
Buy One Get One Free!

Enroll by March 31 in any TWO public classes in 2023 for the price of ONE! 

Click for Details & Additional Offers

Learn. Explore. Advance!

Extend your training investment! Recorded sessions, free re-sits and after course support included with Every Course
Trivera MiniCamps
Gain the skills you need with less time in the classroom with our short course, live-online hands-on events
Trivera QuickSkills: Free Courses and Webinars
Training on us! Keep your skills current with free live events, courses & webinars
Trivera AfterCourse: Coaching and Support
Expert level after-training support to help organizations put new training skills into practice on the job

The voices of our customers speak volumes

Special Offers
Limited Offer for most courses.

SAVE 50%

Learn More