Сhat now

Oracle Database 19c: SQL Tuning Workshop

Information
Course code
19cDB-SQL-TUN-WS
Duration
3 Days, 24 Acad. Hours
Delivery formats
Classroom
OnLine
On-Site
Virtual

Course overview

Oracle SQL Tuning for Developers Workshop course helps you learn how plans are tuned execution of Oracle SQL statements. You will learn how to write well-tuned SQL statements out of the box that are perfectly suited to Oracle DBMS.

Course Objectives

Upon completion of the course, students will be able to:

  • Interpret query execution plans and different ways of accessing data.
  • Decrypt, make optimization decisions, then apply settings in SQL code.
  • Use a variety of customization methods. Take advantage of bind variables, trace files, and use different types of indexes.
  • Use different access data paths for better code optimization.

Prerequisites:

  • Oracle Database 19c: SQL Workshop course or equivalent knowledge

Course Topics

1. Course Introduction

  • Course Introduction
  • Workshop 1: Enhancing the Performance of a SQL Query Statement

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

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)

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)

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

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

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)

8. Optimizer: Join Operators

  • Optimizer: Join Operators
  • Workshop 8: Tuning Strategy
  • Practice 8: Using Join Paths

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. 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)

11. Using Bind Variables

  • Using Bind Variables
  • Cursor Sharing Enhancements
  • Practice 11-1: Using Adaptive Cursor Sharing
  • Practice 11-2: Using CURSOR_SHARING (Optional)

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)

13. Workshops

  • Workshops

Oracle Database 19c: SQL Tuning Workshop

Information
Course code
19cDB-SQL-TUN-WS
Duration
3 Days, 24 Acad. Hours
Delivery formats
Classroom
OnLine
On-Site
Virtual

Course overview

Oracle SQL Tuning for Developers Workshop course helps you learn how plans are tuned execution of Oracle SQL statements. You will learn how to write well-tuned SQL statements out of the box that are perfectly suited to Oracle DBMS.

Course Objectives

Upon completion of the course, students will be able to:

  • Interpret query execution plans and different ways of accessing data.
  • Decrypt, make optimization decisions, then apply settings in SQL code.
  • Use a variety of customization methods. Take advantage of bind variables, trace files, and use different types of indexes.
  • Use different access data paths for better code optimization.

Prerequisites:

  • Oracle Database 19c: SQL Workshop course or equivalent knowledge

Course Topics

1. Course Introduction

  • Course Introduction
  • Workshop 1: Enhancing the Performance of a SQL Query Statement

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

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)

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)

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

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

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)

8. Optimizer: Join Operators

  • Optimizer: Join Operators
  • Workshop 8: Tuning Strategy
  • Practice 8: Using Join Paths

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. 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)

11. Using Bind Variables

  • Using Bind Variables
  • Cursor Sharing Enhancements
  • Practice 11-1: Using Adaptive Cursor Sharing
  • Practice 11-2: Using CURSOR_SHARING (Optional)

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)

13. Workshops

  • Workshops