Oracle SQL performance (EN/NL/FR)

This 2-day ABIS course covers SQL query tuning and optimisation. Useful for anyone tasked with (and interested in) query optimisation. Interactive session, live classroom training. Prior knowledge of SQL, and basic knowledge of Oracle databases is recommended.

The cause of many database performance problems can still be traced back to the coding of defective, inefficient SQL statements. Writing SQL statements seems simple; the rules for doing so are generally known. But it is not possible to write efficient SQL without knowledge of the operation of the Oracle optimizer. Hence, we will focus on the various tasks of the optimizer in this course; and we will investigate where and how the application developer can influence this optimizer. A number of techniques are discussed: use of indexes, hints, importance of statistics, ... The tools we can use to evaluate the optimization process and our SQL statements in terms of performance evaluation are also discussed: Explain, SQL Trace, autotrace, ...

The purpose of this course is:

• to explain the 'parsing' of Oracle SQL statements;
• to focus on specific parts of that parsing - optimization, statistics, ...
• to explain the measures and actions that are available to influence optimization - and parsing;
• to review the various tools that are traditionally available to evaluate the efficiency of the coded SQL statements - QEP access path generation;
• to analyze QEP access paths, in order to indicate where and possibly why certain suboptimal execution plans are generated.

Intended for

Analist-programmers, technical designers, DBA's, functional analysts. Everybody interested in how Oracle SQL statements are analyzed and optimized.

Backgroud

A basic knowledge of SQL (see SQL and relational databases fundamentals) is required; insights into the Oracle database structure is useful (see Oracle fundamentals course).

You can test for yourself to see if you have enough background by filling out the on-line self-test 'SQL and relational databases fundamentals'.

Main topics

• Introduction to performance tuning - available tools and techniques (explain, SQLTRACE, autotrace, ...)
• 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

Live instructor-led training, with plenty of opportunities for hands-on exercises and discussion

Delivered as a live, interactive training: available in-person or online, or in a hybrid format. 
Training can be implemented in English, Dutch, or French.
If interested in organising this training internally, get in touch with us; we can even customise it to meet your specific needs.

Certificate

At the end of the course session, the participant receives a 'Certificate of Completion'.

Duration
2 days.