Call : (+91) 968636 4243
Mail : info@EncartaLabs.com

Oracle - Database Tuning

( Duration: 5 Days )

This Oracle - Database Tuning training course explores core tuning skills such as Database parameters, SQL Tuning Advisor, SQL Access Advisor, Adaptive SQL plans and more.

By attending Oracle - Database Tuning workshop, delegates will learn:

Overview Oracle Database Architecture
Query Optimizer
Tuning Container Databases and Pluggable Databases
Oracle 12c Tuning features
Evaluating Execution Plans
Oracle Tuning Tools
Using Automatic Workload Repository
Join Types
AWR Using Baselines
Additional AWR performance tools
Optimizer Statistics
Monitoring a Service
Bind Variables and database parameters
Oracle's Real Application Testing (RAT)
SQL Tuning Advisor
Automatic Sql Tuning
Sql Plan Management
Shared Pool Tuning
Tuning the database buffer cache
Tuning the PGA (Program Global Area)
Automatic Memory Management (AMM)
Tuning Segment Space Utilization (ASSM)
Automatic Storage Management

Experience working with Oracle Database 18 or higher.

COURSE AGENDA

Overview Oracle Database Architecture

Instance Definition
Define SGA
Define Background Processes
Datafile Definition

Query Optimizer

SQL Parsing
Optimizing Terms
Optimizing Methods
Query Plan Generation
Query Plan Control

Tuning Container Databases and Pluggable Databases

Pluggable tuning parameters
Define Container tuning structure
Using PDB$SEED
Create a new PDB
Plug and unplug a PDB

Oracle 12c Tuning features

Identifying and Using Oracle's Heat Map
12c Compression Levels and Types

Evaluating Execution Plans

Defining SQL execution plans
Automatic Workload Repository
Reading execution plans

Oracle Tuning Tools

Monitoring tools overview
Enterprise Manager
Dynamic Performance Views
Automatic Workload Repository
Automatic Database Diagnostic Monitor
Sql Tuning Advisor
SQL Access Advisor
Sql Access Advisor
DB operation Tuning
DB operation Active Reporting

Using Automatic Workload Repository

Defining AWR
AWR Settings
Creating AWR Baselines

Metrics, Alerts, and Thresholds

Defining Metrics
Setting Alerts
Setting Corrective Actions
User Defined Metrics
Metric Dynamic Views

Join Types

Nested Loops Join
Sort Merge join
Hash Join and Cartesian Join
Equijoins and Nonequijoins
Outer Joins
Semijoins

AWR Using Baselines

Creating AWR baselines
Creating AWR Repeating baselines
Moving Window Baseline

Additional AWR performance tools

Automatic Maintenance Tasks
Segment Advisor
Statistics Gathering
Automatic Tuning Optimizer
Automatic Database Diagnostic Monitor
Active Session History (ASH)

Optimizer Statistics

Optimizer Statistics Overview
Table and Index Statistics
Statistic Preferences
Statistics Gathering
Locking Statistics, Export/Import Statistics
Pending and published statistics
Optimizer Hints
Optimizer Paths
Cost Base Optimization

Monitoring a Service

Overview of what is an Oracle Service
Creating an Oracle Service for Single instance and RAC
Monitoring a Service
Resource Management and a Service
Enterprise Manager and a Service

Bind Variables and database parameters

Bind variable definition
Cursor_sharing parameter
Adaptive Cursor Sharing

Oracle's Real Application Testing (RAT)

Sql Performance Analyzer overview
Sql Performance Analyzer Options
Database Parameter changes
Database version changes
Creating SQL Tuning Sets
Database Replay Overview
Database Replay Configuration
Database Replay Options

SQL Tuning Advisor

SQL Tuning Advisor: Overview
SQL Tuning Advisor Limited Mode
Sql Tuning Advisor Comprehensive mode
Sql Tuning Profiles

SQL Access Advisor

SQL Access Advisor: Overview
Sql Access Advisor options
SQL Access Advisor and Sql Tuning Sets
Sql Access Advisor and AWR
Results and Implementation

Automatic Sql Tuning

Automatic Sql Tuning Maintenance Task
Automatic Tuning Optimization implementation(ATO)
Automatic Tuning Optimization Results
Enable/Disable Automatic Tuning Optimization

Sql Plan Management

Sql plan Management and baseline overview
Enable sql plan management
Loading Sql Plan baselines into the SGA
Adaptive plan management

Shared Pool Tuning

Shared pool architecture
Shared pool parameters
Library Cache
Dictionary cache
Large pool considerations and contents

Tuning the database buffer cache

Database buffer cache overview
Database buffer cache parameters
Oracle and Dirty reads and writes
Automatic Shared Memory Management (ASMM)
Buffer Cache goals and responsibility
Buffer Cache pools

Tuning the PGA (Program Global Area)

PGA Overview
PGA Database Parameters
Temporary Segments
Temporary Tablespace
Sizing the PGA

Automatic Memory Management (AMM)

Oracle's Automatic Memory Management Overview
Database Auto-tuned Parameters
Database Non Auto-tuned Parameters
Automatic Memory Management Hints and Sizing suggestions
AMM versus ASMM

Tuning Segment Space Utilization (ASSM)

Overview of Automatic Segment Space Management
Defining the DB_BLOCK_SIZE
Defining DB_nk_CACHE_SIZE parameter
The DB_BLOCK_SIZE Parameter
Overview of table compression, block chaining, and block migration

Automatic Storage Management

Overview of ASM
Definition of Grid Infrastructure
ASM Instance
ASM Diskgroups
ASM Diskgroup parameters and templates
ASMCMD

Encarta Labs Advantage

One Stop Corporate Training Solution Providers for over 6,000 various courses on a variety of subjects
All courses are delivered by Industry Veterans
Get jumpstarted from newbie to production ready in a matter of few days

Trained more than 50,000 Corporate executives across the Globe
All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting https://www.encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top

Notice