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
