TruTek

Seminar Overview
This 3-day Masterclass is entirely about making Oracle SQL execution run faster, 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: 3 days
Audience: Senior DBAs, senior developers, database designers
Skill level: Intermediate to Advanced
Objectives

• Gain deep understanding of SQL plan execution flow
• Know what and how to change to get efficient execution plans
• Be able to manually control SQL execution plans and guarantee their stability
• Be able to systematically index tables for access performance
• 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

• This is not a beginners “How to write SQL” class (although we will talk about rewriting SQL for optimization purposes)
• This is not a database/instance tuning class

You will learn the following…

1) 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
• How to use a systematic approach for tuning SQL of any complexity

2) 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?

3) 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

4) 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 misestimates and bugs

Customer Feedback About Tanel:

Our recent exercise with Tanel Poder was an extremely positive one and one which I would recommend to anyone seeking help with a contention issue on their Oracle database.  I had seen Tanel speak at the Hotsos Conference in Dallas and knew that he would be extremely valuable in helping us identify what was causing an unpredictable latching scenario in our production environment. 

Even before he arrived he asked for and begin reviewing the data we had collected (AWR reports, statspack output, etc.) and begin devising a "plan of attack" for determining the cause of our issue.  Upon arrival he took our input very seriously,  but was quick to validate our assumptions with factual database analysis.  I also appreciated his concern for our security and the well-being of our production environments and was very careful to not run scripts which would cause contention themselves in production, or leave behind bothersome custom objects.  

Tanel was always courteous and friendly and was willing to repeat his explanations and translate his very technical knowledge into terms that the lesser-trained DBA could comprehend.  And the tools he brought along were bundled up nicely for us to download to our own environment and use for our troubleshooting.  He summarized his findings for us in a way that was presentable to upper management and gave a clear explanation of his approach, analysis, and final recommendations.

I appreciated Tanel's offer to be a resource in the future if we have further questions after using his scripts and tools and I fully intend to take him up on it!    

Not finding the course or date that you're looking for? Let us know!