Two day seminar "Optimizing for Performance"
By Joze Senegacnik
| Seminar: |
Optimizing for Performance |
| Trainer: |
Joze Senegacnik |
| Time: |
27.aug – 28.aug |
| Place: |
Baltijas Datoru Akadēmija, Elizabetes street 65, Riga |
| Cost for one person: |
512 Ls + VAT |
Description:
This seminar is prepared for developers and DBAs in order to help them to understand the optimization process and introduce them in performance problem troubleshooting. During the course we discuss in details the basic database mechanisms. This step is necessary to understand the possible bottlenecks in SQL statement execution and become familiar with the remedies for performance problems. In subsequent lessons we study the SQL statement execution, the influence of binding variables on SQL statement execution and their side effects. Then we move to the cost based optimization details. In second day we start with the wait events and response time analysis. The index internals are discussed in separate lesson. In this session we try to find the answers why indexes are not used. We also discuss available database features which can be used for access path optimization. In the last session we discuss the optimizer hints and SQL Profiles.
Prerequisites:
The attendees should have at least 1 year of experience with application development or database administration and should be familiar with the database architecture.
Target Audience:
Developers, DBAs
Key words:
Cost based optimization, optimization, performance, tuning, response time analysis, execution plan, statistics gathering, CBO, configuration, query transformation, histograms, optimizer hints, automatic tuning optimizer, SQL Profiles
Objectives:
- To become familiar with possible optimization approaches.
- To get knowledge of database architecture and SQL statement processing.
- To study the cost based optimizer basics and how object statistics and histograms influence execution plan preparation.
- To become familiar with response time analysis and most common wait events and possible actions to reduce them.
- To understand index internals and indexing strategies; to optimize access paths to data
- To learn how to overcome execution plan instability problems and automatic tuning optimizer capabilities.
Day 1:
| |
Title |
Description |
| 1 |
What is optimization? |
This lesson discusses possible approaches to the optimization, how to properly diagnose the bottlenecks and root causes of performance problems; it points out how an optimization project should be defined, organized and monitored and what are the critical success factors for an optimization project. |
| 2 |
Database Architecture |
In order to be able to diagnose performance bottleneck we need to be familiar with the database architecture. In this chapter we walk through the major components of the Oracle database and study the mechanisms which have strong impact on the performance like SGA, PGA, Undo/redo, memory management and usage. |
| 3 |
SQL Statement Processing |
Each SQL statement executed in the kernel must be first parsed. There are many potential problems which may occur during the parse phase. One of the most important steps of the parse phase is the preparation of the execution plan. Here we briefly discuss the cost based optimization, which is later discussed in more detail. We discuss also the problems related to binding the variables and reasons why the SQL statements can’t be shared.
In the second part of this lesson we study the interpretation of the execution plan and the possibilities for parallel execution.
|
| 4 |
Cost Based Optimization |
The key prerequisite for optimal SQL statement execution is optimal execution plan which is the result of correctly gathered statistics and proper setup of the CBO. There are many important details how one can tell more “truth” to the CBO in order to enable it to prepare better execution plans. In this lesson we walk through the cost based optimization in more details and at the same time we study the influence of gathered object statistics, system statistics and histograms. At the end of the lesson we shortly discuss also the capabilities of the extensible optimizer which are used to define the selectivity and cost of execution of PL/SQL functions and packages. |
Day 2:
| |
Title |
Description |
| 5 |
Wait Events |
The response time analysis is based on the analysis of the service and wait time. We use the Queuing Theory as the base for understanding the response time analysis. The Oracle database kernel is instrumented to report time used in waiting for different kind of resources. These wait events can be reported to trace files and used for the most detailed response time analysis. In this lesson we discuss the most common wait events and possibilities for their elimination. |
| 6 |
Indexes |
This lesson discusses the indexes and the problems related to their usage. The most common question of the developers regarding indexes is: “Why my index is not used?”. In this lesson we briefly study the index internals, possible index access paths, the cost of the index access, when the index should be coalesced or rebuilt, index statistics and its influence on the execution plan. In the second part of the lesson we discuss the causes why an index is not used. |
| 7 |
Access Path Optimization |
Optimization of physical layer is as much important as any another area. In this lesson we discuss the impact of physical organization and possible optimization of access paths. Usually developers use only heap organized tables and b*tree indexes but there are many other options available in Oracle database which are there to be used in order to establish the optimal physical layout. We will look at index and hash clusters, single table hash clusters, materialized views, small tables organization and many more. |
| 8 |
Optimizer Hints,
SQL Profiles and
Execution Plan Stability |
The optimizer hints are really not hints but rather they are directives. When applied, a hint directs the optimization of the execution plan and disables possible optimization options. Therefore the optimizer hints are the last resort in the SQL |
Joze Senegacnik is an internationally recognized speaker and a member of the highly respected Oak Table Network. He is regular speaker in all Slovenian Oracle Users Group Conferences (SIOUG), Independent Oracle Users Group (IOUG) conferences, Hotsos Symposium and United Kingdom Oracle Users Group and Oracle Open World.
Interesting video
If you have any questions or you want to register, please contact Mara Ruce-Veselova, e-mail:
mara@bda.lv, phone. 26163153