Oracle DataBasics 11g SQL Tuning E-Learning

Order this great E-Learning Training Oracle DataBasics 11g SQL Tuning online, 1 year 24/7 access to rich interactive videos, voice, practice assignments, progress monitoring through reports and tests per subject to test the knowledge directly. After the course you will receive a certificate of participation.

Ensuring optimal Oracle database performance is a key facet of a DBA's day-to-day tasks. In this learning path, you will learn the Oracle methodology for identifying and resolving poorly performing SQL statements, using a variety of tools that are built into the Oracle 12c Database. You will also learn how to use built-in performance analysis features and tools, such as the Automatic Workload Repository and the SQL Performance Analyzer.

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