Duration
3 days
OVERVIEW
In the Oracle Database: SQL Tuning for Developers course, you learn about Oracle SQL tuning and how to apply tuning techniques to your SQL code. Learn the different ways in which data can be accessed efficiently.
Learn To:
- Use Oracle tools to identify inefficient SQL.
- statements.
- Use Automatic SQL Tuning.
- Use Real Time SQL monitoring.
- Write more efficient SQL statements.
- Monitor and trace high load SQL statements.
- Manage optimizer statistics on database objects.
- Understand the optimizer process steps and operators.
- Interpret execution plans.
- Perform application tracing.
Benefits to You:
- Benefit from gaining a deeper understanding of Oracle.
- SQL statement tuning and how write well-tuned SQL
- statements appropriate for the Oracle database in this
- application development-centric course.
You will learn.
- how to decipher, decide and then apply tuning to your
- SQL code. Various tuning techniques are demonstrated.
AUDIENCE
This course is specially design for Application Developers, Data Warehouse Developer, Developer, PL/SQL Developer and Support Engineer
PREREQUISITES
Required Prerequisites:
- Familiarity with database architecture
- Knowledge of SQL and PL/SQL
- Oracle Database: Introduction to SQL
Recommended Related Training Courses:
- Using Java - for PL/SQL and Database Developers This Java for PL/SQL and Developers training teaches you to access Oracle Database using JDBC, UCP, Java stored procedures and SQLJ technologies. Learn to create, load, resolve and publish Java classes in the Database and more.
- Oracle Database: SQL Workshop II This Oracle Database: SQL Workshop II helps you manage the database objects, data dictionary views and multiolumn sub queries. Learn to write more efficient queries using the functions supporting the timezone and regular expressions.
- Oracle Database: SQL Tuning for Developers In the Oracle Database: SQL Tuning for Developers course, you learn about Oracle SQL tuning and how to apply tuning techniques to your SQL code. Learn the different ways in which data can be accessed efficiently.
- Oracle Database: Develop PL/SQL Program Units This Oracle Database: Develop PL/SQL Program Units training helps you enhance your skills by developing stored procedures, functions, packages, and database triggers. Learn to manage PL/SQL program units and use Oracle-supplied packages.
- Oracle Database: PL/SQL Fundamentals Oracle Database: PL/SQL Fundamentals introduces you to the PL/SQL programming language. Learn about stored procedures, functions and creating blocks of application code that can be shared by multiple forms and data management applications.
METHODOLOGY
This program will be conducted with interactive lectures, PowerPoint presentations, discussions and practical exercises.
COURSE OBJECTIVES
Upon completion of this program, participants should be able to:
- Modify a SQL statement to perform at its best.
- Identify poorly performing SQL.
- Trace an application through its different levels of the application architecture.
- Understand how the Query Optimizer makes decisions about how to access data.
- Define how optimizer statistics affect the performance of SQL.
- List the possible methods of accessing data, including different join methods.
Day 1
9:00am-10:30am
Module 1: Course Introduction
- Course Introduction
- Workshop 1: Enhancing the Performance of a SQL Query Statement
10:30am-10:45am Morning break
10:45am-12:30pm
Module 2: Introduction to SQL Tuning
- Introduction to SQL Tuning
- Quick Solution Strategy
- Workshop 2: Reviewing the Execution Steps of the SQL Statement
- Practice 2-1: Using SQL Developer
12:30pm-1:30pm Lunch
1:30pm-3:30pm
Module 3: Using Application Tracing Tools
- Using Application Tracing Tools
- trcsess Utility
- Workshop 3: Learn to Tune Sort Operation Using an Index in the ORDER BY Clauses
- Practice 3-1: Tracing Applications (Part 01)
- Practice 3-1: Tracing Applications (Part 02)
3:30pm-3:45pm Afternoon break
3:45pm-5:00pm
Module 4: Optimizer Fundamentals
- Optimizer Fundamentals
- Query Estimator: Selectivity and Cardinality
- Plan Generator
- Workshop 4: Identifying and Tuning a Poorly Written SQL Statement
- Practice 4-1: Understanding Optimizer Decisions (Optional)
Day 2
9:00am-10:30am
Module 5: Generating and Displaying Execution Plans
- Generating and Displaying Execution Plans
- AUTOTRACE
- Automatic Workload Repository
- Workshop 5: Effects of Changing the Column Order in a Composite Index
- Practice 5-1: Extracting an Execution Plan by Using SQL Developer
- Practice 5-2: Extracting Execution Plans
10:30am-10:45am Morning break
10:45am-12:30pm
Module 6: Interpreting Execution Plans and Enhancements
- Interpreting Execution Plans and Enhancements
- Workshop-6: Using Information in the 10053 File to Tune a SQL Statement
- Practice 6-1: Using Dynamic Plans
12:30pm-1:30pm Lunch
1:30pm-3:30pm
Module 7: Optimizer: Table and Index Access Paths
- Optimizer: Table and Index Access Paths
- Indexes: Overview
- Bitmap Indexes
- Common Observations
- Workshop 7: Understanding the Optimizer’s Decision
- Practice 7-1: Using Different Access Paths (Part 01)
- Practice 7-1: Using Different Access Paths (Part 02)
- Practice 7-1: Using Different Access Paths (Part 03)
- Practice 7-1: Using Different Access Paths (Part 04)
3:30pm-3:45pm Afternoon break
3:45pm-5:00pm
Module 8: Optimizer: Join Operators
- Optimizer: Join Operators
- Workshop 8: Tuning Strategy
- Practice 8: Using Join Paths
Day 3
9:00am-10:30am
Module 9: Other Optimizer Operators
- Other Optimizer Operators
- Workshop 9: Using SQL Plan Baseline to Manage a Better Execution Plan
- Practice 9-1: Using the Result Cache
- Practice 9-2: Using Other Access Paths (Optional)
10:30am-10:45am Morning break
10:45am-12:30pm
Module 10: Introduction to Optimizer Statistics Concepts
- Introduction to Optimizer Statistics Concepts
- Column Statistics: Histograms
- Session-Specific Statistics for Global Temporary Tables
- Practice 10-1: Index Clustering Factor
- Practice 10-2: Creating Expression Statistics
- Practice 10-3: Enabling Automatic Statistics Gathering Optional (Part 01)
- Practice 10-3: Enabling Automatic Statistics Gathering Optional (Part 02)
- Practice 10-4: Using System Statistics (Optional)
12:30pm-1:30pm Lunch
1:30pm-3:30pm
Module 11: Using Bind Variables
- Using Bind Variables
- Cursor Sharing Enhancements
- Practice 11-1: Using Adaptive Cursor Sharing
- Practice 11-2: Using CURSOR_SHARING (Optional)
3:30pm-3:45pm Afternoon break
3:45pm-5:00pm
Module 12: SQL Plan Management
- SQL Plan Management
- Configuring SQL Plan Management
- Possible SQL Plan Manageability Scenarios
- Practice 12-1: Using SQL Plan Management SPM (Part 01)
- Practice 12-1: Using SQL Plan Management SPM (Part 02)
Module 13: Workshops
- Workshops
Programme scope, delivery arrangements and suitability are confirmed during enquiry.