Oracle SQL performance

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.