OD-AI-PMT: Oracle AI Database: Performance Management and Tuning

Module 1: Overview

• Overview
• Objectives
• What is performance management?
• Who manages performance?
• What does the DBA tune?
• Types of tuning
• Tuning methodology
• Effective tuning goals
• General tuning session
• Tuning a CDB
• Performance tuning: diagnostics
• Performance tuning: features and tools
• Tuning objectives
• Summary

Module 2: Defining the Scope of Performance Issues

• Objectives
• Defining the problem
• Limit the scope
• Determining tuning priorities
• Common tuning problems
• Tuning life cycle phases
• Tuning during the life cycle
• Application design and development
• Testing: database configuration
• Deployment
• Production
• Migration, upgrade, and environment changes
• ADDM tuning session
• Performance versus business requirements
• Monitoring and tuning tools: overview
• Summary

Module 3: Using the Time Model to Diagnose Performance Issues

• Objectives
• Time model: overview
• DB time
• CPU and wait time tuning dimensions
• Time model statistics hierarchy
• Time model: example
• Top timed events
• Summary

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

• Objectives
• Dynamic performance views
• Dynamic performance views: usage examples
• Dynamic performance views: considerations
• Statistic levels
• Instance activity and wait event statistics
• System statistic classes
• Displaying statistics
• Displaying SGA statistics
• Wait events
• Using the V$EVENT_NAME view
• Wait classes
• Displaying wait event statistics
• Commonly observed wait events
• Using the V$SESSION_WAIT view
• Precision of system statistics
• Summary

Module 5: Using Log and Trace Files to Monitor Performance

• Objectives
• Viewing the alert log
• Using alert log information as an aid in managing performance
• Administering the DDL log file
• Understanding the debug log file
• User trace files
• Background process trace files
• Summary

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

• Objectives
• Enterprise Manager: overview
• Oracle Enterprise Manager Cloud Control components
• Using features of the Oracle management packs and options
• Oracle SQL Developer
• SQL Developer command line (SQLcl)
• Summary

Module 7: Using Statspack to View Performance Data

• Objectives
• Introduction to Statspack
• Statspack scripts
• Installing Statspack
• Capturing Statspack snapshots
• Configuring snapshot data capture
• Statspack snapshot levels
• Statspack baselines and purging
• Reporting with Statspack
• Statspack considerations
• Statspack reports
• Reading a Statspack report
• Statspack report drilldown sections
• Report drilldown examples
• Load profile section
• Time model section
• Statspack and AWR
• Summary

Module 8: Using Automatic Workload Repository

• Objectives
• Automatic Workload Repository: overview
• Automatic Workload Repository data
• Workload repository
• AWR administration
• AWR snapshot purging policy
• Managing snapshots with PL/SQL
• AWR snapshot settings
• Manual AWR snapshots
• Managing AWR data in a multitenant environment
• AWR snapshots and ADDM in a multitenant architecture database
• Generating AWR reports
• Reading the AWR report
• AWR report: multitenant data
• Generating AWR reports by using SQL*Plus
• Statspack and AWR reports
• Reading a Statspack or an AWR report
• Compare periods: benefits
• Snapshots and periods comparisons
• Compare periods: results
• Compare periods: report
• Multitenant AWR views
• Summary

Module 9: Using Metrics and Alerts

• Objectives
• Metrics and alerts
• Limitation of base statistics
• Typical delta tools
• Oracle Database metrics
• Benefits of metrics
• Viewing metric history information
• Viewing metric details
• Statistics histograms
• Histogram views
• Server-generated alerts
• Alert usage model
• Metric and alert views
• Summary

Module 10: Using Baselines

• Objectives
• Comparative performance analysis with AWR baselines
• Automatic Workload Repository baselines
• AWR baselines
• Types of baselines
• Moving window baseline
• Baseline templates
• Creating AWR baselines
• Creating a single AWR baseline
• Creating a repeating baseline and template
• Managing baselines using the DBMS_WORKLOAD_REPOSITORY package
• Generating a baseline template for a single time period
• Creating a repeating baseline template
• Baseline views
• Performance monitoring and baselines
• Summary

Module 11: Managing Automated Maintenance Tasks

• Objectives
• Automated maintenance tasks
• Maintenance windows
• Default maintenance plan
• Automated maintenance task priorities
• Configuring automated maintenance tasks
• Summary

Module 12: Using ADDM to Analyze Performance

• Objectives
• ADDM performance monitoring
• ADDM and database time
• DB time graph and ADDM methodology
• Top performance issues detected
• ADDM recommendations
• Creating a manual ADDM task
• ADDM tasks in a multitenant architecture database
• Changing ADDM attributes
• Retrieving ADDM reports using SQL
• Compare period ADDM: analysis
• Workload compatibility
• Configuring automatic ADDM analysis at the PDB level
• Using the DBMS_ADDM package to compare periods
• Summary

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

• Objectives
• Active Session History: overview
• Active Session History: mechanics
• ASH sampling: example
• Accessing ASH data
• Analyzing the ASH data
• Using Enterprise Manager to view ASH analysis
• Using Enterprise Manager to generate ASH reports
• Using the ASH report script to generate a report
• ASH report structure
• Determining the source of data
• Performing skew analysis
• Additional Automatic Workload Repository views
• Summary

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

• Objectives
• Emergency monitoring: challenges
• Emergency monitoring: goals
• Using Real-Time ADDM to perform a root cause analysis
• Using Real-Time ADDM
• Real-Time ADDM in the database
• Viewing Real-Time ADDM results
• Summary

Module 15: Overview of SQL Statement Processing

• Objectives
• SQL statement processing phases
• Parsing
• SQL cursor storage
• Session cursor cache
• Cursor usage and parsing
• Bind phase
• Execute and fetch phases
• Processing a DML statement
• Commit processing
• Identifying poorly performing SQL statements
• Top SQL reports
• SQL monitoring
• Monitored SQL execution details
• Summary

Module 16: Maintaining Indexes

• Objectives
• Creating indexes
• Using invisible and unusable indexes
• Dropping indexes
• Reducing the cost of SQL operations
• Index maintenance
• Using advanced index compression
• Other index options
• SQL Access Advisor
• Automatic indexing task
• Automatic indexing task workflow
• Automatic indexing task reporting
• Automatic indexing views
• Summary

Module 17: Maintaining Tables

• Objectives
• Reducing the cost of SQL operations
• Table maintenance for performance
• Table reorganization methods
• Space management
• Extent management
• Locally managed extents
• Large extents: considerations
• How table data is stored
• Anatomy of a database block
• Minimizing block visits
• Block allocation
• Free lists
• Block space management
• Block space management with free lists
• Automatic segment space management
• Automatic segment space management at work
• Block space management with ASSM
• Creating an automatic segment space management segment
• Migration and chaining
• Guidelines for PCTFREE and PCTUSED
• Detecting migration and chaining
• Selecting migrated rows
• Eliminating migrated rows
• Shrinking segments: overview
• Shrinking segments: considerations
• Shrinking segments by using SQL
• Segment shrink: basic execution
• Segment shrink: execution considerations
• Data compression
• Advanced row compression: overview
• Advanced row compression: concepts
• Using advanced row compression
• Advanced row compression for DML operations
• Advanced index compression
• How hybrid columnar compression works
• Using the compression advisor
• Using the compression advisor for indexes
• Viewing table compression information
• Summary

Module 18: Introduction to Query Optimizer

• Objectives
• Role of the Oracle Optimizer
• Functions of the Query Optimizer
• Selectivity
• Cardinality and Cost
• Changing Optimizer Behavior
• Setting and Viewing Optimizer Parameters
• Using Initialization Parameters to Control Optimizer Behavior
• Enabling Query Optimizer Features
• Influencing the Optimizer Approach
• Optimizing SQL Statements
• Access Paths
• Choosing an Access Path
• Summary

Module 19: Understanding Execution Plans

• Objectives
• What is an execution plan?
• Methods for Viewing Execution Plans
• Uses of Execution Plans
• DBMS_XPLAN Package: Overview
• EXPLAIN PLAN Command
• EXPLAIN PLAN Command: Example
• EXPLAIN PLAN Command: Output
• Reading an Execution Plan
• Using the V$SQL_PLAN View
• Querying V$SQL_PLAN
• V$SQL_PLAN_STATISTICS View
• Querying the AWR
• SQL*Plus AUTOTRACE
• Using SQL*Plus AUTOTRACE
• SQL*Plus AUTOTRACE: Statistics
• Adaptive Execution Plans
• Dynamic Plans
• Dynamic Plan: Adaptive Process
• Dynamic Plans: Example
• Continuous Adaptive Query Plans
• Automatic Re-Optimization
• Comparing Execution Plans
• Summary

Module 20: Viewing Execution Plans Using SQL Trace and TKPROF

• Objectives
• SQL Trace Facility
• How to Use the SQL Trace Facility
• Initialization Parameters
• Enabling SQL Trace
• Disabling SQL Trace
• Formatting Your Trace Files
• TKPROF Command Options
• Output of the TKPROF Command
• TKPROF Output with No Index: Example
• TKPROF Output with Index: Example
• Generating an Optimizer Trace
• Summary

Module 21: Managing Optimizer Statistics

• Objectives
• Optimizer Statistics
• Types of Optimizer Statistics
• Optimizer Statistics Collection
• Dynamic Statistics
• Gathering Statistics and Setting Optimizer Statistics Preferences
• Setting Statistic Preferences
• Viewing and Managing Optimizer Statistics Preferences
• Extended Statistics
• Maintaining Optimizer Statistics
• Automated Maintenance Tasks
• Optimizer Statistics Advisor
• Optimizer Statistics Advisor Report
• Executing Optimizer Statistics Advisor Tasks
• Restoring Statistics
• Deferred Statistics Publishing: Overview
• Deferred Statistics Publishing: Example
• Managing Real-Time Statistics
• Configuring High-Frequency Automatic Optimizer Statistics Collection
• Summary

Module 22: Using Automatic SQL Tuning

• Objectives
• Automatic SQL tuning: overview
• SQL profiling
• SQL tuning loop
• SQL profiles
• Summary

Module 23: Using the SQL Plan Management Feature

• Objectives
• SQL Plan Management: Overview
• SQL Plan Baseline: Architecture
• Loading SQL Plan Baselines
• Loading SQL Plan Baselines from AWR
• Evolving SQL Plan Baselines
• Important SQL Plan Baseline Attributes
• SQL Plan Selection
• Possible SQL Plan Manageability Scenarios
• SQL Performance Analyzer and SQL Plan Baseline Scenario
• Loading a SQL Plan Baseline Automatically
• Purging SQL Management Base Policy
• Enterprise Manager and SQL Plan Baselines
• Automatic SQL Plan Management
• SPM Evolve Advisor
• Summary

Module 24: Overview of the SQL Advisors

• Objectives
• SQL Tuning Process
• SQL Tuning Advisor: Overview
• SQL Access Advisor: Overview
• SQL Performance Analyzer: Overview
• Summary

Module 25: Using the SQL Tuning Advisor

• Objectives
• Real Application Testing overview
• Use cases
• SQL Performance Analyzer
• Database Replay
• Summary

Module 26: Using the SQL Access Advisor

• Objectives
• SQL Access Advisor: Overview
• Using the SQL Access Advisor
• Viewing Recommendations
• Viewing Recommendation Details
• Practice Overview
• Summary

Module 27: Overview of Real Application Testing Components

• Objectives
• Real Application Testing: Overview
• Real Application Testing: Use Cases
• Summary

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

• Objectives
• SQL Performance Analyzer: Process
• Steps 6–7: Comparing/Analyzing Performance and Tuning Regressed SQL
• Capturing the SQL Workload
• Creating a SQL Performance Analyzer Task
• SQL Performance Analyzer Task Page
• SQL Performance Analyzer Comparison Report
• SQL Performance Analyzer: PL/SQL Example
• Tuning Regressed SQL Statements
• SQL Performance Analyzer: Data Dictionary Views
• Summary

Module 29: Using Database Replay to Test System Performance

• Objectives
• Using Database Replay
• The Big Picture
• System Architecture: Capture
• System Architecture: Processing the Workload
• System Architecture: Replay
• Database Replay Workflow in Enterprise Manager
• Accessing Database Replay in Enterprise Manager
• Capture Considerations
• Replay Considerations: Preparation
• Replay Considerations
• Replay Customized Options
• Replay A