SQL TUNING COURSE CONTENT

Exploring the Oracle Database Architecture

• Oracle Database Server Architecture: Overview
• Oracle Database Memory Structures: Overview
• Background Process Roles
• Automatic Shared Memory Management
• Automated SQL Execution Memory Management
• Automatic Memory Management
• Database Storage Architecture
• Logical and Physical Database Structures

Introduction to SQL Tuning

• Reasons for Inefficient SQL Performance
• Performance Monitoring Solutions
• Monitoring and Tuning Tools: Overview
• EM Performance Pages for Reactive Tuning
• CPU and Wait Time Tuning Dimensions
• Scalability with Application Design, Implementation, and Configuration
• Common Mistakes on Customer Systems
• Proactive Tuning Methodology

Introduction to the Optimizer

• Structured Query Language
• SQL Statement Representation, Implementation & Processing: Overview
• SQL Statement Parsing: Overview
• Why Do You Need an Optimizer?
• Optimization During Hard Parse Operation
• Cost-Based Optimizer
• Controlling the Behavior of the Optimizer
• Optimizer Features and Oracle Database Releases

Optimizer Operators

• Row Source Operations
• Main Structures and Access Paths
• Full Table Scan
• Indexes: Overview
• Using Indexes: Considering Nullable Columns
• Bitmap Indexes, Composite Indexes &Invisible Index
• Guidelines for Managing Indexes
• Clusters

Interpreting Execution Plans

• Execution Plan
• Links Between Important Dynamic Performance Views
• Automatic Workload Repository (AWR)
• Generating SQL Reports from AWR Data
• SQL Monitoring: Overview
• Reading More Complex Execution Plans
• Reviewing the Execution Plan
• Looking Beyond Execution Plans

Case Study: Star Transformation

• The Star & Snowflake Schema Model
• Execution Plan Without Star Transformation
• Retrieving Fact Rows from One Dimension All Dimensions
• Joining the Intermediate Result Set with Dimensions
• Using Bitmap Join Indexes
• Star Transformation
• Bitmap Join Indexes

Optimizer Statistics

• Optimizer Statistics & Types of Optimizer Statistics
• Multicolumn Statistics: Overview
• Expression Statistics: Overview
• Gathering System Statistics
• Statistic Preferences: Overview
• Optimizer Dynamic Sampling: Overview
• Locking Statistics

Using Bind Variables

• Cursor Sharing and Different Literal Values
• Cursor Sharing and Bind Variables
• Bind Variables in SQL*Plus & Enterprise Manager
• Cursor Sharing Enhancements
• Adaptive Cursor Sharing: Overview
• Interacting with Adaptive Cursor Sharing

Using Optimizer Hints

• Optimizer Hints: Overview
• Types of Hints
• Specifying Hints
• Rules for Hints
• Hint Recommendations
• Hint Categories
• Optimization Goals and Approaches
• Additional Hints

Application Tracing

• End-to-End Application Tracing Challenge
• Location for Diagnostic Traces
• What Is a Service?
• Use Services with Client Applications
• Trace Your Own Session
• SQL Trace File Contents
• Formatting SQL Trace Files: Overview
• Invoking the tkprof Utility

Automating SQL Tuning

• Tuning SQL Statements Automatically
• Application Tuning Challenges
• SQL Tuning Advisor: Overview
• Stale or Missing Object Statistics
• SQL Statement Profiling
• Plan Tuning Flow and SQL Profile Creation
• Database Control and SQL Tuning Advisor
• Implementing Recommendations