Oracle SQL Tuning for Developers Workshop (D73549)

Introduction

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