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.
Analyst programmers, technical designers, DBAs, functional analysts.
A basic knowledge of SQL and of the Oracle database structure is required (see Oracle fundamentals course).
- 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
Classroom instruction with exercises.
Kris Van Thillo.
Enrollees for this training also took the following courses:
|SESSION INFO AND ENROLMENT|