Oracle Database 12c: Performance Management and Tuning Training NEW

1. Introduction

• Course Objectives Agenda, & Organization
  
• Topics Not Included in the Course
  
• Tuning Methodology
  
• Who Tunes? What Does the DBA Tune?
  
  

2. Basic Tuning Diagnostics

• Performance Tuning Diagnostics & Tools
  
• Tuning Objectives
  
• Top Timed Events & DB Time
  
• Time Model
  
• CPU and Wait Time Tuning Dimensions
  
• Dynamic Performance Views
  
  

3. Using Automatic Workload Repository

• Automatic Workload Repository Data
  
• Automatic Workload Repository Overview
  
• Enterprise Manager Cloud Control and AWR
  
• Compare Periods
  
• Snapshots & Reports
  
  

4. Defining the Scope of Performance Issues

• Define the Problem
  
• Define the Scope & Set the Priority
  
• Top SQL Reports
  
• Common Tuning Problems
  
• Tune During the Life Cycle
  
• Performance Versus Business Requirements
  
• ADDM Tuning Session
  
  

5. Metrics and Alerts Usage

• Metrics and Alerts Overview
  
• Limitation of Base Statistics
  
• Benefits of Metrics
  
• View Metric History Information
  
• View Histograms
  
• Server-Generated Alerts
  
• Set Thresholds
  
• Metrics and Alerts Views
  
  

6. Using Baselines

• Comparative Performance Analysis with AWR Baselines
  
• Automatic Workload Repository Baselines
  
• Move Window Baseline
  
• Baselines in Performance Page Settings
  
• Baseline Templates
  
• Create AWR Baselines
  
• Manage Baselines with PL/SQL
  
  

7. Using AWR-Based Tools

• Automatic Maintenance Tasks
  
• ADDM Performance Monitoring
  
• Use Compare Periods ADDM
  
• Real-time ADDM
  
• Active Session History
  
• New or Enhanced Automatic Workload Repository Views
  
• Emergency Monitoring
  
  

8. Real-Time Database Operation Monitoring

• Define a Database Operation
  
• Database Operation Concepts
  
• Scope of a Composite Database Operation
  
• Identify a Database Operation
  
• Enable Monitoring of Database Operations
  
• Identify, Start, and Complete a Database Operation
  
  

9. Monitoring Applications

• Define a Service
  
• Service Attributes
  
• Create Services
  
• Service Types
  
• Manage Services in a Single-Instance Environment
  
• Deployment of Services
  
• Deploy Services with Client Applications
  
• Services and Pluggable Databases
  
  

10. Identifying Problem SQL Statements

• Role of the Oracle Optimizer
  
• Identify Bad SQL
  
• Top SQL Reports
  
• SQL Statement Processing Phases
  
• SQL Monitoring
  
• Define an Execution Plan
  
• Methods to View Execution Plans
  
• Uses of Execution Plans
  
  

11. Influencing the Optimizer

• Functions of the Query Optimizer
  
• Cardinality and Cost
  
• Changing Optimizer Behavior
  
• Optimizer Statistics
  
• Extended Statistics
  
• Control the Behavior of the Optimizer with Parameters
  
• Enable Query Optimizer Features
  
  

12. Reducing the Cost of SQL Operations

• Cost Reduction
  
• Index Maintenance
  
• SQL Access Advisor
  
• Table Maintenance for Performance
  
• Table Re-Organization Methods
  
• Space Management & Extent Management
  
• Data Storage
  
  

13. Using SQL Performance Analyzer

• Real Application Testing: Overview & Use Cases
  
• SQL Performance Analyzer: Process
  
• Capture the SQL Workload
  
• Create a SQL Performance Analyzer Task
  
• SQL Performance Analyzer: Tasks
  
• Parameter Change
  
• SQL Performance Analyzer Task Page
  
  

14. SQL Performance Management

• Maintain SQL Performance
  
• Maintain Optimizer Statistics
  
• Automated Maintenance Tasks
  
• Statistic Gathering Options
  
• Set Statistic Preferences
  
• Restore Statistics
  
• Deferred Statistics Publishing
  
• Automated SQL Tuning
  
  

15. Using Database Replay

• Database Replay Usage
  
• The Big Picture
  
• System Architecture
  
• Capture Considerations
  
• Replay Considerations: Preparation, Options & Analysis
  
  

16. Tuning the Shared Pool

• Shared Pool Architecture & Operation
  
• The Library Cache
  
• Latch and Mutex
  
• Diagnostic Tools for Tuning the Shared Pool
  
• Avoid Hard Parses
  
• Reduce the Cost of Soft Parses
  
• Size the Shared Pool
  
  

17. Tuning the Buffer Cache

• Oracle Database Architecture: Buffer Cache
  
• Buffer Cache: Highlights
  
• Buffer Hash Table for Lookups
  
• Database Buffers
  
• Working Sets
  
• Buffer Cache Performance Symptoms & Solutions
  
• Buffer Cache Tuning Goals and Techniques
  
  

18. Tuning PGA and Temporary Space

• SQL Memory Usage & Memory Manager
  
• Performance Impact
  
• Automatic PGA Memory
  
• Configure Automatic PGA Memory
  
• Set PGA_AGGREGATE_TARGET Initially
  
• Limit the size of the Program Global Area (PGA)
  
  

19. Automatic Memory

• Oracle Database Architecture
  
• Dynamic SGA
  
• Memory Advisories
  
• Manually Adding Granules to Components
  
• Increasing the Size of an SGA Component
  
• Automatic Shared Memory Management: Overview
  
  

20. Performance Tuning Summary with Waits

• Commonly Observed Wait Events
  
• Additional Statistics
  
• Top 10 Mistakes Found in Customer Systems
  
  

21. Oracle Database Cloud Service: Overview

• Database as a Service Architecture, Features and Tooling
  
• Access the Oracle Database Cloud Service Console & Automated Database Provisioning
  
• Software Editions: Included Database Options and Management Packs
  
• Performance Monitoring and Tuning
  
• Manage Network Access to Database as a Service & Scaling a Database Deployment
  
• Manage the Compute Node Associated with a Database Deployment
  
• Performance Management in the Database Cloud Environment
  
• What can be tuned in a DBCS Environment?