Oracle Database 19c: Performance Management and Tuning

Oracle Database 19c: Performance Management and Tuning

Oracle 19C Database Tuning is an intermediate-level course for Oracle database experienced attendees that explores core tuning skills such as Database parameters, SQL Tuning Advisor, SQL Access Advisor, Adaptive SQL plans, and more.

The Oracle Database 19c: Performance Management and Tuning OCP has demonstrated a strong understanding of performance analysis, management, and tuning for optimal performance of an Oracle Database environment. They can apply a database tuning methodology by using the appropriate tools in order to monitor, diagnose and tune instance and SQL performance problems.

Course Key Benefits

• Use the Oracle Database tuning methodology appropriate to the available tools
• Utilize database advisors to proactively tune an Oracle Database Instance
• Use the tools based on the Automatic Workload Repository to tune the database
• Diagnose and tune common SQL-related performance problems
• Diagnose and tune common Instance related performance problems
• Use Enterprise Manager performance-related pages to monitor an Oracle Database

Course Outline:

Module1: Overview of Oracle Database Architecture

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

Module2: Query Optimizer

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

Module3: 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

Module4: Oracle 12c Tuning features

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

Module5: Evaluating Execution Plans

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

Module6: 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

Module7: Using Automatic Workload Repository

• Defining AWR
• AWR Settings
• Creating AWR Baselines

Module8: Metrics, Alerts, and Thresholds

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

Module9: Join Types

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

 Module10: AWR Using Baselines

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

 Module11: Additional AWR performance tools

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

 Module12: 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

 Module13: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

 Module14:Bind Variables and database parameters

• Bind variable definition
• Cursor_sharing parameter
• Adaptive Cursor Sharing

Module15: 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

Module16:SQL Tuning Advisor

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

Module17: 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

Module18:Automatic SQL Tuning

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

Module19: SQL Plan Management

• SQL plan Management and baseline overview
• Enable SQL plan management
• Loading SQL Plan baselines into the SGA
• Adaptive plan management

Module20:Shared Pool Tuning

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

Module21: Tuning the PGA (Program Global Area)

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

Module22: Automatic Storage Management

• Overview of ASM
• Definition of Grid Infrastructure
• ASM Instance
• ASM disk groups
• ASM Diskgroup parameters and templates
• ASM

Course Benefit: 

After completion of this course, you will learn the following topics:

• Diagnose and tune regular Instance related execution issues
• Use Enterprise Manager execution-related pages to screen an Oracle database
• Use the Oracle Database tuning procedure
• Utilize database counsels to proactively tune an Oracle Database Instance
• Use the devices dependent on the Automatic Workload Repository to tune the database
• Diagnose and tune basic SQL-related execution issues

Course Prerequisites