Oracle Database 12c DBA Performance Tuning and Management

Follow-On Courses

• Oracle Database 12c Backup & Recovery with RMAN
• Oracle Database 12c Data Guard

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

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

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
• Online Statistics Gathering for Bulk Loads

Sorts

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

Indexes

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

Advanced Indexes

• Bitmap Indexes
• Key Compressed Indexes
• Index Organized Tables
• Function Based Indexes
• Invisible Indexes

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

SQL Tuning Advisor Using SQL Developer

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

Sequences and Views

• Sequence Caching
• Views
• View Merging
• Inline Views

Using Hints

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

Miscellaneous

• Tips for Avoiding Problematic Queries
• Array Size
• The Shared Pool
• The WITH Clause
• Bind Variable Usage
• Result Caching
• Some Miscellaneous PL/SQL Issues

Basic Tuning Diagnostics

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

Reduce the Cost of SQL Operations

• Maintain Tables
• Reorganize Tables
• Manage Extents
• Compress Tables
• Row Chaining and Row Migration
• Segment Shrink

The SQL Performance Analyzer

• An Overview of the SQL Performance Analyzer
• Usage of the SQL Performance Analyzer
• Capture a SQL Workload
• Create a SQL Performance Analyzer Task
• Generate Comparison Reports
• Tune Regressed Statements
• Guided Workflow Analysis
• SQL Performance Analyzer Views

SQL Performance Management

• Maintenance of the Optimizer Statistics
• Gather Statistics Options
• Defer Publishing Statistics
• Automatic SQL Tuning
• The SQL Tuning Advisor
• The SQL Access Advisor
• SQL Plan Management

Tune the Shared Pool

• Overview of the Shared Pool Architecture
• Latches and Mutexes
• Diagnostic Tools for Tuning the Shared Pool
• Avoiding Hard Parses
• Size the Shared Pool
• Avoiding Fragmentation of the Shared Pool
• The Data Dictionary Cache
• The SQL Query Result Cache

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

Tune PGA and Temporary Space

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

Automatic Memory Management

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

Create and Use Snapshots and Baselines with the Automatic Workload Repository

• An Overview of the Automatic Workload Repository
• AWR Data
• Enterprise Manager and the AWR
• Create and Compare Snapshots
• Examine AWR Reports
• Create and Compare Baselines
• Moving Window Baseline
• Baseline Templates
• Baselines Views
• Performance Monitoring and Baselines
• Define Alert Thresholds Using a Static Baseline
• Configure Adaptive Thresholds

Define the Scope of Performance Issues

• An Overview of Monitoring and Tuning Tools
• Common Tuning Problems
• Define the Problem and Limiting the Scope
• Set the Priority of a Performance Issue
• View the Top SQL Reports
• Tune During the Life Cycle of a problem
• Generate and Analyze ADDM Reports
• Performance Tuning Resources

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

Use AWR Based Tools

• Automatic Maintenance Tasks
• ADDM Performance Monitoring
• Active Session History
• Generate an ASH Report
• Real-time ADDM

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
• Database Operation Tuning

Monitor Applications

• The Attributes and Types of Services
• Create Services
• Use Services with Client Applications
• Use Services with the Resource Manager
• Use Services the Oracle Scheduler
• Services and Metric Thresholds
• Use Aggregation of Services with Tracing
• The Top Services Performance Page

Database Replay

• Overview of Database Replay Architecture
• Use Enterprise Manager to Capture and Replay a Workload
• Use the Database Replay Packages to Capture and Replay a Workload
• Capture a Workload
• Pre-process the Captured Workload
• Configure Replay Clients
• Replay the Workload
• Replay Workload Options
• Analyze the Replayed Workload
• Database Replay Views

Tune Database I/O

• An Overview of Database I/O Management
• I/O Architecture and Modes
• Important I/O Metrics for Oracle Databases
• I/O Calibration
• I/O Statistics and Diagnostics
• Database I/O Tuning
• Automatic Storage Management (ASM)

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