home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
C
All CoursesbalkjeGeneral Introduction to HW & SW Soft skills TracksOperating systems MVS - z/OS UNIX - Linux - AIX Mac OS X iPad and iPhone iOSDatabases and middleware Relational databases & SQL DB2 for z/OS DB2 for LUW Oracle SQL Server MySQL & MariaDB IMS CICS IBM MQ WebSphere Big data and analyticsApplication development Methods and techniques TOGAF PRINCE2 Agile development and Scrum Programming languages Internet development Object Oriented systems Java Development tools SAS XML SOA & web servicesSystems management ITIL SecuritybalkjePractical informationRegistration 
Oracle SQL performance

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
 

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).

Training method

Classroom instruction with exercises.

Course leader

Kris Van Thillo.

Duration

2 days.

Schedule

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 contact ABIS.

Global score

   
3.9/5 (based on 92 evaluations)

Reviews

     
ok, maar in ons geval meer als achtergrondinfo (, )
     
zeer interessant, misschien niet alles van toepassing in onze situatie (, )
     
Er zou een elektronische versie beschikbaar moeten zijn, met referentiemateriaal. (, )
     
goed, duidelijke cursus, voldoende ruimte om vragen te stellen (, )
     
Deze cursus heeft zeker toegevoegde waarde, maar is redelijk theoretisch. (, )
     
Te weinig praktijk of oefeningen (, )
     
good, have seen a lot (, )
     
Ter inleiding goed, echter ik was in de veronderstelling dat er meer SQL-tuning met opdrachten aan bod kwamen. (, )
     
Goed en grondig uitgelegd. Zeer interessant (, )
     
very good course (, )