Oracle SQL Tuning Training Logo

Oracle SQL Tuning Training

Live Online & Classroom Enterprise Training

Are you looking to upgrade your skills on RDBM performance tuning? Specifically in Oracle database server? Then this course is what you should look at. Our training course covers aspects related to SQL tuning, optimizations, analyzing execution plans, optimizer statistics, advisor and performance management.

Looking for a private batch ?

REQUEST A CALLBACK

Need help finding the right training?

Your Message

  • Enterprise Reporting

  • Lifetime Access

  • CloudLabs

  • 24x7 Support

  • Real-time code analysis and feedback

What is Oracle SQL Tuning Training about?

Oracle SQL Tuning training covers the need for SQL Tuning internals on Query Optimizer and optimizing performance by modifying a SQL statement to perform at its best - for example using different possible methods of accessing data including different join methods.

What are the objectives of Oracle SQL Tuning Training ?

  • Understand the need for SQL Tuning
  • Understand how the Query Optimizer makes decisions about how to access data
  • Define how optimizer statistics affect the performance of SQL
  • List the possible methods of accessing data, including different join methods
  • Modify a SQL statement to perform at its best

What are the prerequisites for Oracle SQL Tuning Training?

Working knowledge of SQL on Oracle.

Available Training Modes

Live Online Training

18 Hours

Classroom Training

3 Days

Course Outline Expand All

Expand All

  • Tuning Challenges
  • Performance Metrics
  • Management and Advisory Framework
  • ADDM and AWR
  • SQL Tuning Privileges
  • Introduction to Oracle Database Server Architecture
  • Introduction to Oracle Database Memory Structures
  • Database Storage Architecture, Logical and Physical Database Structures
  • Segments, Extents, and Blocks & SYSTEM and SYSAUX Tablespaces
  • Reason for Inefficient SQL Performance
  • Performance Monitoring Solutions
  • CPU and Wait Time Tuning Dimensions
  • Scalability with Application Design, Implementation, and Configuration
  • Common Mistakes on Customer systems & Proactive Tuning Methodology
  • Simplicity in Application Design
  • Structured Query Language
  • SQL Statement Parsing: Overview
  • Why Do You Need an Optimizer?
  • Optimization During Hard Parse Operation
  • Transformer & Estimator
  • Cost-Based Optimizer
  • Plan Generator
  • Controlling the Behavior of the Optimizer, Optimizer Features and Oracle Database Releases
  • Optimization Methods
  • Optimization Goals
  • Optimizer related parameters
  • Optimizer Components
  • Execution Plan Operations
  • What Is an Execution Plan? Where To Find Execution Plans and Viewing Execution Plans
  • Plan Table & AUTOTRACE
  • Using the V$SQL_PLAN View
  • Interpreting an Execution Plan
  • Reading More Complex Execution Plans and Reviewing the Execution Plan
  • Looking Beyond Execution Plans
  • Collecting Performance Statistics
  • Real-Time SQL Monitoring
  • Table Access Methods
  • Join Methods
  • Index Operations
  • Data Operations
  • End-to-End Application Tracing Challenge
  • Location for Diagnostic Traces
  • What is a Service? Use Services with Client Applications & Tracing Services
  • Session Level Tracing
  • The trcsess Utility and SQL Trace File Contents
  • Invoking the tkprof Utility and Output of the tkprof Command
  • Row Source Operations, Main Structures and Access Paths
  • Full Table Scan
  • Indexes: Overview and B*-tree Indexes and Nones
  • Index-Organized Tables
  • Bitmap Indexes, Bitmap Operations and Bitmap Join Index
  • Composite Indexes and Invisible Index
  • Guidelines for Managing Indexes and Investigating Index Usage
  • Nested Loops Join
  • Nested Loops Join: 11g Implementation
  • Sort Merge join
  • Hash Join and Cartesian Join
  • Equijoins and Nonequijoins
  • Outer Joins
  • Semijoins
  • Antijoins
  • When Are Clusters Useful?
  • Sorting Operators and Buffer Sort Operator
  • Filter operations and Concatenation Operations
  • UNION [ALL], INTERSECT, MINUS
  • Result Cache Operator
  • Types of Optimizer Statistics
  • Table, Index and Column Statistics
  • Index Clustering Factor
  • Multicolumn Statistics and Expression Statistics
  • Gathering System Statistics and Statistic Preferences
  • Manual Statistics Gathering
  • Locking Statistics, Export/Import Statistics and Set Statistics
  • More About Optimizer Statistics
  • Automatic Maintenance Tasks
  • Using Historical Statistics
  • Dynamic Sampling
  • About Optimizer System Statistics
  • Manage System Statistics
  • Pending and Published Statistics
  • Cursor Sharing and Different Literal Values
  • Cursor Sharing and Bind Variables
  • Bind Variable Peeking
  • Cursor Sharing Enhancements
  • The CURSOR_SHARING Parameter
  • Forcing Cursor Sharing
  • Adaptive Cursor Sharing
  • Interacting with Adaptive Cursor Sharing
  • Why Are Histograms Needed?
  • Histograms Internal Structure
  • Manually Managing Histograms
  • Expression Statistics
  • Multicolumn Statistics
  • Tuning SQL Statements Automatically
  • Application Tuning Challenges
  • SQL Tuning Advisor: Overview
  • Stale or Missing Object Statistics and SQL Statement Profiling
  • Plan Tuning Flow and SQL Profile Creation
  • SQL Tuning Loop, Access Path Analysis and SQL Structure Analysis
  • Database Control and SQL Tuning Advisor
  • Implementing Recommendations
  • Using The SQL Access Advisor
  • SQL Access Advisor Templates
  • Performing A Quick Tune Task
  • Index Database Parameters
  • About Cursor Sharing
  • Bind Variables and Cursor Sharing
  • Finding Candidates For Rewrite
  • Automatic SQL Tuning
  • Automatic Tuning Process
  • Automatic SQL Tuning example
  • Maintaining SQL Performance and SQL Plan Management
  • SQL Plan Baseline
  • Using SQL Performance Analyzer and SQL Plan Baseline

Who is the instructor for this training?

The trainer for this Oracle SQL Tuning Training has extensive experience in this domain, including years of experience training & mentoring professionals.

Reviews