Oracle Database 19c: SQL Tuning Workshop

Course duration, academic hours: 24
Price (excl. VAT) 1800,00 
Price (with VAT): 2178,00 
Lecturer: Gints Plivna
Gints Plivna
Gints Plivna works as a data architect at Swedbank. The lecturer has a deep interest in databases, professional experience with relational database management systems (RDBMS) Oracle, Teradata, SQL Server, MySQL, as well as NoSQL DBMS - MongoDB. Instructor of Oracle SQL and PL/SQL certified courses since 2007.

Jānis Ozoliņš Signis Vāvere
Apply for the course:

Apply or ask a question!

 

If you want to apply for the course or ask a question about it, feel free to use this form.

Please enter your first and last names
Please enter company name
Please enter your phone number
Please enter your email

Thank you! We will contact you.

Course target
Audience
  • Architect
  • Developer
At Course Completion

Upon completion of this course, the student should be able 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.
  • Use different access paths for better optimization.
Prerequisites
Training materials

Oracle University study materials.

Certification Exam
Course outline

1. Course Introduction

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

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

  • 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

  • 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

  • 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

  • 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

  • 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

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

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

  • 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

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

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

If you want to get more information about this course, contact us by phone +371 67505091 or send us an e-mail at mrn@bda.lv.