_ Oracle SQL – Performance Tuning

software course category

This Oracle SQL - Performance Tuning course is developed in order for you to learn and practice about Oracle SQL tuning and how to apply tuning techniques to your SQL code. Learn the different ways in which data can be accessed efficiently.

Objectives

Modify an Oracle SQL statement to perform at its best
Identify poorly performing SQL
Trace an application through its different levels of the application architecture
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

Audience

Data Warehouse Developer
Application Developers
Developer
Support Engineer
PL/SQL Developer

Duration

3 days

Requirements

Familiarity with database architecture
Knowledge of Oracle SQL and PL/SQL
Oracle Database: Introduction to SQL Ed 1.1

Curriculum

Introduction to Oracle SQL Tuning
SQL Tuning Session
SQL Tuning Strategies
Development Environments: Overview
SQLTXPLAIN (SQLT) Diagnostic Tool
Using Application Tracing Tools
Using the SQL Trace Facility: Overview
Steps Needed Before Tracing
Available Tracing Tools: Overview
The trcsess Utility
Formatting SQL Trace Files: Overview
Understanding Basic Tuning Techniques
Developing Efficient Oracle SQL statement
Scripts Used in This Lesson
Table Design
Index Usage
Transformed Index
Data Type Mismatch
NULL usage
Tune the ORDER BY Clause
Optimizer Fundamentals
SQL Statement Representation
SQL Statement Processing
Why Do You Need an Optimizer?
Components of the Optimizer
Query Transformer
Cost-Based Optimizer
Adaptive Query Optimization
Optimizer Features and Oracle Database Releases
Generating and Displaying Execution Plans
Execution Plan
The EXPLAIN PLAN Command
Plan Table
AUTOTRACE
V$SQL_PLAN View
Automatic Workload Repository
SQL Monitoring
Interpreting Execution Plans and Enhancements
Interpreting a Serial Execution Plan
Adaptive Optimizations
Optimizer: Table and Index Access Paths
Row Source Operations
Main Structures and Access Paths
Full Table Scan
Indexes
Common Observations
Optimizer Join Operations
Join Methods
Join Types
Other Optimizer Operators
SQL operators
Other N-Array Operations
Result Cache operators
Introduction to Optimizer Statistics Concepts
Optimizer Statistics
Types of Optimizer Statistics
Gather and Manage Optimizer Statistics: Overview
Using Bind Variables
Cursor Sharing and Different Literal Values
Cursor Sharing and Bind Variables
SQL Plan Management
Maintaining SQL Performance
Oracle SQL Plan Management
Exercises

Resources

_ Classes

No classes available for this course...

Our Delivery

Online or On-site delivery

We created a personalized delivery strategy by offering blended learning​

Communities of practice

Find a bunch of people who are on the same page with you.

Engaged learning

Engaging platform with gamification for collaboration and friendly competition.

Interactive training

Interactive online trainings and live webinars available.

Learning efficiency

A special learning environment can boost learning efficiency.

Learning everywhere

Available anywhere and anytime, on your phone, computer or tablet.

Q&A

Can you organise virtual training sessions or only face to face trainings?​

Yes, you can choose an online or an offline training session. We use Knolyx, an e-learning platform, to make the process of online training and mobile learning delivery as efficient as a face-to-face training session. You can check here a complete list of features set.

Should we deliver online or
offline classes?

That’s up to you. We offer you an efficient learning environment and you can use it according to your needs: either online or offline. Nobody knows better than you and your team what you need, so we do not imposed some predefined criteria, we adapt to yours.

Can you customize courses to suit our particular requirements?​

Yes. We offer training consultancy and we establish the most appropriate courses according to the specific needs and business objectives of your company.