Oracle 19c SQL Performance Tuning Training Course

Course Contents - DAY 1

Course Introduction

• • Administration and Course Materials
• • Course Structure and Agenda
• • Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

• • Overview of Oracle Database Tuning
• • Application Developer Tuning Responsibilities
• • Oracle DBA Tuning Responsibilities
• • Oracle Tuning Process
• • Plan a Routine Monitoring Regime
• • Setting Suitable Goals
• • Syntax Considerations

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

• • Overview of SQL Statement Tuning
• • Tools to Assist in SQL Tuning
• • Use Explain Plan,Autotrace and SQL Trace to Examine the Execution of a SQL Statement
• • Interpreting a SQL Trace

Session 3: THE SQL OPTIMIZER

• • The SQL Optimizer
• • Statement Transformation
• • The Optimizer_Mode Initialization Parameter
• • Cost Based Optimizer
• • Managing Statistics with DBMS_STATS
• • Automatic Statistics Gathering
• • Dynamic Statistics
• • Adaptive Optimization
• • Transferring Statistics between Databases

Session 4: SORTS

• • How Oracle Processes Sorts
• • Temporary Disk Space Assignment
• • SQL Operations that Use Sorts

Session 5: INDEXES

• • Index Overview
• • Selecting Suitable Columns for an Index
• • B*Tree Indexes
• • Rebuild an Index
• • Create Multiple Indexes on the Same Column
• • Composite Indexes
• • Descending Indexes
• • Access Paths with Indexes
• • Index Scans
• • Conditions That Stop Indexes Being Used
• • Parameters that Affect Optimizer Index Choice

Oracle 19c SQL Performance Tuning Training Course

Course Contents - DAY 2

Session 6: ADVANCED INDEXES

• • Bitmap Indexes
• • Key Compressed Indexes
• • Index Organized Tables
• • Function Based Indexes
• • Invisible Indexes
• • Table Partitioning
• • Serial Direct Path Reads

Session 7: JOIN OPERATIONS

• • Understand Access Paths
• • Joining Tables
• • Nested Loops Join
• • Merge Join
• • Cluster Join
• • Hash Join
• • Anti Join and Semi Join
• • Outer Joins
• • Star Join
• • Improve Optimization with Different Access Paths

Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER

• • Overview of the DBMS_SQLTUNE Package
• • Using the SQL Tuning Advisor with SQL Developer

Session 9: SEQUENCES AND VIEWS

• • Sequence Caching
• • Views
• • View Merging
• • Inline Views
• • The WITH Clause

Session 10: USING HINTS

• • Using Hints to Influence Execution Plan
• • Optimization Mode and Goals
• • Access Methods
• • Query Transformations
• • Join Orders
• • Join Operations
• • Hint Examples

Session 11: MISCELLANEOUS

• • Tips for Avoiding Problematic Queries
• • SQL Performance Settings Options
• • Array Size
• • The Shared Pool
• • Intelligent Cursor Sharing
• • Bind Variable Usage
• • Virtual Columns
• • Result Caching
• • Approximate Query Processing
• • Some PL/SQL Performance Issues