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