Oracle Database 11g: SQL Tuning Workshop

Exploring the Oracle Database Architecture

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

Introduction to SQL Tuning

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

Introduction to the Optimizer

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

Interpreting Execution Plans

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

Application Tracing

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

Optimizer: Table and Index Operations

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

Optimizer Join Methods

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

Optimizer: Other Operators

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

Case Study: Star Transformation

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

Optimizer Statistics

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

Using Bind Variables

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

Using SQL Tuning Advisor

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

Using SQL Access Advisor

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

Using Automatic SQL Tuning

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

SQL Performance Management

• Maintaining SQL Performance and SQL Plan Management: Overview
• SQL Plan Baseline: Architecture
• Important Baseline SQL Plan Attributes
• SQL Plan Selection
• 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
• Enterprise Manager and SQL Plan Baselines