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

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

1

Overview Oracle Database Architecture

  • Instance Definition
  • Define SGA
  • Define Background Processes
  • Datafile Definition
2

Query Optimizer

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

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
4

Oracle 12c Tuning features

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

Evaluating Execution Plans

  • Defining SQL execution plans
  • Automatic Workload Repository
  • Reading execution plans
6

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
7

Using Automatic Workload Repository

  • Defining AWR
  • AWR Settings
  • Creating AWR Baselines
8

Metrics, Alerts, and Thresholds

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

Join Types

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

AWR Using Baselines

  • Creating AWR baselines
  • Creating AWR Repeating baselines
  • Moving Window Baseline
11

Additional AWR performance tools

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

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
13

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
14

Bind Variables and database parameters

  • Bind variable definition
  • Cursor_sharing parameter
  • Adaptive Cursor Sharing
15

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
16

SQL Tuning Advisor

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

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
18

Automatic Sql Tuning

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

Sql Plan Management

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

Shared Pool Tuning

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

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
22

Tuning the PGA (Program Global Area)

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

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
24

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
25

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
X