Oracle Database 11g: SQL Tuning Workshop Ed 2
Duration : 3 Days (24 Hours)
Oracle Database 11g: SQL Tuning Workshop Ed 2 Course Overview:
The “Oracle Database 11g: SQL Tuning Workshop Ed 2” course is structured into four modules: the first introduces the importance of optimizing SQL code and Oracle Optimizer fundamentals; the second focuses on specific tuning aspects like access paths and execution plans; the third covers query optimization techniques and principles; and the final delves into advanced methods like materialized views and SQL Plan Baseline. Through these modules, participants gain comprehensive insights into Oracle Optimizer, its capabilities, and SQL tuning package, enabling them to optimize their own SQL statements for peak performance.
- Oracle Database Professionals:
- Database Administrators
- System Administrators
- Database Developers
- Solution Architects
- Database Designers
- Database Application Developers
- IT Professionals new to Oracle Database 11g seeking comprehensive understanding of major aspects like monitoring database performance, configuring the database optimizer, and resource management with Oracle Database Tuning.
Learning Objectives of Oracle Database 11g: SQL Tuning Workshop Ed 2:
1. Understand the fundamentals of the Oracle Cost-Based Optimizer, including the role of execution plans and statistics in the optimization process.
2. Use Automatic Tuning Optimizer and Real-Time SQL Monitoring.
3. Analyze workloads related to performance bottleneck issues.
4. Create and tune SQL profiles.
5. Tune poorly-performing SQL queries.
6. Understand the purpose and use of Indexes and Partitioning.
7. Use the SQL Access Advisor to recommend best practices for short-term vs. long-term solutions.
8. Leverage the Oracle Database advisors to optimize SQL.
9. Diagnose performance problems using wait events and histograms.
10. Explore Baseline Templates and SQL Baselines to automate performance tuning.
Module 1: 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
Module 2: 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
Module 3: 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
Module 4: 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
Module 5: 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
Module 6: 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
Oracle Database 11g: SQL Tuning Workshop Ed 2 Course Prerequisites:
The prerequisite for the Oracle Database 11g: SQL Tuning Workshop Ed 2 Training is the Oracle Database 11g: Introduction to SQL Ed 2 course, or a basic knowledge of SQL. This class is intended for application developers and database administrators who need to tune inefficient SQL statements for improved performance.
Discover the perfect fit for your learning journey
Choose Learning Modality
This course comes with following benefits:
- Practice Labs.
- Get Trained by Certified Trainers.
- Access to the recordings of your class sessions for 90 days.
- Digital courseware
- Experience 24*7 learner support.
Got more questions? We’re all ears and ready to assist!