Detailed Course Outline
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