Oracle SQL Tuning for Developers Workshop

Course Code: 
OR73549
Number of Days: 
3
$2,100.00

This Oracle SQL Tuning for Developers Workshop will help you explore Oracle SQL statement tuning. Learn how to write well-tuned SQL statements appropriate for the Oracle database.Students will 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; and Use different access paths for better optimization.This course will 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. 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.

Required Skills: 

Oracle Database: SQL Fundamentals IOracle Database: SQL Fundamentals IIOracle Database: Introduction to SQL

Target Audience: 

PL/SQL DeveloperDeveloperData Warehouse DeveloperDatabase Designers

Objectives: 

Choose an appropriate SQL tuning approachGather and interpret session statistics using the SQL trace facilityIdentify the SQL statements that perform poorlyUse tuning techniques to tune inefficient SQL statementsInterpret Execution PlansDescribe the Oracle optimizer fundamentalsManage 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 statementUsing SQL Trace Facility and TKPROF - Explore a trace file to understand the optimizer’s decisionsUnderstand Basic Tuning Techniques - Rewrite queries for better performance - Rewrite applications for better performance - Utilize SQL Tuning Advisor using SQL Developer 3.0Optomizer Fundamentals - Explore a trace file to understand the optimizer’s decisionsUnderstanding Serial Execution Plans - Use different techniques to extract execution plans - Use SQL monitoringOptimizer: Table and Index Access Paths - Learn about using different access paths for better optimizationOptimizer: Join Operations - Use different access paths for better optimization - Examine and use the result cacheOther Optimizer Methods - Use different access paths for better optimization - Examine and use the result cacheOptimizer Statistics - Analyze and use system statistics - Use automatic statistics gatheringUsing Bind Variables - Use adaptive cursor sharing and bind peeking - Use the CURSOR_SHARING initialization parameterSQL Plan Management - Use SQL Plan Management