Understanding The Oracle Cost Based Optimizer (ENG)

Why attend this workshop?

Quite often database performance problems encountered with applications comes from some general misconceptions how the database handles the SQL thrown at it. This workshop should give some insights into how the database turns the SQL into an set of executable instructions and what typically can go wrong with this, and why, leading to some important points how these problems can be avoided.

Objectives (Basics course)

• Understand the basic principles how the Oracle optimizer calculates the cost of queries and the main driving factors of the cost calculation
• Get an idea why some of these calculations can go wrong even for cases that seem to be rather trivial for the human eye
• Learn what you can do to improve some of these calculations and what to do for those cases where you can't easily improve
• Learn how to properly read the execution plans generated by the optimizer

Objectives (Advanced)

• Improve indexing and get an idea what the new In Memory Option and Exadata Smart Scans are about
• Get a better understanding of how the optimizer attempts to re-write queries
• Understand what difference the partitioning option can make and the challenge for maintaining statistics on partitions.
• How to manually set statistics and the difference between "good" and "bad" hinting
• Learn to know the different flavors of plan stability in Oracle
• Learn how to read Parallel Execution plans

Required skills


Basic understanding of Oracle Architecture and how Oracle works. SQL

Target Audience

Developers and DBAs

Content

Day 1 (Basics)

- The basic principles and main driving factors of the Oracle optimizer cost calculation

- Where those calculations can go wrong and why

- How to improve the calculations to get them closer to reality

- What to do if they can't be improved that easily

- How to read execution plans generated by the optimizer

- How to generate a runtime profile of a query - comparing estimates to actuals and see where the time is spent

Optional Day 2 (Advanced)

- Indexing / In Memory and Exadata Smart Scans

- Query Rewrites - the Cost Based Query Transformation (CBQT) framework

- Partitioning and statistics on partitions

- Manually setting statistics and making good use of hints

- Plan Stability

- Parallel Execution plans