Course Details
Course Duration: 3 days/21 hours; Instructor-led/ remote online training
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 presentation, discussion and practical exercise.
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.
Outlines
Module 1: Course Introduction
- Course Introduction
- Workshop 1: Enhancing the Performance of a SQL Query Statement
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
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)
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)
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
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
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)
Module 8: Optimizer: Join Operators
- Optimizer: Join Operators
- Workshop 8: Tuning Strategy
- Practice 8: Using Join Paths
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)
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)
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)
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
