Oracle 11G Performance Tuning

Have Queries? Ask us +91 72592 22234

Course Overview


Participants learn how to use Oracle Database 11g automatic tuning features such as SQL Tuning Advisor, SQL Access Advisor, Automatic Workload Repository and Automatic Database Diagnostic Monitor, and practice these tuning methods. Oracle 11G Performance Tuning Training focuses on the tuning tasks expected of a DBA: reactive tuning of SQL statements, maintaining SQL statement performance, and tuning the Oracle Database Instance components. Throughout Oracle 11G Performance Tuning course, Participants practice the art of tuning an Oracle Instance through a series of workshops. The methodology is practiced in the workshops rather than taught. Oracle 11G Performance Tuning course does not address partition tuning, materialized views, or RAC specific issues as they are covered in courses specifically for these products. Oracle 11G Performance Tuning course makes use of many features that require the Enterprise Edition and optional Packs. Oracle 11G Performance Tuning course counts towards the Hands-on course requirement for the Oracle Database 11g Administrator Certification. Only instructor-led inclass or instructor-led online formats of Oracle 11G Performance Tuning course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement. By the end of this training participants will learn: Use the Oracle Database tuning methodology appropriate to the available tools Utilize database advisors to proactively tune an Oracle Database Instance Use the tools based on the Automatic Workload Repository to tune the database. Diagnose and tune common SQL related performance problems Diagnose and tune common Instance related performance problems Use Enterprise Manager performance-related pages to monitor an Oracle Database

Course Content


 Introduction

  • Cause of Performance Problems
  • Setting Performance Goals
  • The Tuning Cycle
  • ORACLE Architecture
  • Logical Storage Structures
  • Physical Structures
  • Memory Structures
  • The Shared Pool
  • Processes
  • Some Administration Terminology

Design

  • Data Design Phase
  • Data Model Design
  • Online Transaction Processing
  • Decision Support Systems
  • Multi-purpose Applications

Optimizing SQL

  • Physical Retrieval of Data
  • Full Table Scan versus Index Reads
  • Performance Diagnostic Tools
  • Explain Plan
  • SQL Trace Facility
  • TKPROF
  • Autotrace
  • Join Methods
  • Sort/Merge Joins
  • Nested Loops
  • Hash Joins
  • Hash Join Example Data Access Methods

Indexes

  • Basic Indexes
  • B-Tree Indexes
  • Bitmap Index
  • Comparing B-Tree and Bitmap Indexes
  • Reverse Key Index
  • Index-Organised Tables
  • Invisible index
  • Creating Monitoring and Maintaining Indexes

Automatic SQL Tuning

  • Query Optimizer Modes Types of Tuning Analysis
  • Automatic workload repository SQL Tuning Advisor SQL Tuning Sets Top SQL
  • Identify high-load SQL Dynamic Performance views

SQL Performance Analyzer

  • Describe SQL Performance Analyzer process and benefits
  • Use SQL Performance Analyzer

The Optimizer

  • The ORACLE Optimizer
  • SQL statement parsing
  • Initialisation parameters
  • Rule Based Optimizer
  • Cost Based Optimizer
  • Rule / Cost Comparisons
  • Choosing an Approach
  • Multiple WHERE Clauses
  • Using Indexes for Sorts
  • Multiple Table Joins
  • Disabling Indexes
  • Hints
  • Sharing SQL Statements
  • Sharing Cursors
  • Hints
  • Sharing SQL Statements
  • Sharing Cursors
  • Adaptive Cursors Sharing
  • Other SQL Tuning Tips

Gathering Statistics

  • Analyzing Statistics
  • DBMS_STATS
  • Automatic Optimizer Statistics Collection
  • Histograms
  • How to Generate Histograms Statistics

SQL Plan Management

  • SQL outlines
  • SQL profiles
  • SQL Access Advisor
  • Set up and use SQL plan baseline

Advanced Tuning

  • Star Queries
  • Materialized Views
  • Refreshing Views
  • Materialized View Logs
  • SQL Result Cache
  • Temporary Tables

Statistics and Wait Events

  • Identify dynamic performance views useful in tuning
  • Identify key tuning components of the alert log file
  • Identify key tuning components of user trace files
  • Use dynamic performance views to view statistics and wait events

Metrics, Alerts, and Baselines

  • View metrics using the metrics history views
  • Create metric thresholds
  • View alerts
  • Create metric baselines
  • Enable adaptive thresholds

Using Statspack

  • Install Statspack
  • Create Statspack snapshots
  • Generate Statspack reports
  • Identify the major sections of the Statspack report

Using Statspack

  • Create and manage AWR snapshots
  • Generate AWR reports
  • Create snapshot sets and compare periods
  • Generate ADDM reports
  • Generate ASH reports

Customer Reviews


Thanks to Xpertised and the tutor who walked me through all the topics with Practical exposure which is helping me in my current project.
-Waseem

Course was quite helpful in terms of understanding of concepts and practicality. Its really a very friendly environment to learn. The timing were mutually chosen, as we both are working professional. I am quite satisfied with the course.
-Tanmoy

...more
Share:

For Batch Details
Call us at: +91 7259222234

Not sure? Consult Our Experts

Looking for a Training for

Myself

My Team/Organization

I agree to be contacted over mail or phone

or
Call us at: +91 7259222234