Free Webinar: ForgeRock Launches On Demand Training


Oracle Database: Performance Management and Tuning

  • Tuition USD $3,860 - $4,070
  • Reviews star_rate star_rate star_rate star_rate star_half 949 Ratings
  • Course Code OUDATA-PMT
  • Duration 5 days
  • Available Formats Virtual, Classroom
  • Use the Oracle tuning methodology
  • Use Oracle-supplied tools for monitoring and diagnosing SQL and instance tuning issues
  • Use database advisors to proactively correct performance problems
  • Identify problem SQL statements 
  • Tune SQL performance problems
  • Monitor instance performance by using Enterprise Manager 
  • Tune instance components by primarily using instance parameters



Skills Gained

  • Use the Oracle Database tuning methodology appropriate to the available tools
  • Utilize database advisors to proactively tune an Oracle Database Instance
  • Use the tools based on the Automatic Workload Repository to tune the database
  • Diagnose and tune common SQL related performance problems
  • Diagnose and tune common Instance related performance problems
  • Use Enterprise Manager performance-related pages to monitor an Oracle Database

Who Can Benefit

  • Administrator
  • Database Administrator


Suggested Prerequisite

  • Oracle Database 12c R2: Install and Upgrade Workshop Ed 2
  • Familiarity with Oracle Database installation
  • Familiarity with Oracle Database configuration concepts

Required Prerequisite

  • Oracle Database 18c: Administration Workshop Ed 1
  • Basic knowledge of Linux operating system
  • A working knowledge of SQL and PL/SQL packages
  • Basic understanding of Oracle Database architecture
  • Familiarity with basic database monitoring procedures

Course Details


  • Introduction
  • Basic Tuning Diagnostics
  • Using Automatic Workload Repository
  • Defining the Scope of Performance Issues
  • Using Metrics and Alerts
  • Using Baselines
  • Using AWR-Based Tools
  • Real-Time Database Operation Monitoring
  • Monitoring Applications
  • Identifying Problem SQL Statements
  • Influencing the Optimizer
  • Reducing the Cost of SQL Operations
  • Using SQL Performance Analyzer
  • SQL Performance Management
  • Using Database Replay
  • Tuning the Shared Pool
  • Tuning the Buffer Cache
  • Tuning PGA and Temporary Space
  • Automatic Memory
  • Performance Tuning Summary with Waits
  • Types of Tuning
  • Tuning Methodology
  • Effective Tuning Goals
  • General Tuning Session

Basic Tuning Diagnostics

  • Performance Tuning Features and Tools
  • Top Timed Events
  • DB Time
  • CPU and Wait Time Tuning Dimensions
  • Time Model
  • Statistic Levels
  • Wait Events
  • Alert Log and Trace Files

Using Automatic Workload Repository

  • Automatic Workload Repository Data
  • AWR Administration
  • AWR in a Multitenant Architecture Database
  • Snapshots
  • Reports
  • Compare Periods
  • PDB-Level Snapshot Views

Defining the Scope of Performance Issues

  • Determining Tuning Priorities
  • Top SQL Repors
  • Common Tuning Problems
  • Tuning During the Life Cycle
  • ADDM Tuning Session
  • Performance Versus Business Requirements
  • Performance Tuning Resources

Using Metrics and Alerts

  • Typical Delta Tools
  • Oracle Database Metrics
  • Statistic Histograms
  • Server-Generated Alerts
  • Alert Usage Model
  • Setting Thresholds

Using Baselines

  • Comparative Performance Analysis with AWR Baselines
  • Types of Baselines
  • Creating AWR Baselines
  • Managing Baselines by Using the DBMS_WORKLOAD_REPOSITORY PL/SQL Package
  • Performance Monitoring and Baselines
  • Defining Alert Thresholds Using a Static Baseline
  • Configuring a Basic Set of Thresholds

Using AWR-Based Tools

  • Automatic Maintenance Tasks
  • ADDM Performance Monitoring
  • AWR and ADDM Behavior in a Multitenant Architecture Database
  • Using Compare Periods ADDM
  • Active Session History
  • Emergency Monitoring
  • Real-time ADDM

Real-Time Database Operation Monitoring

  • Scope of a Composite Database Operation
  • Database Operation Concepts
  • Identifying a Database Operation
  • Enabling Monitoring of Database Operations
  • Monitoring Load Database Operations
  • Reporting Database Operations by Using Functions
  • Database Operation Tuning

Monitoring Applications

  • Creating Services
  • Using Services with Client Applications
  • Using Services with the Resource Manager
  • Using Enterprise Manager to Manage Consumer Group Mappings
  • Using Services with Metric Thresholds
  • Service Aggregation and Tracing
  • Client Identifier Aggregation and Tracing
  • trcsess Utility

Identifying Problem SQL Statements

  • SQL Statement Processing Phases
  • Role of the Oracle Optimizer
  • Identifying Bad SQL
  • Uses of Execution Plans
  • Reading an Execution Plan
  • SQL Trace Facility
  • Generate an Optimizer Trace

Influencing the Optimizer

  • Functions of the Query Optimizer
  • Selectivity,Cardinality and Cost
  • Optimizer Statistics
  • Extended Statistics
  • Adaptive Execution Plans
  • Using Hints
  • Access Paths
  • Join Operations and Sort Operations

Reducing the Cost of SQL Operations

  • Index Maintenance
  • SQL Access Advisor
  • Table Reorganization Methods
  • Space Management
  • Extent Management
  • Migration and Chaining
  • Shrinking Segments
  • Data Compression

Using SQL Performance Analyzer

  • Capturing the SQL Workload
  • Creating a SQL Performance Analyzer Task
  • Comparison Report
  • Tuning Regressing Statements
  • SQL Tuning Recommendations
  • Preventing Regressions
  • Tuning Regressed SQL Statements

SQL Performance Management

  • Maintaining Optimizer Statistics
  • Setting Statistic Preferences
  • Using the Optimizer Statistics Advisor
  • Deferred Statistics Publishing
  • Automatic SQL Tuning
  • SQL Tuning Advisor
  • Using the SQL Access Advisor
  • SQL Plan Management

Using Database Replay

  • Capture Considerations
  • Replay Considerations
  • Replay Analysis
  • Database Replay Packages
  • Calibrating Replay Clients

Tuning the Shared Pool

  • Shared Pool Architecture
  • The Library Cache
  • Latch and Mutex
  • Diagnostic Tools for Tuning the Shared Pool
  • Sizing the Shared Pool
  • Avoiding Fragmentation
  • Data Dictionary Cache
  • SQL Query Result Cache

Tuning the Buffer Cache

  • Database Buffers
  • Symptoms of a Buffer Cache Issue
  • Solutions for Buffer Cache Issues
  • Memoptimized Rowstore
  • Automatically Tuned Multiblock Reads
  • Database Smart Flash Cache
  • Force Full Database Caching
  • Flushing the Buffer Cache

Tuning PGA and Temporary Space

  • SQL memory usage
  • SQL Memory Manager
  • Configuring Automatic PGA Memory
  • Managing PGA for PDBs
  • PGA Target Advice Statistics and Histograms
  • Temporary Tablespace Management
  • Temporary Tablespace Group
  • Shrinking a Temporary Tablespace

Automatic Memory

  • Dynamic SGA
  • Memory Advisories
  • Automatic Shared Memory Management
  • Memory Broker Architecture
  • Memory Management for CDBs
  • Managing SGA for PDBs
  • SGA Advisor
  • Automatic Memory Management

Performance Tuning Summary with Waits

  • Automatic Checkpoint Tuning
  • Sizing the Redo Log Buffer
  • Sizing Redo Log Files
  • Increasing the Performance of Archiving
  • General Tablespace and Undo Tablespace Best Practices
  • SQL Execution Related Waits
  • Internal Fragmentation Considerations
  • I/O Modes

When does class start/end?

Classes begin promptly at 9:00 am, and typically end at 5:00 pm.

Does the course schedule include a Lunchbreak?

Lunch is normally an hour long and begins at noon. Coffee, tea, hot chocolate and juice are available all day in the kitchen. Fruit, muffins and bagels are served each morning. There are numerous restaurants near each of our centers, and some popular ones are indicated on the Area Map in the Student Welcome Handbooks - these can be picked up in the lobby or requested from one of our ExitCertified staff.

How can someone reach me during class?

If someone should need to contact you while you are in class, please have them call the center telephone number and leave a message with the receptionist.

What languages are used to deliver training?

Most courses are conducted in English, unless otherwise specified. Some courses will have the word "FRENCH" marked in red beside the scheduled date(s) indicating the language of instruction.

What does GTR stand for?

GTR stands for Guaranteed to Run; if you see a course with this status, it means this event is confirmed to run. View our GTR page to see our full list of Guaranteed to Run courses.

Does ExitCertified deliver group training?

Yes, we provide training for groups, individuals and private on sites. View our group training page for more information.

I was familiar with some knowledge on AWS Cloud, however the instructor Lopez explained stuff that made it easy to understand. He was very knowledgeable with the information he provided during class

The course and labs were well organized and performed flawlessly. They made sure that all the information was timely relayed to the students.

Very good intro class. Nice to have a support staff available, even it not required.

It was a great learning experience last week and a great exposure to AWS as i am not very technical. I am looking forward to join the classes with exitcert and take the foundational exam.

Class & instructor was very knowledgeable. I can honestly say I received a lot of information that I am retaining for my exam. Material was taught in the right tone for IT and non IT students.

8 options available

  • Oct 19, 2020 Oct 23, 2020 (5 days)
    08:00 AM 16:00 PM GMT
  • Oct 26, 2020 Oct 30, 2020 (5 days)
    06:00 AM 14:00 PM PST
  • Nov 16, 2020 Nov 20, 2020 (5 days)
    Virtual - French, Virtual
    9:00 AM 5:00 PM GMT
  • Nov 16, 2020 Nov 20, 2020 (5 days)
    King of Prussia, PA
    09:00 AM 17:00 PM EST
  • Nov 16, 2020 Nov 20, 2020 (5 days)
    Edison, NJ
    09:00 AM 17:00 PM EST
  • Nov 16, 2020 Nov 20, 2020 (5 days)
    King of Prussia, PA
    09:00 AM 17:00 PM EST
  • Nov 16, 2020 Nov 20, 2020 (5 days)
    Edison, NJ
    09:00 AM 17:00 PM EST
  • Nov 23, 2020 Nov 27, 2020 (5 days)
    09:00 AM 17:00 PM EST
Contact Us 1-800-803-3948
Contact Us Live Chat
FAQ Get immediate answers to our most frequently asked qestions. View FAQs arrow_forward