Professional Training

Prerequisites

A working knowledge of SQL. A knowledge of SQL Explain Plans is helpful but not necessary.

Topics covered

Oracle databases up through and including Oracle21c
Oracle Architecture from a SQL Performance point-of-view
Understanding SQL Tuning Statement Topics
Reading Explain Plans/Understanding Explain Plans
Controlling both the Cost-based and Rule-based Optimizers
A close look at Indexes - how they work and how they are selected
-SQL Tuning via coding style
A review of how Oracle computes row cardinality
Important CBO Statistics review
Oracle Trace Facility - collecting SQL and interpreting using TKProf
Profiling PL/SQL, PL/SQL Coding Tips
Finding problem SQL

Lectures and topics are enhanced with live illustrations and hands-on exercises

Attendees Receive

Study guide with presentations and relevant white papers
USB drive full of tuning and problem discovery scripts
Opportunity to ask the tough Oracle questions
A hands-on opportunity to learn more about Oracle, SQL Developer, and TOAD

Day 1

Oracle RDBMS Architecture overview
Understanding/Reading/Interpreting Explain Plans
Understanding the Cost-based Optimizer
Understanding Parallel Explain Plans
Working with Hints
Index Review/Tips & Techniques

Day 2

A close look at sub-query coding techniques
SQL Coding Tips
Where Clause and controlling Cardinality
A close look at CBO statistics
Review the CBO Trace (10053 Trace)

Day 3

Hint Review/Tips & Techniques
Finding problem SQL
Tuning Tool Review: SQL Tracing/Tkprof
Profiling and tuning PL/SQL
PL/SQL Coding Tips

Course Details

The course starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course goes in-depth on understanding and controlling the explain plan (how Oracle retrieves data and in what order). The discussion includes considerable detail, with SQL examples, on how the optimizers (both rule-based and cost-based but mostly cost-based) make their decisions.

Students will work with a variety of SQL statements, reviewing explain plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the explain plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler.

This course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used/not being used.

About Dan Hotka

Dan Hotka is a Training Specialist and an Oracle ACE Director Alumni (a recognized outside expert/evangelist on Oracle Products) who has over 40 years in the computer industry, over 34 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 14 Oracle books and well over 200 published articles. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world.
Dan is available for training in Ireland exclusively through Professional Training.