Oracle SQL performance
It is extremely hard to write efficient SQL without having a thorough understanding of the Oracle optimizer. We therefore first discuss its characteristics, and determine where, and how, we can influence its behaviour. A number of techniques are discussed: creation and use of indexes, use of hints, the importance of statistics gathering. The tools available to evaluate the optimization process and the queries we write, are discussed as well: Explain, SQL Trace, autotracce.
Finally, the impact of the use of PL/SQL as an alternative to writing SQL statements is introduced, and its advantages discussed.
Participants have the opportunity to practise all the discussed topics during practical labs.
Schedule
| REQUEST CORPORATE TRAINING |
| Public training calendar | |||||
|---|---|---|---|---|---|
| date | dur. | lang. | location | price | |
| 19 Mar | 2 | web based | 1310 EUR (excl. VAT) | ||
| 19 Mar | 2 | Leuven | 1310 EUR (excl. VAT) | ||
| SESSION INFO AND ENROLMENT | |||||
Intended for
Analyst programmers, technical designers, DBAs, functional analysts.
Background
A basic knowledge of SQL and of the Oracle database structure is required (see Oracle fundamentals course).
Main topics
- Introduction to performance tuning - available tools and technieques (explain, SQLTRACE, auatotrace, ...)
- Identification of performance problems
- The SQL optimisation process
- Tuning table access (single value, ranges, multi-column lookup, ...)
- Table join methods (types, optimization, join order) - Subqueries (simple, correlated)
- Sorting, Grouping
- The use of hints in SQL statements
- Query tuning
- Performance improvement through the use of PL/SQL
- Advanced techniques for performance optimization
Training method
Classroom instruction with exercises.
Duration
2 days.
Course leader
Kris Van Thillo.
| SESSION INFO AND ENROLMENT |