Oracle SQL Tuning for Developers Workshop

Sono richieste le conoscenze di:

• Oracle Database: SQL Fundamentals I
• Oracle Database: SQL Fundamentals II
• Oracle Database: Introduction to SQL

Prerequisiti suggeriti :

• Oracle Database: PL/SQL Fundamentals

Il corso è rivolto a:

• Sviluppatore PL/SQL

In this application development-centric course, you'll explore Oracle SQL statement tuning. Learn how to write well-tuned SQL statements appropriate for the Oracle database.

Learn To:

• Interpret execution plans and the different ways in which data can be accessed.
• Decipher, decide and then apply tuning to SQL code.
• Use various tuning techniques.
• Take advantage of bind variables, trace files and use the different types of indexes.
• Use different access paths for better optimization.

Benefits to You

• Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.
• Understand Basic Tuning Techniques
• Expert instructors will also teach you how to rewrite queries for better performance. Furthermore, you'll learn how to utilize SQL Tuning Advisor using SQL Developer 3.0.

You will learn to:

• Choose an appropriate SQL tuning approach
• Gather and interpret session statistics using the SQL trace facility
• Identify the SQL statements that perform poorly
• Use tuning techniques to tune inefficient SQL statements
• Interpret Execution Plans
• Describe the Oracle optimizer fundamentals
• Manage SQL performance through changes

Introduction to SQL Tuning

Find a workaround to enhance performance

Analyze a poorly written SQL statement

Create a function based index

Redesign a simple table

Rewrite the SQL statement

Using SQL Trace Facility and TKPROF

Explore a trace file to understand the optimizer’s decisions

Understand Basic Tuning Techniques

Rewrite queries for better performance

Rewrite applications for better performance

Utilize SQL Tuning Advisor using SQL Developer 3.0

Optomizer Fundamentals

Explore a trace file to understand the optimizer’s decisions

Understanding Serial Execution Plans

Use different techniques to extract execution plans

Use SQL monitoring

Optimizer: Table and Index Access Paths

Learn about using different access paths for better optimization

Optimizer: Join Operations

Use different access paths for better optimization

Examine and use the result cache

Other Optimizer Methods

Use different access paths for better optimization

Examine and use the result cache

Optimizer Statistics

Analyze and use system statistics

Use automatic statistics gathering

Using Bind Variables

Use adaptive cursor sharing and bind peeking

Use the CURSOR_SHARING initialization parameter

SQL Plan Management

Use SQL Plan Management