Oracle Database 19c DBA Performance Tuning & Management

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

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

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

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

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