Oracle SQLTuning Training

Course Details

Introduction to training ORACLE SQL Tuning for Developers

• Course objectives, program and annexes

• Target audience and prerequisites for Oracle SQL Tuning for Developers training

• Schemas used in the Oracle SQL Tuning for Developers course

• Account information used for the Oracle SQL Tuning for Developers course

• SQL environments are available for the Oracle SQL Tuning for Developers course

• Oracle SQL Tuning Developer Labs, Demo Scripts, and Sample Code Scripts

• Oracle SQL Tuning for Developers course appendices

Introduction to SQL Statement Tuning

• SQL tuning session

• SQL tuning strategies

• SQLTXPLAIN Diagnostic Tool (SQLT)

Use application tracing tools

• SQL Trace Utility Overview

• Operations to perform before a trace

• Overview of tracing tools

• trcsess utility

• Formatting SQL trace files

Understand basic tuning techniques

• Write an efficient SQL statement

• Scripts used for the chapter

• Table design

• Using indexes

• Transformed index

• Data type mismatch

• Using NULL Values

• Setting an ORDER BY clause

Optimizer Fundamentals

• Representation of SQL statements

• Processing SQL statements

• Why use an optimizer?

• Optimizer Components

• Query Transformation Tool

• Cost-based optimizer

• Adaptive query optimization

• Optimizer Features and Oracle Database Versions

Generate and view execution plans

• Presentation of execution plans

• EXPLAIN PLAN command

• Plan table

• AUTOTRACE

• V$SQL_PLAN view

• AWR repository (Automatic Workload Repository)

• SQL monitoring function

Interpret Execution Plans

• Understanding serial execution plans

• Adaptive Optimizations

Optimizer: Table and Index Paths

• Operations on row sources

• Main structures and access paths

• Full table scan

• Index

• Common observations

Optimizer Joins Operations

• Join methods

• Join Types

Other Optimizer Operators

• SQL Operators

• Other operations of degree N

• Results Cache Related Operators

Overview of Optimizer Statistics

• Optimizer Statistics

• Types of statistics

• Collecting and managing optimizer statistics

Use Attached Variables

• Sharing cursors and different literal values

• Sharing cursors and attached variables

SQL Plan Management

• Guarantee SQL code performance

• SQL Plan Management