SQL and PL/SQL optimization

Course objectives

Understanding optimization rules and SQL queries in Oracle databases. The use of available tools in the optimization of SQL queries, procedures, functions.

Benefits

• Workshop participants will learn about:
  • concepts regarding Oracle databases,
  • operating principles of the optimizer and optimization rules,
  • using packages and embedded procedures
  • creating own functions and procedures in accordance with optimization principles,
  • creation and use of data stored in databases used in optimization,
  • the meaning of HINT
• Participants will acquire detailed knowledge allowing for:
  • creating optimal SQL queries,
  • the importance and use of statistics, histograms,
  • automation of operations,
  • event handling and time-consuming query handling.

Training scope

• Basics of statistics
• Statistics and histograms – available information
• Use of statistics by the optimizer – rules for switching off, optimizer operation control
• Options available at the time of creating the tables – rules of use, benefits
• The choice of index by the optimizer – forced actions, no reaction
• Index types: usefulness, when to create, types of indexes, advantages and disadvantages
• Generating an execution plan (various ways) and reading a plan table. Viewing various query plans / tuning history
• Selected hints (HINT)
• Code optimization

Audience

All individuals involved in the process of developing software for Oracle databases, members of project teams.

Programmers who have knowledge about creating SQL queries and procedures and want to deepen their expertise towards creating SQL tests.