Oracle Database 19c DBA Performance Tuning & Management Training Course

Course Contents - DAY 1

Course Introduction

• • Administration and Course Materials
• • Course Structure and Agenda
• • Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

• • Tuning Overview of Oracle Database Tuning
• • Application Developer Tuning Responsibilities
• • Oracle DBA Tuning Responsibilities
• • Oracle Tuning Process
• • Plan a Routine Monitoring Regime
• • Setting Suitable Goals
• • Syntax Considerations

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

• • Overview of SQL Statement Tuning
• • Tools to Assist in SQL Tuning
• • Use Explain Plan,Autotrace and SQL Trace to Examine the Execution of a SQL Statement
• • Interpreting a SQL Trace

Session 3: THE SQL OPTIMIZER

• • The SQL Optimizer
• • Statement Transformation
• • The Optimizer_Mode Initialization Parameter
• • Cost Based Optimizer
• • Managing Statistics with DBMS_STATS
• • Automatic Statistics Gathering
• • Dynamic Statistics
• • Adaptive Optimization
• • Transferring Statistics between Databases

Session 4: SORTS

• • How Oracle Processes Sorts
• • Temporary Disk Space Assignment
• • SQL Operations that Use Sorts

Session 5: INDEXES

• • Index Overview
• • Selecting Suitable Columns for an Index
• • B*Tree Indexes
• • Rebuild an Index
• • Create Multiple Indexes on the Same Column
• • Composite Indexes
• • Descending Indexes
• • Access Paths with Indexes
• • Index Scans
• • Conditions That Stop Indexes Being Used
• • Parameters that Affect Optimizer Index Choice

Oracle Database 19c DBA Performance Tuning & Management Training Course

Course Contents - DAY 2

Session 6: ADVANCED INDEXES

• • Bitmap Indexes
• • Key Compressed Indexes
• • Index Organized Tables
• • Function Based Indexes
• • Invisible Indexes
• • Table Partitioning
• • Serial Direct Path Reads

Session 7: JOIN OPERATIONS

• • Understand Access Paths
• • Joining Tables
• • Nested Loops Join
• • Merge Join
• • Cluster Join
• • Hash Join
• • Anti Join and Semi Join
• • Outer Joins
• • Star Join
• • Improve Optimization with Different Access Paths

Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER

• • Overview of the DBMS_SQLTUNE Package
• • Using the SQL Tuning Advisor with SQL Developer

Session 9: SEQUENCES AND VIEWS

• • Sequence Caching
• • Views
• • View Merging
• • Inline Views
• • The WITH Clause

Session 10: USING HINTS

• • Using Hints to Influence Execution Plan
• • Optimization Mode and Goals
• • Access Methods
• • Query Transformations
• • Join Orders
• • Join Operations
• • Hint Examples

Session 11: MISCELLANEOUS

• • Tips for Avoiding Problematic Queries
• • Oracle 12.2 SQL*Plus Performance Setting Options
• • Array Size
• • The Shared Pool
• • Intelligent Cursor Sharing
• • Virtual Columns
• • Bind Variable Usage
• • Result Caching
• • Approximate Query Processing
• • Reduce Cursor Invalidations for DDLs
• • Some PL/SQL Performance Issues

Oracle Database 19c DBA Performance Tuning & Management Training Course

Course Contents - DAY 3

Session 12: BASIC TUNING DIAGNOSTICS

• • Performance Tuning Diagnostics,Features,and Tools
• • DB Time
• • CPU and Wait Time Tuning Dimensions
• • Time Model
• • Dynamic Performance Views
• • Database Statistics
• • Wait Events
• • Diagnostic Sources
• • Log Files and Trace Files

Session 13: REDUCE THE COST OF SQL OPERATIONS

• • Identify Unusable Objects
• • Maintain Indexes
• • Maintain Tables and Reorganize Tables
• • Manage Extents
• • Row Chaining and Row Migration
• • Segment Shrink

Session 14: THE SQL PERFORMANCE ANALYZER

• • An Overview of the SQL Performance Analyzer
• • Usage of the SQL Performance Analyzer
• • Capture a SQL Workload into a SQL Tuning Set
• • Create a SQL Performance Analyzer Task
• • Generate Comparison Reports
• • Configuring Analysis Tasks
• • Transfer SQL Tuning Sets

Session 15: SQL PERFORMANCE MANAGEMENT

• • Maintenance of the Optimizer Statistics
• • Optimizer Statistics Collection
• • Gather Statistics Options
• • Defer Publishing Statistics
• • The Optimizer Statistics Advisor
• • The Expression Statistics Store
• • Adaptive Query Optimization
• • Automatic SQL Tuning
• • SQL Monitoring
• • The SQL Access Advisor
• • SQL Plan Management

Session 16: AUTOMATIC MEMORY MANAGEMENT

• • Overview of Automatic Shared Memory Management
• • Dynamic SGA
• • Parameters for Sizing the SGA
• • Enable and Disable Automatic Shared Memory Management
• • Overview of Automatic Memory Management
• • Enable and Disable Automatic Memory Management
• • Monitor Dynamic Memory Allocation
• • Use the Memory Advisors

Session 17: TUNE THE SHARED POOL

• • Overview of the Shared Pool Architecture
• • Tune the Shared Pool
• • Tune the Data Dictionary Cache
• • Tune the Library Cache
• • Pin objects in the Shared Pool
• • The Data Dictionary Cache
• • Latches and Mutexes
• • The SQL Query Result Cache

Oracle Database 19c DBA Performance Tuning & Management Training Course

Course Contents - DAY 4

Session 18: TUNE THE BUFFER CACHE

• • Overview of the Database Buffer Cache Architecture
• • Buffer Cache Tuning Goals and Techniques
• • Buffer Cache Performance Symptoms and Solutions
• • Buffer Cache Advisor
• • Database Smart Flash Cache
• • Full Database Caching
• • When to Flush the Buffer Cache

Session 19: TUNE PGA AND TEMPORARY SPACE

• • Overview of the PGA
• • SQL Memory Usage
• • Automatic PGA Memory Mode Configuration
• • Configure the PGA for a New Instance
• • Data Dictionary Views and PGA Management
• • PGA Target Advice Statistics and Histograms
• • Temporary Tablespace Management
• • Temporary Tablespace Group
• • Multiple Temporary Tablespaces
• • Monitoring Temporary Tablespaces
• • Temporary Tablespace Shrink
• • Data Dictionary Views and Sort Segments

Session 20: CREATE AND USE SNAPSHOTS AND BASELINES WITH THE AUTOMATIC WORKLOAD REPOSITORY

• • An Overview of In-Built Automatic Tuning Capabilities
• • An Overview of the Automatic Workload Repository
• • AWR Data
• • Create and Compare Snapshots
• • Examine AWR Reports
• • Static and Moving Window Baselines
• • Baseline Templates
• • AWR Reports
• • Monitor AWR using SQL Developer
• • Performance Hub Active Report

Session 21: USE AWR-BASED TOOLS

• • Automatic Maintenance Tasks
• • Maintenance Window Configuration
• • ADDM Performance Monitoring
• • ADDM Reports
• • Active Session History
• • Generate an ASH Report

Session 22: USE METRICS AND ALERTS

• • An Overview of Metrics and Alerts
• • The Benefits and Limitations of Metrics and Alerts
• • System Generated,Threshold Generated and Event Based Alerts
• • Set Thresholds
• • View Metric History Information
• • View Histograms
• • Metric and Alert Views

Oracle Database 19c DBA Performance Tuning & Management Training Course

Course Contents - DAY 5

Session 23: REAL TIME DATABASE OPERATION MONITORING

• • Overview of Real Time Database Operation Monitoring
• • Database Operation Concepts
• • Define a Database Operation
• • Enable the Monitoring of Database Operations
• • Identify,Start and Complete a Database Operation
• • Monitor the Progress of a Database Operation
• • Database Operation Views

Session 24: MONITOR APPLICATIONS

• • Aggregation of Services with Tracing
• • The DBMS_Monitor Package
• • Enable Tracing for a Client
• • Enable Session Tracing
• • Enable Tracing for a Module
• • Analyze Trace Results

Session 25: BIG DATA AND DATA WAREHOUSE FEATURES

• • Reduce Cursor Invalidations For DDLS
• • Automatic Indexing with DBMS_AUTO_INDEX
• • Real Time Statistics Gathering During DML Operations
• • High Frequency Statistics Gathering
• • Advanced Index Compression
• • Basic and Advanced Table Compression
• • Quarantine of Runaway SQL Statements
• • Bitmap-Based COUNT(DISTINCT) SQL Functions
• • Scaleable Sequences

Session 26: TUNE DATABASE I/O

• • An Overview of Database I/O Management
• • I/O Architecture and Modes
• • Important I/O Metrics for Oracle Databases
• • Layout Files using Operating System or Hardware Striping
• • Manually Distribute Files to Reduce I/O Contention
• • Sample Configurations
• • Asynchronous and Synchronous I/O
• • Multi-Threaded Oracle
• • Automatic Storage Management(ASM)Overview

Session 27: A SUMMARY OF ORACLE PERFORMANCE TUNING

• • The Potential Impact of Initialization Parameters on Performance
• • Initially Size Memory for a Database
• • Recommended Best Practices for Different Types of Tablespaces
• • Determine and Use Block Sizes
• • Size the Redo Log Buffer and the Redo Log Files
• • Configure Automatic Statistics Gathering