Formation ORACLE 19C DATABASE TUNING

Module 1: Overview Oracle Database Architecture

Instance Definition

Understanding SGA (System Global Area)

Background Processes Overview

Datafile Definition

Module 2: Query Optimizer

SQL Parsing

Optimizing Terms and Methods

Query Plan Generation

Query Plan Control

Module 3: Tuning Container Databases and Pluggable Databases

Tuning Parameters for Pluggable Databases

Understanding Container Tuning Structure

Working with PDB$SEED

Creating and Managing PDBs

Plug and Unplug Operations for PDBs

Module 4: Oracle 12c Tuning Features

Utilizing Oracle's Heat Map for Identification

Understanding 12c Compression Levels and Types

Module 5: Evaluating Execution Plans

Defining SQL Execution Plans

Reading and Analyzing Execution Plans

Module 6: Oracle Tuning Tools

Overview of Monitoring Tools

Utilizing Enterprise Manager

Dynamic Performance Views

Working with Automatic Workload Repository (AWR)

Automatic Database Diagnostic Monitor (ADDM)

SQL Tuning Advisor

SQL Access Advisor

Database Operation Tuning and Active Reporting

Module 7: Using Automatic Workload Repository

AWR Overview and Settings

Creating AWR Baselines

Module 8: Metrics, Alerts, and Thresholds

Defining Metrics and Setting Alerts

Implementing Corrective Actions

Utilizing User-Defined Metrics and Dynamic Views

Module 9: Join Types

Understanding Various Join Types like Nested Loops, Sort Merge, Hash, Cartesian, Equijoins, Nonequijoins, Outer Joins, and Semijoins

Module 10: Additional AWR Performance Tools

Automatic Maintenance Tasks

Segment Advisor

Statistics Gathering

Automatic Tuning Optimizer

Active Session History (ASH)

Module 11: Optimizer Statistics

Overview of Optimizer Statistics

Table and Index Statistics

Statistic Preferences and Gathering Techniques

Locking and Export/Import Statistics

Pending and Published Statistics

Utilizing Optimizer Hints and Paths

Cost-Based Optimization

Module 12: Monitoring a Service

Oracle Service Overview

Creating and Monitoring Oracle Services

Resource Management with Services

Utilizing Enterprise Manager for Service Monitoring

Module 13: Bind Variables and Database Parameters

Understanding Bind Variables

Cursor Sharing and Adaptive Cursor Sharing

Module 14: Oracle's Real Application Testing (RAT)

Overview of SQL Performance Analyzer

SQL Performance Analyzer Options and Implementation

Database Replay Configuration and Options

Module 15: SQL Tuning Advisor

Comprehensive Overview of SQL Tuning Advisor

Limited and Comprehensive Mode

SQL Tuning Profiles

Module 16: SQL Access Advisor

Overview and Options of SQL Access Advisor

Utilizing SQL Access Advisor with SQL Tuning Sets and AWR

Module 17: Automatic SQL Tuning

Overview of Automatic SQL Tuning

Implementation and Maintenance Tasks

Enabling/Disabling Automatic Tuning Optimization

Module 18: SQL Plan Management

Baseline Overview and Management

Enabling SQL Plan Management

Loading Baselines and Adaptive Plan Management

Module 19: Shared Pool Tuning

Shared Pool Architecture and Parameters

Library Cache and Dictionary Cache Management

Large Pool Considerations

Tuning Database Buffer Cache

Module 20: Tuning the PGA (Program Global Area)

Understanding PGA Parameters and Sizing

Temporary Segments and Tablespace Management

Module 21: Automatic Memory Management (AMM)

Overview of AMM

Auto-Tuned Parameters and Non-Auto-Tuned Parameters

Hints and Sizing Suggestions

Module 22: Tuning Segment Space Utilization (ASSM)

Automatic Segment Space Management Overview

Configuring DB_BLOCK_SIZE and DB_nk_CACHE_SIZE Parameters

Table Compression, Block Chaining, and Block Migration Overview

Module 23: Automatic Storage Management (ASM)

Introduction to ASM and Grid Infrastructure

ASM Instance and Diskgroups Management

ASMCMD Commands and Utilities