Oracle 11g SQL Performance Tuning

Have Queries? Ask us +91 72592 22234

Course Overview


Oracle 11g SQL Performance Tuning Training introduces the delegate to the main concepts of Oracle SQL performance tuning. Oracle 11g SQL Performance Tuning is designed to give delegates practical experience in analyzing and tuning the performance of SQL. Oracle 11g SQL Performance Tuning course is suitable for users of Oracle Database 10g and Oracle Database 11g.

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

  • SQL Troubleshooting Tips and Techniques
    • Index Review/Tips & Techniques
      • Identify good and poor indexes
      • Monitor Index Usage
      • Using Bit-map Indexes
    • A close look at sub-query coding techniques
  • 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
  • Adaptive Cursors Sharing
  • Other SQL Tuning Tips

Gathering Statistics

  • Analyzing Statistics
  • DBMS_STATS
  • Automatic Optimizer Statistics Collection
  • Histograms

How to Generate Histograms Statistics

  • Tuning the SGA (including new 11g topics)
  • Additional Oracle Hash and Sort Trace information

Oracle Internals: How Oracle writes

  • Various Space Management Issues discussed
  • Monitoring Sorting
  • Finding Problem SQL using V$ information
  • Tuning the network/Tuning the client

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 AWR

  • Create and manage AWR snapshots
  • Generate AWR reports
  • Create snapshot sets and compare periods
  • Generate ADDM reports
  • Generate ASH reports
  • Using the SQL Tuning Advisor

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