Objectives

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.

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