Oracle SQL Execution

Seminar Overview

This seminar is entirely about making Oracle SQL execution run faster and more efficiently, understanding the root causes of SQL performance problems and Cost Based Optimizer misbehavior.

You will learn the full range of techniques for SQL tuning starting from optimizer stats placement strategy all the way to advanced topics such as comprehensive SQL hinting and CBO’s estimated row count adjustments.

We start from the low level fundamentals, explaining how exactly Oracle SQL plan execution works, what are the main factors affecting query performance and how to systematically diagnose and fix SQL performance problems.

Some SQL performance problems cannot be cured by just an easy change to application code or optimizer stats, so a significant part of this seminar also concentrates on good database design and indexing practices.

Finally, this class is not about a limited list of “tips and tricks”, instead it will give you the knowledge and tools for systematic tuning and troubleshooting SQL of any complexity.

Seminar Duration and Details

• Seminar duration: 8 x 4 hours (sepatated into two parts, on 2 separate weeks, 4 half-days each week)
• Venue: Online, virtual seminar at your desk (no travel!)
• Audience: Senior DBAs, senior developers, database designers
• Skill level: Intermediate to Advanced
• Speaker: Tanel Poder
• Price:
• $1196 USD per attendee for 1 part (4 sessions)
• $2392 USD per attendee for 2 parts (8 sessions)

Objectives

1. Gain deep understanding of SQL plan execution flow
2. Know what and how to change to get efficient execution plans
3. Be able to manually control SQL execution plans and guarantee their stability
4. Be able to systematically index tables for access performance
5. Help CBO to find good plans, to keep the manual tuning to minimum

After this class you won’t need to memorize every single SQL performance or optimizer problem out there, instead you will be able to systematically work out the problem root causes yourself!

Non-objectives

1. This is not a beginners “How to write SQL” class (although we will talk about rewriting SQL for optimization purposes)
2. This is not a database/instance tuning class (Visit the seminar schedule page for other classes)

You will learn the following…

Part 1

(4 x 0.5 days on 1st week)

Understanding SQL Plan Execution

• Deep understanding of how exactly Oracle executes SQL statements, both SELECT and DML statements
• Understand the fundamental factors affecting query execution performance
• Monitoring SQL execution activity and where the response time is spent
• How to use a systematic approach for tuning SQL of any complexity

Controlling SQL Execution Plans

• How to make a SQL execution plan do exactly what you want
• How to achieve SQL performance and plan stability
• Understand why SQL execution plans unexpectedly change even when nothing else has apparently changed – and what to do about it
• Understand why can the SQL performance suddenly drop, when nothing, even the execution plan itself has not changed – and what to about it
• How to optimize SQL when you cannot change the code?

Part 2

(4 x 0.5 days on 2nd week)

Accessing Data Efficiently

• Systematic indexing – which index type to use, when and how
• How to decide which columns and in which order to index – without trial and error
• Understanding index-based data retrieval overhead
• Understanding index maintenance overhead
• Identifying best candidates for index-organized tables

Helping the Cost Based Optimizer to Find Good Plans

• Understand what exactly is the Optimizer Cost, which input data is used for cost calculations and where does this number matter
• How to configure Oracle, statistics and CBO to keep the need for manual SQL tuning to the minimum
• How to troubleshoot CBO Cost and row count misestimation and bugs

Seminar content technical details

Download the full details from this link.