Oracle DataBasics 11g SQL Tuning E-Learning

Order this great course online!
✔️ 1 year 24/7 access to interactive videos, speech, practical assignments and progress monitoring.
✔️ Test your knowledge per subject with targeted tests and receive a certificate of participation.

Why choose this course?

Optimizing the performance of an Oracle database is critical for database administrators (DBAs). A poorly performing SQL query can cause significant delays and inefficiencies. This training provides the knowledge and skills necessary to identify and improve underperforming SQL statements using advanced Oracle Database 11g and 12c tools and methodologies.

What you will learn:

• Oracle Methodology: Understand the systematic approach to identifying and optimizing underperforming SQL statements.
• Performance Analysis Tools: Learn to work with Oracle tools such as the Automatic Workload Repository (AWR) and SQL Performance Analyzer to monitor and improve performance.
• Advanced optimization techniques: Learn how to use Oracle's built-in functions for more efficient SQL tuning.
• Practical Applications: Work on real-world assignments that simulate real-world challenges so you gain immediate experience solving database performance problems.

Who should participate?

This training is ideal for:

• Database Administrators (DBAs): Professionals responsible for managing and optimizing Oracle database performance.
• SQL Developers: Developers who want to write and improve efficient SQL statements.
• IT professionals: Anyone involved in database management and optimization in an Oracle environment.
• Those interested in Oracle certifications: Individuals who want to prepare for advanced Oracle certifications.

Course content

Ground Rules for Performance Management

Course: 13 Minutes

• Course Introduction
• Introduction to Performance Management
• Who Does Performance Management
• Tuning Methodology
• Effective Tuning Goals
• General Tuning Session

Basic Tuning Diagnostics

Course: 12 Minutes

• Tuning Objectives
• Top Timed Events
• DB Time
• CPU and Wait Time Tuning Dimensions
• Time Model Overview

Dynamic Performance Views

Course: 13 Minutes

• Dynamic Performance Views
• Wait Events
• Wait Classes
• Wait Event Statistics
• Commonly Observed Wait Events

Performance Diagnosis Tools

Course: 14 Minutes

• Enterprise Manager Overview
• Oracle EM Architecture
• Viewing the Performance Hub Page
• Using the Alert Log in Tuning
• User Trace Files

Scoping Performance Issues

Course: 15 Minutes

• Defining the Scope of a Problem
• Common Tuning Problems
• Tuning Life Cycle Phases
• Performance vs. Business Requirements
• Filing a Performance Service Request

Practice: Basic Tuning Diagnostics

Course: 5 Minutes

• Exercise: View Diagnostic Information

Overview of AWR

Course: 11 Minutes

• Course Introduction
• Automatic Workload Repository
• AWR Architecture
• AWR Administration
• Managing Snapshots with PL/SQL

AWR Snapshots and Reports

Course: 12 Minutes

• Generating AWR Reports
• Reading an AWR Report
• Producing Reports Comparing Periods
• Reading an AWR Comparison Report

Automatic Database Diagnostic Monitor

Course: 19 Minutes

• ADDM Performance Monitoring
• ADDM Methodology
• Observing ADDM Findings
• Manipulating ADDM
• Comparing ADDM Periods
• ADDM Comparison Report

Using Active Session History

Course: 12 Minutes

• An Overview of ASH
• Using ASH Data
• Generating ASH Reports
• Reading an ASH Report

Using Real-Time ADDM

Course: 10 Minutes

• Emergency Monitoring
• Real-Time ADDM
• Real-Time ADDM in the Database
• Using Real-Time ADDM

Practice : Using the Automatic Workload Repository

Course: 6 Minutes

• Exercise: Review an AWR Report

Database Metrics and Alerts

Course: 17 Minutes

• Course Introduction
• Metrics and Statistics
• Oracle Database Metrics
• Viewing Metric Information
• Statistic Histograms
• Database Alerts

Working with Baselines

Course: 18 Minutes

• Comparative Performance Analysis with AWR Baselines
• AWR Baselines
• Types of Baselines
• Creating AWR Baselines
• Managing Baselines and Templates
• Performance Monitoring and Baselines

Real-time Database Operation Monitoring

Course: 15 Minutes

• Real-time Database Operation Monitoring
• Database Operation Concepts
• Identifying a Database Operation
• Workflow of a Database Operation
• Monitoring Load Database Operations
• Database Operations Reporting and Tuning

Practice: Using Baselines

Course: 4 Minutes

• Exercise: Creating Baselines

Monitoring Applications

Course: 16 Minutes

• Course Introduction
• Defining a Service
• Service Attributes and Types
• Creating Services
• Working with Services
• Resource Management and Services

Advanced Application Monitoring

Course: 18 Minutes

• Services and the Scheduler
• Using Services with Metric Thresholds
• Service Aggregation and Tracing
• Service Aggregation Configuration
• Service Performance Views

SQL Processing

Course: 24 Minutes

• Phases of SQL Statement Processing
• SQL Statement Parsing
• Processing a DML Statement
• The Oracle Optimizer
• Introduction to Execution Plans
• Viewing Execution Plans

Advanced SQL Diagnostics

Course: 22 Minutes

• Reading an Execution Plan
• Execution Plan Details
• SQL*Plus Autotrace
• Extended SQL Tracing
• Formatting Trace Files with TKPROF
• TKPROF and Optimizer Tracing

Practice : Advanced SQL Diagnostics

Course: 6 Minutes

• Exercise: Use AutoTrace and DBMS_XPLAN

Influencing the Optimizer

Course: 25 Minutes

• Course Introduction
• Functions of the Query Optimizer
• Optimizer Key Concepts
• Optimizer Statistics
• Optimizer Parameters
• Adaptive and Dynamic Execution Plans
• Cardinality Feedback

Optimizer Access Paths and Join Orders

Course: 27 Minutes

• Optimizing SQL for Throughput
• Access Paths
• Index Operations
• Join Operations
• Types of Join Operations
• Join Performance
• Sort Operations and Performance

Indexing and Block Visits

Course: 25 Minutes

• Adding Indexes
• Maintaining Indexes
• Table Maintenance for Performance
• Extent Management
• The Anatomy of a Database Block
• Block Visits and Allocation
• Block Space Management with Freelists

Compressing Segments

Course: 22 Minutes

• Automatic Segment Space Management
• Block Space Management with ASSM
• Migration and Chaining
• Shrinking Segments
• Data Compression
• Hybrid Columnar Compression

Practice: Influencing the Optimizer

Course: 5 Minutes

• Exercise: Influencing Performance with Statistics

Using the SQL Performance Analyzer

Course: 20 Minutes

• Course Introduction
• Real Application Testing
• SQL Performance Analyzer
• SQL Performance Analyzer Tasks
• Preventing Regressing SQL Statements
• SQL Performance Analyzer Example

SQL Performance Management

Course: 27 Minutes

• Maintaining SQL Performance
• Managing Optimizer Statistics
• Deferred Statistics Publishing
• Automatic SQL Tuning
• SQL Tuning Advisor
• SQL Access Advisor

SQL Plan Management

Course: 24 Minutes

• SQL Plan Baselines
• The Lifecycle of SQL Plan Baselines
• Adaptive SQL Plan Management
• SQL Plan Manageability
• SPM Configuration and Enterprise Manager

Using Database Replay

Course: 13 Minutes

• Database Replay
• Database Replay Architecture
• Database Replay Integration
• Capturing Workload

Executing a Replay and Viewing Results

Course: 15 Minutes

• Replay Tasks and Considerations
• Running a Replay
• Replay Analysis
• Replay Packages and Views

Practice: SQL Plan Management

Course: 6 Minutes

• Exercise: Implement Plan Stability with SPM

Tuning the Shared Pool

Course: 24 Minutes

• Course Introduction
• Shared Pool Architecture
• Latch and Mutex
• Tuning the Shared Pool
• Using AWR Reports to Tune the Shared Pool
• Parsing and Cursor Sharing

Advanced Tuning of the Shared Pool

Course: 26 Minutes

• Adaptive Cursor Sharing
• Sizing the Shared Pool
• Fragmentation Within the Shared Pool
• SQL Query Result Cache: Part 1
• SQL Query Result Cache: Part 2

Tuning the Buffer Cache

Course: 27 Minutes

• Oracle Database Architecture
• Buffer Cache Internals
• Tuning Goals and Techniques
• Buffer Cache Contention: Part 1
• Buffer Cache Contention: Part 2
• The Buffer Cache Advisor

Advanced Tuning of the Buffer Cache

Course: 23 Minutes

• Multiple Buffer Pools
• Background Processes and the Buffer Cache
• I/O Tuning and the Buffer Cache
• Database Smart Flash Cache
• Using Database Smart Flash Cache

Tuning PGA and Temporary Space

Course: 25 Minutes

• SQL Memory Usage
• Configuring Automatic PGA Memory
• Monitoring Automatic PGA Usage
• Temporary Tablespaces
• Temporary Tablespace Groups
• Temporary Tablespace Maintenance

Practice: Tuning the Shared Pool

Course: 7 Minutes

• Exercise: Keep Objects in the Shared Pool

Using Automatic Memory Management

Course: 28 Minutes

• Course Introduction
• Dynamic SGA
• Manipulating SGA Components
• Behavior of SGA Parameters
• ASMM Maintenance
• Automatic Memory Management
• Automatic Memory Management Maintenance

Common Resolutions to CPU and Redo Waits

Course: 29 Minutes

• Methodology and Common Symptoms
• Handling Errors
• CPU Waits
• Redo Waits
• Redo Log Waits
• Buffer Cache Waits

Resolutions to SGA and SQL Execution Waits

Course: 20 Minutes

• Shared and Large Pool Waits
• Tablespace Best Practices
• SQL Execution Waits
• I/O Modes
• Statistics Gathering

Practice: Using Automatic Memory Management

Course: 6 Minutes

• Exercise: Use the Automatic Memory Tuning Function

Get started with Oracle Database 11g SQL Tuning!

✔️ Learn at your own pace with extensive interactive videos and practical assignments.
✔️ Progress monitoring with reports to track your learning process.
✔️ Receive a certificate of participation upon successful completion of the course.

Order your course now and improve your Oracle SQL tuning skills immediately!