Oracle Database 12c/18c/19c SQL Tuning

Day 1

• Lesson 1 : Exploring the Oracle Database Architecture
• Lesson 2 : Introduction to SQL Tuning
• Lesson 3 : Introduction to the Optimizer
• Lesson 4 : Interpreting Execution Plans
• Lesson 5 : Application Tracing

Day 2

• Lesson 6 : Optimizer Operators
• Lesson 7 : Optimizer: Join Operators
• Lesson 8 : Other Optimizer Operators
• Lesson 9 : Case Study: Star Transformation
• Lesson 10 : Optimizer Statistics

Day 3

• Lesson 11 : Using Bind Variables
• Lesson 12 : SQL Tuning Advisor
• Lesson 13 : Automating SQL Access Advisor
• Lesson 14 : Automating SQL Tuning
• Lesson 15 : SQL Plan Management

Lesson 1 : Exploring the Oracle Database Architecture

• List the major architectural components of the Oracle Database server
• Explain memory structures
• Describe background processes
• Correlate logical and physical storage structures

Lesson 2 : Introduction to SQL Tuning

• Describe what attributes of a SQL statement can make it perform poorly
• List the Oracle tools that can be used to tune SQL
• List the tuning tasks

Lesson 3 : Introduction to the Optimizer

• Describe the execution steps of a SQL statement
• Discuss the need for an optimizer
• Explain the various phases of optimization
• Control the behavior of the optimizer

Lesson 4 : Interpreting Execution Plans

• Gather execution plans
• Display execution plans
• Interpret execution plans

Lesson 5 : Application Tracing

• Configure the SQL Trace facility to collect session statistics
• Use the TRCSESS utility to consolidate SQL trace files
• Format trace files using the tkprof utility
• Interpret the output of the tkprof command

Lesson 6 : Optimizer Operations

• Describe most of the SQL operators
• List the possible access paths
• Explain how join operations are performed

Lesson 7 : Optimizer: Join Operators

• Describe the SQL operators for joins
• List the possible access paths

Lesson 8 : Other Optimizer Operators

• Describe SQL operators for:
  • Clusters
  • In-List
  • Sorts
  • Filters
  • Set Operations
• Result Cache operators

Lesson 9 : Case Study: Star Transformation

• Define a star schema
• Show a star query plan without transformation
• Define the star transformation requirements
• Show a star query plan after transformation

Lesson 10 : Optimizer Statistics

• Gather optimizer statistics
• Gather system statistics
• Set statistic preferences
• Use dynamic sampling
• Manipulate optimizer statistics

Lesson 11 : Using Bind Variables

• List the benefits of using bind variables
• Use bind peeking
• Use adaptive cursor sharing

Lesson 12 : SQL Tuning Advisor

• Describe statement profiling
• Use SQL Tuning Advisor

Lesson 13 : SQL Access Advisor

• Use SQL Access Advisor

Lesson 14 : Automating SQL Tuning

• Use Automatic SQL Tuning

Lesson 15 : SQL Plan Management

• Manage SQL performance through changes
• Set up SQL Plan Management
• Set up various SQL Plan Management scenarios