Descrizione del corso
This course introduces query optimization techniques by examining Oracle's syntax and statement processing. A good knowledge of SQL is required.
Programma
Introduction to SQL Tuning
Introduction to the Optimizer
- SQL Statement Parsing: Overview
- Optimization During Hard Parse Operation
- Transformer & Estimator
- Cost-Based Optimizer
- Plan Generator
Interpreting Execution Plans
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
Optimizer: Table and Index Operations
- 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
- Sort Merge join
- Hash Join and Cartesian Join
- Outer Joins
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
SQL Tuning Advisor, SQL Access Advisor
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
