Oracle Database 11g: SQL Tuning

Duration

3 days

Overview

This course will equip database administrators and application developers to build efficient SQL statements and to tune database applications. When this effort is complemented by database server and PL/SQL application tuning, then a highly efficient application execution environment is created. One will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how one can influence the behavior of the database to achieve performance gains.

Course Objectives

  • Consider the unique and differing tuning issues found in online database applications, enterprise resource and data warehouse environments and the important metrics of SQL statement performance.

  • Learn about the internal mechanisms use for SQL statement execution within a database instance and how these can affect performance for good or bad, including the Optimizer facilities known as the Transformation Engine, Estimator and Plan Generator.

  • Use a variety of techniques to examine the details of SQL statement execution, spotting trouble areas and bottlenecks which require tuning.

  • Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.

  • Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics collection procedures using the DBMS_STATS() package, system statistics, histograms, expression statistics and MultiColumn statistics.

  • Influence the behavior of the Optimizer by setting database parameters and other SQL tuning techniques.

  • Utilize the database advisory framework and the SQL Tuning and SQL Access advisors.

  • Use plan management to achieve plan stability which is adaptive and even dynamic.

  • Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.

  • Employ SQL hints embedded into the statement text to resolve unique tuning challenges.

  • Learn to identify poorly performing SQL statements using real-time SQL monitoring and application tracing techniques such as DBMS_MONITOR(), trcsess and tkprof.

Prerequisites

Participants should have knowledge of SQL, or equivalent experience.

Oracle Database 12c: SQL I - Introduction and Oracle Database 12c: SQL II – Intermediate are recommended prerequisites for this course.

Course Content

  • Tuning & The Oracle Database Advisory Framework

  • Viewing & Monitoring The Execution Plan

  • Understanding The Optimizer

  • Execution Plan Methods & Operations

  • Managing Optimizer Statistics

  • Enhanced Optimizer Statistics

  • Histograms & Extended Statistics

  • Application Tracing

  • ADDM & The SQL Tuning Advisor

  • The SQL Access Advisor

  • Plan Management

  • Managing Cursor Sharing

  • Optimizer Hints

 
 

Next training dates

  • 6 to 8 March 2019

Training cost

2.250 EUR (VAT excluded) per person

Practical details

3 days
Classroom
From 9 AM to 5 PM

Language

English

Location

De Villermontstraat 9
2550 Kontich
Belgium

This training in-company?

Upon your request we can organize this training for you.