Call : (+91) 968636 4243
Mail : info@EncartaLabs.com
EncartaLabs

Oracle 11g SQL Performance Tuning

( Duration: 3 Days )

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

By attending Oracle SQL Performance Tuning workshop, delegates will learn:

  • Tuning Database Applications for Optimal Performance
  • Managing Statistics
  • Creating and Using Indexes
  • Structuring SQL Statements for Performance
  • Using the SQL Optimizers
  • Examining the Execution Plan of a SQL Statement using EXPLAIN PLAN
  • Examining the Efficiency of SQL Statements using SQL Trace and Autotrace
  • Using Hints to Influence Execution Plan
  • Identifying Unused Indexes

  • Oracle SQL knowledge
  • Oracle PL/SQL knowledge

The Oracle SQL Performance Tuning class is designed for SQL programmers, application developers/designers and technical support professionals who are required to tune the performance of an Oracle application running under Oracle 9i, 10g or.

COURSE AGENDA

1

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
2

Design

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

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
4

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
5

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
6

SQL Performance Analyzer

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

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
8

Gathering Statistics

  • Analyzing Statistics
  • DBMS_STATS
  • Automatic Optimizer Statistics Collection
  • Histograms
  • How to Generate Histograms Statistics
    • Tuning the SGA (including new 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
9

SQL Plan Management

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

Advanced Tuning

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

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
12

Metrics, Alerts, and Baselines

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

Using Statspack

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

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

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 6,000 various courses on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting https://www.encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top
Notice
X