Module 1: Overview

Topics

• What performance management involves and who owns it
• Types of tuning and effective tuning goals
• Performance tuning diagnostics, features, and tools

Labs / Practice

• Review performance tuning objectives and methodology

Module 2: Defining the Scope of Performance Issues

Topics

• Defining and limiting the scope of a performance problem
• Determining tuning priorities and common tuning problems
• Tuning across the application lifecycle: design, testing, deployment, production, and migration

Labs / Practice

• Practice scoping and prioritizing a performance issue

Module 3: Using the Time Model to Diagnose Performance Issues

Topics

• Time Model overview and DB Time
• CPU and wait time tuning dimensions
• Time Model statistics hierarchy and top timed events

Labs / Practice

• Diagnose performance issues using the Time Model

Module 4: Using Statistics and Wait Events to Diagnose Performance Issues

Topics

• Dynamic performance views and statistic levels
• Instance activity and wait event statistics
• Wait classes and commonly observed wait events
• The V\$SESSION_WAIT and V\$EVENT_NAME views

Labs / Practice

• Diagnose performance using statistics and wait events

Module 5: Using Log and Trace Files to Monitor Performance

Topics

• Viewing and using the alert log for performance management
• Administering the DDL log file and debug log file
• User and background process trace files

Labs / Practice

• Review alert log and trace file information

Module 6: Using Enterprise Manager Cloud Control and SQL Developer to Monitor Performance

Topics

• Enterprise Manager overview and Database Express configuration
• Oracle Management Packs and options
• Oracle SQL Developer and SQLcl for performance monitoring

Labs / Practice

• Monitor performance using Enterprise Manager and SQL Developer

Module 7: Using Statspack to View Performance Data

Topics

• Installing Statspack and capturing snapshots
• Snapshot levels, baselines, and purging
• Reading and drilling down into Statspack reports

Labs / Practice

• Install Statspack and generate a performance report

Module 8: Using Automatic Workload Repository

Topics

• AWR overview, data, and administration
• Managing snapshots and the snapshot purging policy
• Generating and reading AWR reports, including multitenant data
• Comparing periods using AWR

Labs / Practice

• Generate and interpret an AWR report

Module 9: Using Metrics and Alerts

Topics

• Metrics vs. base statistics and delta tools
• Oracle Database metrics and their benefits
• Statistic histograms and server-generated alerts

Labs / Practice

• Configure metrics and review server-generated alerts

Module 10: Using Baselines

Topics

• Comparative performance analysis with AWR baselines
• Types of baselines: fixed, moving window, and templates
• Creating and managing baselines with DBMS_WORKLOAD_REPOSITORY
• Defining alert thresholds from a static baseline

Labs / Practice

• Create and use AWR baselines for performance comparison

Module 11: Managing Automated Maintenance Tasks

Topics

• Automated maintenance tasks and the default maintenance plan
• Maintenance windows and task priorities
• Configuring automated maintenance tasks

Labs / Practice

• Configure automated maintenance tasks and priorities

Module 12: Using ADDM to Analyze Performance

Topics

• ADDM performance monitoring and its relationship to DB Time
• Top performance issues and ADDM recommendations
• Creating manual ADDM tasks and comparing periods
• ADDM in a multitenant architecture database

Labs / Practice

• Run ADDM analysis and interpret recommendations

Module 13: Using Active Session History Data for First Fault System Analysis

Topics

• Active Session History (ASH) overview and sampling mechanics
• Accessing and analyzing ASH data
• Generating ASH reports and performing skew analysis

Labs / Practice

• Generate and analyze an ASH report

Module 14: Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues

Topics

• Emergency monitoring challenges and goals
• Using Real-Time ADDM for root-cause analysis
• Viewing Real-Time ADDM results

Module 15: Overview of SQL Statement Processing

Topics

• SQL statement processing phases: parse, bind, execute, fetch
• Cursor storage and the session cursor cache
• Identifying poorly performing SQL statements and SQL Monitoring

Module 16: Maintaining Indexes

Topics

• Creating, dropping, and maintaining indexes
• Invisible and unusable indexes
• Advanced index compression and other index options
• The Automatic Indexing task and workflow

Labs / Practice

• Practice index creation, maintenance, and automatic indexing

Module 17: Maintaining Tables

Topics

• Table reorganization and space management methods
• Block space management, free lists, and Automatic Segment Space Management
• Detecting and eliminating row migration and chaining
• Shrinking segments and data/index compression

Labs / Practice

• Maintain tables using shrink, compression, and reorganization techniques

Module 18: Introduction to Query Optimizer

Topics

• Role and functions of the Oracle Optimizer
• Selectivity, cardinality, and cost
• Changing optimizer behavior and initialization parameters
• Access paths and how the optimizer chooses them

Module 19: Understanding Execution Plans

Topics

• What an execution plan is and how to view one
• The DBMS_XPLAN package and EXPLAIN PLAN command
• The V\$SQL_PLAN and related dynamic views
• Adaptive and dynamic execution plans

Labs / Practice

• Generate and read execution plans using multiple methods

Module 20: Viewing Execution Plans by Using SQL Trace and TKPROF

Topics

• SQL Trace facility and related initialization parameters
• Enabling and disabling SQL Trace
• Formatting trace files with TKPROF and reading TKPROF output
• Generating an optimizer trace

Labs / Practice

• Use SQL Trace and TKPROF to analyze SQL execution

Module 21: Managing Optimizer Statistics

Topics

• Types of optimizer statistics and collection methods
• Setting and managing statistics preferences and extended statistics
• The Optimizer Statistics Advisor and restoring statistics
• Real-time and high-frequency statistics collection

Labs / Practice

• Gather and manage optimizer statistics

Module 22: Using Automatic SQL Tuning

Topics

• Automatic SQL Tuning overview and SQL statement profiling
• Plan tuning flow and SQL Profile creation
• The SQL Tuning Loop

Module 23: Using the SQL Plan Management Feature

Topics

• SQL Plan Management and SQL Plan Baseline architecture
• Loading and evolving SQL Plan Baselines, including from AWR
• Adaptive SQL Plan Management
• Purging the SQL Management Base

Labs / Practice

• Load and evolve SQL plan baselines

Module 24: Overview of the SQL Advisors

Topics

• SQL Tuning Advisor overview
• SQL Access Advisor overview
• SQL Performance Analyzer overview

Module 25: Using the SQL Tuning Advisor

Topics

• SQL Tuning Advisor architecture and the Automatic Tuning Optimizer
• Running the SQL Tuning Advisor and reviewing options
• Interpreting recommendations and alternative execution plans

Labs / Practice

• Run the SQL Tuning Advisor and apply recommendations

Module 26: Using the SQL Access Advisor

Topics

• SQL Access Advisor overview and usage
• Viewing recommendations and recommendation details

Labs / Practice

• Run the SQL Access Advisor and review recommendations

Module 27: Overview of Real Application Testing Components

Topics

• Real Application Testing overview
• Real Application Testing use cases

Module 28: Using SQL Performance Analyzer to Determine the Impact of Changes

Topics

• SQL Performance Analyzer process and workflow
• Capturing SQL workload and creating an analysis task
• Comparing and tuning regressed SQL statements

Labs / Practice

• Use SQL Performance Analyzer to assess a system change

Module 29: Using Database Replay to Test System Performance

Topics

• Database Replay architecture: capture, process, and replay
• Database Replay workflow in Enterprise Manager
• Capture and replay considerations, and replay analysis
• Capturing and replaying in a CDB and PDBs

Labs / Practice

• Capture and replay a production workload using Database Replay

Module 30: Implementing Real-Time Database Operation Monitoring

Topics

• Defining and scoping a database operation
• Enabling and monitoring database operations in sessions
• Monitoring load database operations and related views

Labs / Practice

• Monitor real-time database operations

Module 31: Using Services to Monitor Applications

Topics

• Service attributes, types, and creation
• Using services with client applications, Resource Manager, and the Scheduler
• Service thresholds, alerts, aggregation, and tracing

Labs / Practice

• Create and monitor services for application workloads

Module 32: Overview of Memory Structures

Topics

• Managing memory caches and structures
• Guidelines for efficient memory usage

Labs / Practice

• Review memory structure guidelines

Module 33: Managing Shared Pool Performance

Topics

• Shared pool architecture, library cache, latches, and mutexes
• Diagnostic tools and AWR/Statspack indicators for the shared pool
• Cursor sharing and adaptive cursor sharing
• Sizing the shared pool, avoiding fragmentation, and the SQL Query Result Cache

Labs / Practice

• Diagnose and tune shared pool performance

Module 34: Managing Buffer Cache Performance

Topics

• Database buffers, working sets, and buffer cache hit ratio
• Diagnosing buffer cache issues and contention
• Sizing the buffer cache and using the Buffer Cache Advisor
• Automatic Big Table Caching and multiple buffer pools

Labs / Practice

• Diagnose and tune buffer cache performance

Module 35: Managing PGA and Temporary Space Performance

Topics

• SQL memory usage and Automatic PGA Memory Management
• Monitoring and tuning SQL memory usage
• Temporary tablespace management and tablespace groups

Labs / Practice

• Configure and monitor PGA and temporary tablespace performance

Module 36: Configuring the Large Pool

Topics

• Large pool overview and use cases
• Tuning the large pool

Module 37: Using Automatic Shared Memory Management

Topics

• Oracle Database memory architecture and granules
• Automatic Shared Memory Management overview and SGA sizing parameters
• Manually and automatically resizing SGA components
• Managing SGA for PDBs

Labs / Practice

• Configure and monitor Automatic Shared Memory Management

Module 38: Introduction to In-Memory Column Store

Topics

• Database In-Memory feature set and goals
• Row store vs. column store architecture
• Dual format in-memory segments

Module 39: Configuring the In-Memory Column Store Feature

Topics

• Deploying the In-Memory Column Store: objects, columns, and compression
• The In-Memory Advisor and compression ratio computation
• IM FastStart and Automatic In-Memory

Labs / Practice

• Configure the In-Memory Column Store feature

Module 40: Using the In-Memory Column Store Feature to Improve SQL Performance

Topics

• Query benefits and performance comparison
• In-memory queries on simple predicates and joins
• Populating in-memory expression results

Labs / Practice

• Compare SQL performance with and without the In-Memory Column Store

Module 41: Using In-Memory Column Store with Oracle Database Features

Topics

• Interaction with the Optimizer and Real Application Clusters
• In-Memory Column Store and Data Pump
• Automatic Data Optimization and Heat Map policies

Labs / Practice

• Configure Automatic Data Optimization policies with the In-Memory Column Store