Oracle SQL Tuning Certification Training

Live Online & Classroom Certification 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.

(4.7) 174 Learners
Instructed by SPRINGPEOPLE
INDIA

No Public/Open-house class on the topic scheduled at the moment!

Course Description

Overview

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.

Objective

  • 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

Prerequisites

Working knowledge of SQL on Oracle.

Course Curriculum

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 Nulls
  • 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

Certification

SpringPeople works with top industry experts to identify the leading certification bodies on different technologies - which are well respected in the industry and globally accepted as clear evidence of a professional’s “proven” expertise in the technology. As such, these certification are a high value-add to the CVs and can give a massive boost to professionals in their career/professional growth.

Our certification courses are fully aligned to these high-profile certification exams; at the end of the course, participants will have detailed knowledge, be eligible and be fully ready take up these certification exams and pass with flying colours.

 

Resources

SpringPeople Corporate Learning Center

Job Trends

About the Instructor

Founded in 2009, SpringPeople is a global premier eLearning marketplace for Online Live, Instructor-led classes in the region. It is a certified training delivery partner of leading technology creators, namely Pivotal, Elastic, Lightbend, EMC, VMware, MuleSoft, RSA, and... Read More


Course Rating and Reviews

4.7

Average Rating
5 Stars
28
4 Stars
12
3 Stars
1
2 Stars
0
1 Star
0

SPRINGPEOPLE SpringPeople Trainer

Debashish Maity

Program Architect
Mindtree
Course:
Instructor:
Course Material:
Class Experience:
Provide the latest exercise, classroom guides as current hands outs are old ones and not matching / consistent with class session.

SPRINGPEOPLE SpringPeople Trainer

Ritabrata Chakraborty

Application Development Team Lead
Accenture
Course:
Instructor:
Course Material:
Class Experience:
Kindly include some part of Anypoint-MQ this course.

SPRINGPEOPLE SpringPeople Trainer

Bidyut

Course:
Instructor:
Course Material:
Class Experience:
No comments as of now

This class is intended for participants with some prior exposure to the technology and are now looking to build up their expertise on the topic.

On successful completion of the course, participants will be eligible to sit of the related certification exam (see course overview). All participants receive a course completion certificate, demonstrating their expertise on the subject.

Total duration of the online, live instructor led sessions. Sessions are typically delivered as short lectures (2-hrs weekdays/3-hrs weekends) and detailed hands-on guidance.

Expected offline lab work hours that participants will need to complete and submit to the trainer, during and after the instructor-led online sessions.

  1. We are happy to refund full fee paid - no questions asked - should you feel that the training is not up to your expectations.
  2. Our dedicated team of expert training enablement advisors are available on email, phone and chat to assist you with your queries.
  3. All courseware, including session recordings, will always be available to access to you for future reference and rework.

Contact Us

+91-80-6567-9700 (BLR)

training@springpeople.com

Request Call Back

Related Courses

Recently Viewed