Course Materials

• Slides

• Lab Guide

Lab Setup

• Links to download VM Images, Scripts, Swingbench1

• Lab Setup-Part1

• Lab Setup-Part2

PART 1: Diagnosing and Tuning Database Performance

• What you will learn?

Performance Tuning General Concepts

• Performance Tuning General Concepts

Database Statistics

• Measuring the Performance of the Database

• Lab - Measuring the Performance of the Database

Automatic Workload Repository (AWR)

• Overview of AWR

• Administering AWR

• Lab - Administering AWR - Part 1

• Lab - Administering AWR - Part 2

• About AWR Report

• Lab - Generating AWR Report - Part1

• Lab - Generating AWR Report - Part 2

• About Baselines

• About Baseline Templates

• Overview of managing Baselines and Baseline Templates

• Lab Practice: Managing Baselines and Baseline Templates

• Lab Practice: Managing Baselines and Baseline Templates using OEM CC

• About AWR Compare Periods Report

• Lab Practice: AWR Compare Periods Report

ADDM

• About ADDM

• About Real Time ADDM

• Lab Practices: ADDM

• Lab Practices: ADDM using OEM

• Lab Practice: Real Time ADDM in OEM

ASH

• About ASH

• Lab Practice: Using ASH in OEM

• Lab Practice: Querying ASH Views

• Lab Practice: Troubleshooting Performance issue using ASH Report

Statspack

• About Statspack

• Lab Practices: Using Statspack

Automated Maintenance Tasks

• About Automated Maintenance Tasks

• Overview of Configuring Automated Maintenance Tasks

• Lab Practice: Configuring Automated Maintenance Tasks

Oracle Database Advisors and Health Checkers

• About Oracle Database Advisors

• About Health Checkers

• Lab Practices: Advisors and Checkers

PART 2 : SQL Tuning

• What you will learn?

SQL Tuning Fundamentals

• SQL Tuning Fundamentals

• Processing of SQL Statements

Query Optimizer Fundamentals

• Optimizer Fundamentals

Query Execution Plans

• What is Execution Plan?

• How to display Execution Plans?

• Using AUTOTRACE to display Execution Plan

• Using EXPLAIN PLAN and DBMS_XPLAN to display Execution Plan

• How to Read Execution Plans?

• Lab Practices

Access Paths and Join Methods

• Overview of Access Paths

• Overview of Join Methods

• Overview of Join Order

• Lab Practices - Access Paths

• Lab Practices: Join Methods

Optimizer Hints

• Overview of Hints

• Lab Practices: Using Hints

Optimizer Statistics

• Overview of Optimizer Statistics

• Types of Optimizer Statistics

• How Database Gathers Statistics?

• Manually Gathering Optimizer Statistics

• Lab Practice: Optimizer Statistics Concepts

• Lab Practice: Gathering Optimizer Statistics

• Lab Practice: Histograms

• Lab Practice: Column Group Statistics

• Lab Practice: Expression Statistics

• Configuring Options for Optimizer Statistics Gathering

• Lab Practice: Configuring Options for Optimizer Statistics Gathering

• Managing Optimizer Statistics

• Lab Practices: Locking and Unlocking Statistics, Publishing Pending Statistics, Using Artificial Statistics

• Lab Practice: Exporting and Importing Optimizer Statistics

• Lab Practice: Managing Historical Optimizer Statistics

Monitoring Database Operations

• Monitoring Database Operations

• Lab Practices: Monitoring Database Operations

End-to-End Application Tracing

• What is End-to-End Application Tracing?

• Tools for Tracing

• SQL Trace

• TRCESESS

• TKPROF

• Lab Practices: End-to-End Application Tracing1

SQL Tuning Set (STS)

• Overview of SQL Tuning Set

• Lab Practice: SQL Tuning Set

SQL Tuning Advisor

• Overview of SQL Tuning Advisor

• Managing Automatic SQL Tuning Task

• Running SQL Tuning Advisor On Demand

• Lab Practices: SQL Tuning Advisor

SQL Access Advisor

• Overview of SQL Access Advisor

• SQL Access Advisor - Basic Tasks

• SQL Access Advisor - Quick Tune

• SQL Access Advisor - Advanced Tasks

• Lab Practices: SQL Access Advisor

SQL Profiles

• Overview of SQL Profiles

SQL Plan Management

• Overview of SQL Plan Management

• Basic Tasks in SPM

• Lab Practices: Managing SPM

SQL Performance Analyzer

• Overview of SQL Performance Analyzer

• Lab Practices: Using SPA

PART 3: Tuning Database Memory

• What you will learn?

Tuning Shared Pool

• Overview of Shared Pool

• Memory Allocation in Shared Pool

• Private and Shared SQL Areas

• Latches and Mutexes

• Shared Pool Common Performance Issues and Indicators

• Tuning the Shared Pool

• Lab Practices: Tuning Shared Pool

Tuning Database Buffer Cache

• Basics of Buffer Cache

• Symptoms of Buffer Cache Issues

• Solutions for Buffer Cache Issues

• Lab Practices: Configuring KEEP POOL

Tuning Redo Log

• Overview of Tuning Redo Log

• Lab Practices: Tuning the Redo Log

Tuning the PGA and Temporary Space

• Overview of Tuning PGA and Temporary Tablespace

• Automatic and Manual PGA Management

• Statistics, Wait Events and DD View related to PGA

• Diagnosing PGA Performance Issues

• Tuning Temporary Tablespace

• Lab Practices: Tuning the PGA