Oracle Database 19c: SQL Tuning Workshop

• Execution Plans: Understand how to interpret execution plans and access data efficiently;
• Tuning Techniques: Learn to apply various tuning techniques and use tools like SQL Tuning Advisor;
• Performance Optimization: Gain knowledge on optimizing SQL code for better performance and database workloads;
• Practical Skills: Acquire practical skills such as using bind variables, trace files, and understanding different types of indexes.

Architects and developers.

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

• Basic Knowledge: Familiarity with Oracle Database architecture and components;
• Experience: Understanding of SQL and experience with writing and tuning SQL queries;
• Background: Prior experience working with Oracle Database, preferably as a DBA or in a role that involves database support or administration.

Oracle University study materials.

Not intended.

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.