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 IN-COMPANY TRAINING |
Public training calendar
No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested? Please let us know.
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 |