Oracle Database 11g: SQL Tuning Workshop

Aidience

• Application Developers
• Database Administrators
• Developer
• Support Engineer
• Data Warehouse Developer
• Data Warehouse Administrator
• PL/SQL Developer

Course Topics

1. Exploring the Oracle Database Architecture

• Oracle Database Server Architecture: Overview
• Automated SQL Execution Memory Management
• Database Storage Architecture, Logical and Physical Database Structures
• Physical Structure
• Segments, Extents, and Blocks & SYSTEM and SYSAUX Tablespaces
• Automatic Shared Memory Management
• Connecting to the Database Instance
• Oracle Database Memory Structures: Overview

2. Introduction to SQL Tuning

• Monitoring and Tuning Tools: Overview
• Scalability with Application Design, Implementation, and Configuration
• Simplicity in Application Design
• Reason for Inefficient SQL Performance
• Performance Monitoring Solutions
• Common Mistakes on Customer systems & Proactive Tuning Methodology
• CPU and Wait Time Tuning Dimensions
• Data Modeling, Table Design, Index Design, Using Views, SQL Execution Efficiency, Overview of SQL*Plus & SQL Developer

3. Introduction to the Optimizer

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

4. Interpreting Execution Plans

• Looking Beyond Execution Plans
• Automatic Workload Repository (AWR)
• Interpreting an Execution Plan
• Using the V$SQL_PLAN View
• What Is an Execution Plan? Where To Find Execution Plans and Viewing Execution Plans
• Reading More Complex Execution Plans and Reviewing the Execution Plan
• SQL Monitoring: Overview
• Plan Table & AUTOTRACE

5. Application Tracing

• End-to-End Application Tracing Challenge
• Use Enterprise Manager to Trace Services
• tkprof Output with and without Index: Example
• Location for Diagnostic Traces
• What is a Service? Use Services with Client Applications & Tracing Services
• Session Level Tracing: Example
• The trcsess Utility and SQL Trace File Contents
• Invoking the tkprof Utility and Output of the tkprof Command

6. Optimizer: Table and Index Operations

• Row Source Operations, Main Structures and Access Paths
• Index-Organized Tables
• Bitmap Indexes, Bitmap Operations and Bitmap Join Index
• Guidelines for Managing Indexes and Investigating Index Usage
• Using Indexes: Considering Nullable Columns
• Full Table Scan
• Indexes: Overview and B*-tree Indexes and Nulls
• Composite Indexes and Invisible Index

7. Optimizer Join Methods

• Hash Join and Cartesian Join
• Equijoins and Nonequijoins
• Outer Joins
• Antijoins
• Nested Loops Join
• Nested Loops Join: 11g Implementation
• Semijoins
• Sort Merge join

8. Optimizer: Other Operators

• Result Cache Operator
• When Are Clusters Useful?
• Filter operations and Concatenation Operations
• Count Stop Key Operator
• Min/Max and First Row Operators and Other N-Array Operations
• Sorting Operators and Buffer Sort Operator
• Inlist Iterator and View Operator
• UNION [ALL], INTERSECT, MINUS

9. Case Study: Star Transformation

• Star Transformation Hints
• Using Bitmap Join Indexes
• The Star Schema Model and The Snowflake Schema Model
• Bitmap Join Indexes: Join Model 1 to 4
• Star Transformation Plan Examples
• Star Transformation
• Retrieving Fact Rows from One Dimension and from All Dimensions
• Joining the Intermediate Result Set with Dimensions

10. Optimizer Statistics

• Locking Statistics, Export/Import Statistics and Set Statistics
• Gathering System Statistics and Statistic Preferences
• Manual Statistics Gathering
• Table, Index and Column Statistics
• Histograms, Frequency Histograms and Histogram Considerations
• Types of Optimizer Statistics
• Multicolumn Statistics and Expression Statistics Overview
• Index Clustering Factor

11. Using Bind Variables

• Bind Variable Peeking
• Cursor Sharing Enhancements
• The CURSOR_SHARING Parameter
• Interacting with Adaptive Cursor Sharing
• Cursor Sharing and Different Literal Values
• Forcing Cursor Sharing
• Adaptive Cursor Sharing
• Cursor Sharing and Bind Variables

12. Using SQL Tuning Advisor

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

13. Using SQL Access Advisor

• SQL Access Advisor: Overview
• SQL Access Advisor: Schedule and Review
• SQL Access Advisor: Workload Source
• SQL Access Advisor: Results
• Possible Recommendations
• SQL Access Advisor Session: Initial Options
• SQL Access Advisor: Recommendation Options
• SQL Access Advisor: Results and Implementation

14. Using Automatic SQL Tuning

• Automatic SQL Tuning
• Automatic SQL Tuning: Result Summary
• Configuring Automatic SQL Tuning
• SQL Tuning Loop
• Automatic SQL Tuning: Result Details
• Automatic SQL Tuning Result Details: Drilldown
• Automatic SQL Tuning Considerations
• Automatic Tuning Process

15. SQL Performance Management

• SQL Plan Baseline: Architecture
• Maintaining SQL Performance and SQL Plan Management: Overview
• SQL Plan Selection
• Enterprise Manager and SQL Plan Baselines
• Possible SQL Plan Manageability Scenarios
• SQL Performance Analyzer and SQL Plan Baseline Scenario
• Loading a SQL Plan Baseline Automatically and Purging SQL Management Base Policy
• Important Baseline SQL Plan Attributes