Oracle Database: Performance Management and Tuning

Course Outline

Topics

• 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