Oracle Database: Performance Management and Tuning

Overview

• Use the Oracle tuning methodology

• Use Oracle-supplied tools for monitoring and diagnosing SQL and instance tuning issues

• Use database advisors to proactively correct performance problems

• Identify problem SQL statements

• Tune SQL performance problems

• Monitor instance performance by using Enterprise Manager

• Tune instance components by primarily using instance parameter

Audience

• Administrator

• Database Administrator

Prerequisites

Suggested Prerequisite

• Oracle Database: Install and Upgrade Workshop

• Familiarity with Oracle Database installation

• Familiarity with Oracle Database configuration concepts

Required Prerequisite

• Oracle Database: Administration Workshop

• Basic knowledge of Linux operating system

• A working knowledge of SQL and PL/SQL packages

• Basic understanding of Oracle Database architecture

• Familiarity with basic database monitoring procedures

Delegates will learn how to

• Describe Performance Management

• Use AWR Performance Statistics

• Use AWR Based Tools

• Improve SQL Statement Performance

• Influence the Optimizer

• Use Tuning Analyzers and Advisors

• Monitor Operations and applications

• Manage Database Memory

• Use In-Memory Column Store Feature

Outline

Overview & Preparing the Databases

• Defining the Scope of Performance Issues, Tuning Life Cycle Phases and Using Enterprise Manager to Identify OS Issues

• Using the Time Model to Diagnose Performance Issues

• Using Statistics and Wait Events to Diagnose Performance Issues

• Using Log and Trace Files to Monitor Performance

• Using Enterprise Manager Cloud Control and SQL Developer to Monitor Performance

• Using Statspack to View Performance Data: Installing Statspack, Creating Snapshots and Generating Statspack Reports

• Using Automatic Workload Repository

• Setting Up and Viewing Server-Generated Alerts

• Using Baselines

• Configuring and Managing Automated Maintenance Tasks

• Using ADDM to Analyze Performance and AWR-Based Tools to Identify Performance Issues

• Using Active Session History Data for First Fault System Analysis and Generating and Reviewing an ASH Report to Identify Performance Issues

• Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues

Overview of SQL Statement Processing

• Maintaining Indexes

• Maintaining Tables: Block Space Management, Shrinking Segments, Advanced Index Compression, Analyzing the Impact of Excess Blocks

• Introduction to Query Optimizer and Using Initialization Parameters to Control Optimizer Behavior

• Understanding Execution Plans, Using AUTOTRACE and EXPLAIN PLAN

• Viewing Execution Plans by Using SQL Trace and the TKPROF Utility

• Managing Optimizer Statistics

• Using Automatic SQL Tuning

• Using the SQL Plan Management Feature

Overview of the SQL Advisors

• Using the SQL Tuning Advisor and SQL Access Advisor to Improve SQL Performance

Overview of Real Application Testing Components

• Using SQL Performance Analyzer to Determine the Impact of Changes

• Using Database Replay to Test System Performance

• Implementing Real-Time Database Operation Monitoring

• Using Services to Monitor Applications

Overview of Memory Structures

• Managing Shared Pool Performance

• Managing Buffer Cache Performance

• Managing PGA and Temporary Space Performance

• Configuring the Large Pool

• Using Automatic Shared Memory Management

• Introduction to In-Memory Column Store

• Configuring the In-Memory Column Store Feature

• Using the In-Memory Column Store Feature to Improve SQL Performance

• Using In-Memory Column Store with Oracle Database Features