Skip to content
Home / Computer Training Courses / Technical courses / Oracle 19c Training

Oracle 19c Training

Enliten IT partner with StayAhead Training to deliver an extensive range of Oracle 19c courses targeted at ensuring delegates, from programmers to administrators, can perform their job roles successfully.  We’ll teach you how to administer, configure, secure, backup, manage storage and maintain databases.  Oracle SQL reporting courses are also available.

Other, not so popular, Oracle training courses (e.g. Oracle e-Business Suite R12) are also available so if you don’t find what you are looking for in the list below then please contact us.

Note: All Oracle 19c workshops can be modified or tailored for delivery, at your own offices, anywhere within the UK.

ORACLE 19c DBA Performance Tuning and Management logo
Home > Computer Training Courses > Technical courses > Oracle 19c Training > ORACLE 19c DBA Performance Tuning and Management
Find Your Course

ORACLE 19c DBA Performance Tuning and Management

5 days

This Oracle 19c DBA Performance Tuning & Management course introduces how to use tuning goals and methodologies to identify tuning contentions in an Oracle database and how to take corrective action, how to detect and tune common database performance problems, how to use the database advisors to proactively tune a database and how to use the various tools provided by Oracle to tune a database.

Delegate will practice:

  • Tuning database applications for optimal performance
  • Identifying and resolving common tuning problems
  • Using diagnostic sources
  • Managing database statistics
  • Identifying and correcting problem SQL statements
  • Examining the execution plan of a SQL statement using Explain Plan
  • Examining the efficiency of SQL statements using SQL trace and autotrace
  • Using tools for monitoring and diagnosing SQL performance issues
  • Tuning memory components
  • Tuning the SGA and Shared Pool
  • Tuning the data block buffers
  • Using Automatic Memory Management
  • Setting the PGA and temporary space
  • Managing disk I/O
  • Tuning block space
  • Monitoring statistics and wait events
  • Using metrics, alerts and baselines
  • Using AWR and ADDM
Download PDF Book now

What will you learn?

By the end of the course, delegates will be able to:

  • Use tuning goals and methodologies
  • Detect and tune common database performance problems
  • Use the database advisors to proactively tune a database
  • Use the AWR and ADDM tools to tune a database
  •  Use Enterprise Manager to monitor an Oracle database

Audience?

Database administrators and technical support staff.

Course Contents

ORACLE 19c SQL Performance Tuning

The first two days of this ORACLE Database 19c DBA Performance Tuning and Management course cover the same content as per the ORACLE 19c SQL Performance Tuning course.  Please refer to that course outline for relevant course modules.

BASIC TUNING DIAGNOSTICS

  • Performance Tuning Diagnostics, Features, Tools
  • DB Time
  • CPU and Wait Time Tuning Dimensions
  • Time Model
  • Dynamic Performance Views
  • Database Statistics
  • Wait Events
  • Diagnostic Sources
  • Log Files and Trace Files

REDUCE THE COST OF SQL OPERATIONS

  • Identify Unusable Objects
  • Maintain Indexes
  • The SQL Access Advisor
  • Maintain Tables and Reorganize Tables
  • Manage Extents
  • Compress Tables
  • Row Chaining and Row Migration
  • Segment Shrink

THE SQL PERFORMANCE ANALYZER

  • An Overview of the SQL Performance Analyzer
  • Usage of the SQL Performance Analyzer
  • Capture a SQL Workload
  • Create a SQL Performance Analyzer Task
  • Generate Comparison Reports
  • Tune Regressed Statements
  • Guided Workflow Analysis
  • SQL Performance Analyzer Views

SQL PERFORMANCE MANAGEMENT

  • Maintenance of the Optimizer Statistics
  • Optimizer Statistics Collection
  • Gather Statistics Options
  • Defer Publishing Statistics
  • The Optimizer Statistics Advisor
  • The Expression Statistics Store
  • Adaptive Query Optimization
  • Continuous Adaptive Query Plans
  • Automatic SQL Tuning
  • SQL Monitoring
  • SQL Monitoring with the SQL Tuning Advisor
  • SQL Monitoring with Enterprise Manager and SQL Developer
  • The SQL Access Advisor
  • SQL Plan Management
  • SQL Diagnostics and Repair

AUTOMATIC MEMORY MANAGEMENT

  • Overview of Automatic Shared Memory Management
  • Dynamic SGA
  • Parameters for Sizing the SGA
  • Enable and Disable Automatic Shared Memory Management
  • Use the SGA Advisor
  • Overview of Automatic Memory Management
  • Enable and Disable Automatic Memory Management
  • Monitor Automatic Memory Management
  • The PGA Aggregate Limit

TUNE THE SHARED POOL

  • Overview of the Shared Pool Architecture
  • Latches and Mutexes
  • Diagnostic Tools for Tuning the Shared Pool
  • Avoiding Hard Parses
  • Pin Objects in the Shared Pool
  • Size the Shared Pool
  • Avoiding Fragmentation of the Shared Pool
  • The Data Dictionary Cache
  • The SQL Query Result Cache

TUNE THE BUFFER CACHE

  • Overview of the Database Buffer Cache Architecture
  • Buffer Cache Tuning Goals and Techniques
  • Buffer Cache Performance Symptoms and Solutions
  • Buffer Cache Advisor
  • Database Smart Flash Cache
  • Full Database Caching
  • When to Flush the Buffer CachE

TUNE PGA AND TEMPORARY SPACE

  • Overview of the PGA
  • SQL Memory Usage
  • Automatic PGA Memory Mode Configuration
  • Configure the PGA for a New Instance
  • PGA Target Advice Statistics and Histograms
  • Automatic PGA and AWR Reports
  • Temporary Tablespace Management
  • Temporary Tablespace Group
  • Multiple Temporary Tablespaces
  • Monitoring Temporary Tablespaces
  • Temporary Tablespace Shrink
  • Data Dictionary Views and Sort Segment

CREATE AND USE SNAPSHOTS AND BASELINES WITH THE AUTOMATIC WORKLOAD REPOSITORY

  • An Overview of In-Built Automatic Tuning Capabilities
  • An Overview of the Automatic Workload Repository
  • AWR Data
  • Enterprise Manager and the AWR
  • Create and Compare Snapshots
  • Examine AWR Reports
  • Create and Compare Baselines
  • Moving Window Baseline
  • Baseline Templates
  • Baselines Views
  • Performance Monitoring and Baselines
  • Compare Periods Report
  • Define Alert Thresholds Using a Static Baseline
  • Configure Adaptive Thresholds
  • Performance Hub Active Report

USE METRICS AND ALERTS

  • An Overview of Metrics and Alerts
  • The Benefits and Limitations of Metrics and Alerts
  • System Generated, Threshold Generated and Event Based Alerts
  • Set Thresholds
  • View Metric History Information
  • View Histograms
  • Metric and Alert Views

USE AWR-BASED TOOLS

  • Automatic Maintenance Tasks
  • Maintenance Window Configuration
  • ADDM Performance Monitoring
  • Active History
  • Generate an ASH Report
  • Real-time ADDM

REAL TIME DATABASE OPERATION MONITORING

  • Overview of Real Time Database Operation Monitoring
  • Database Operation Concepts
  • Define a Database Operation
  • Enable the Monitoring of Database Operations
  • Identify, Start and Complete a Database Operation
  • Monitor the Progress of a Database Operation
  • Database Operation Views
  • Database Operation Tuning

MONITOR APPLICATIONS

  • The Attributes and Types of Services
  • Create Services
  • Use Services with Client Applications
  • Use Services with the Resource Manager
  • Use Services the Oracle Scheduler
  • Services and Metric Thresholds
  • Use Aggregation of Services with Tracing
  • The Top Services Performance Page

• BIG DATA AND DATA WAREHOUSE FEATURES

  • Online Statistics gathering for Bulk Loads
  • Real Time Statistics Gathering During DML Operations
  • Configure a High Frequency Statistics Gathering Job
  • Basic and Advanced Table Compression
  • Advanced Index Compression
  • Reduce Cursor Invalidations For DDLS
  • Automatic Indexing with DBMS_AUTO_INDEX
  • Scaleable Sequences
  • Bitmap-Based COUNT(DISTINCT) SQL Functions
  • Quarantine of Runaway SQL Statements

TUNE DATABASE I/O

  • An Overview of Database I/O Management
  • I/O Architecture and Modes
  • Important I/O Metrics for Oracle Databases
  • I/O Calibration
  • I/O Statistics and Diagnostics
  • Layout Files using Operating System or Hardware Striping
  • Manually Distribute Files to Reduce I/O Contention
  • Sample Configurations
  • Asynchronous and Synchronous I/O
  • Multi-Threaded Oracle
  • Automatic Storage Management (ASM)

SUMMARY OF PERFORMANCE TUNING

  • The Potential Impact of Initialization Parameters on Performance
  • Initially Size Memory for a Database
  • Recommended Best Practices for Different Types of Tablespaces
  • Determine and Use Block Sizes
  • Size the Redo Log Buffer and the Redo Log Files
  • Configure Automatic Statistics Gathering

Course Materials

Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification.

Prerequisites

Delegates should have practical knowledge of using SQL and of administering an Oracle database. They should have attended the Oracle SQL and the Oracle Database 19c Administration course or have a good working knowledge of Oracle SQL and Oracle database administration.  The ability to describe and use Oracle built-in packages would be highly advantageous but is not essential.

This course is run on a Linux operating system, a basic knowledge of Linux/UNIX is recommended but is not essential.

The first 2 days of this course cover the same content as per the ORACLE 19c SQL Performance Tuning course.  Please refer to that course outline for relevant course modules.

Additional Information

Note:  This Oracle Database 19c DBA Performance Tuning and Management course is run on a public schedule or can be taken as a private in-house company course UK wide.   For pricing and availability just pick up the phone and call us on 0845 1085481.