Course Outline
1. Overview
Objectives for government 1-2
What Is Performance Management? 1-3
Who Manages Performance? 1-4
What Does the DBA Tune? 1-5
Types of Tuning 1-6
Tuning Methodology 1-7
Effective Tuning Goals 1-9
General Tuning Session 1-11
Tuning a CDB 1-13
Performance Tuning: Diagnostics 1-14
Performance Tuning: Features and Tools 1-15
Tuning Objectives 1-16
Summary 1-17
Practice Overview 1-18
2. Defining the Scope of Performance Issues
Objectives 2-2
Defining the Problem 2-3
Limit the Scope 2-4
Determining Tuning Priorities 2-5
Common Tuning Problems 2-6
Tuning Life Cycle Phases 2-8
Tuning During the Life Cycle 2-9
Application Design and Development 2-10
Testing: Database Configuration 2-11
Deployment 2-12
Production 2-13
Migration, Upgrade, and Environment Changes 2-14
ADDM Tuning Session 2-15
Performance Versus Business Requirements 2-16
Monitoring and Tuning Tools: Overview 2-17
Summary 2-19
Practice Overview 2-20
3. Using the Time Model to Diagnose Performance Issues
Objectives 3-2
Time Model: Overview 3-3
DB Time 3-4
CPU and Wait Time Tuning Dimensions 3-5
Time Model Statistics Hierarchy 3-6
Time Model: Example 3-8
Top Timed Events 3-9
Summary 3-10
Practice Overview 3-11
4. Using Statistics and Wait Events to Diagnose Performance Issues
Objectives 4-2
Dynamic Performance Views 4-3
Dynamic Performance Views: Usage Examples 4-4
Dynamic Performance Views: Considerations 4-5
Statistic Levels 4-6
Instance Activity and Wait Event Statistics 4-8
System Statistic Classes 4-9
Displaying Statistics 4-10
Displaying SGA Statistics 4-11
Wait Events 4-12
Using the V$EVENT_NAME View 4-13
Wait Classes 4-14
Displaying Wait Event Statistics 4-15
Commonly Observed Wait Events 4-17
Using the V$SESSION_WAIT View 4-18
Precision of System Statistics 4-19
Summary 4-20
Practice Overview 4-21
5. Using Log and Trace Files to Monitor Performance
Objectives 5-2
Viewing the Alert Log 5-3
Using Alert Log Information as an Aid in Managing Performance 5-5
Administering the DDL Log File 5-6
Understanding the Debug Log File 5-7
User Trace Files 5-8
Background Processes Trace Files 5-9
Summary 5-10
Practice Overview 5-11
6. Using Enterprise Manager Cloud Control and SQL Developer to Monitor Performance
Objectives 6-2
Enterprise Manager: Overview 6-3
Configuring Enterprise Manager Database Express 6-4
Oracle Enterprise Manager Cloud Control Components 6-5
Using Features of the Oracle Management Packs and Options 6-6
Oracle SQL Developer 6-7
SQL Developer Command Line (SQLcl) 6-8
Summary 6-9
Practice Overview 6-10
7. Using Statspack to View Performance Data
Objectives 7-2
Introduction to Statspack 7-3
Statspack Scripts 7-4
Installing Statspack 7-6
Capturing Statspack Snapshots 7-7
Configuring Snapshot Data Capture 7-8
Statspack Snapshot Levels 7-9
Statspack Baselines and Purging 7-11
Reporting with Statspack 7-12
Statspack Considerations 7-13
Statspack Reports 7-14
Reading a Statspack Report 7-15
Statspack Report Drilldown Sections 7-16
Report Drilldown Examples 7-18
Load Profile Section 7-19
Time Model Section 7-20
Statspack and AWR 7-21
Summary 7-22
Practice Overview 7-23
8. Using Automatic Workload Repository
Objectives 8-2
Automatic Workload Repository: Overview 8-3
Automatic Workload Repository Data 8-4
Workload Repository 8-5
AWR Administration 8-6
AWR Snapshot Purging Policy 8-7
Managing Snapshots with PL/SQL 8-8
AWR Snapshot Settings 8-9
Manual AWR Snapshots 8-10
Managing AWR Data in a Multitenant Environment 8-11
AWR Snapshots and ADDM in a Multitenant Architecture Database 8-12
Generating AWR Reports 8-13
Reading the AWR Report 8-14
AWR Report: Multitenant Data 8-15
Generating AWR Reports by Using SQL*Plus 8-16
Statspack and AWR Reports 8-17
Reading a Statspack or an AWR Report 8-18
Compare Periods: Benefits 8-19
Snapshots and Periods Comparisons 8-20
Compare Periods: Results 8-21
Compare Periods: Report 8-22
Multitenant AWR Views 8-23
Summary 8-24
Practice Overview 8-25
9. Using Metrics and Alerts
Objectives 9-2
Metrics and Alerts 9-3
Limitation of Base Statistics 9-4
Typical Delta Tools 9-5
Oracle Database Metrics 9-6
Benefits of Metrics 9-7
Viewing Metric History Information 9-8
Viewing Metric Details 9-9
Statistic Histograms 9-10
Histogram Views 9-11
Server-Generated Alerts 9-12
Alert Usage Model 9-13
Metric and Alert Views 9-14
Summary 9-15
Practice Overview 9-16
10. Using Baselines
Objectives 10-2
Comparative Performance Analysis with AWR Baselines 10-3
Automatic Workload Repository Baselines 10-4
AWR Baselines 10-5
Types of Baselines 10-6
Moving Window Baseline 10-7
Baselines in Performance Page Settings 10-8
Baseline Templates 10-9
Creating AWR Baselines 10-10
Creating a Single AWR Baseline 10-11
Creating a Repeating Baseline and Template 10-12
Managing Baselines by Using the DBMS_WORKLOAD_REPOSITORY Package 10-13
Generating a Baseline Template for a Single Time Period 10-14
Creating a Repeating Baseline Template 10-15
Baseline Views 10-16
Performance Monitoring and Baselines 10-17
Defining Alert Thresholds by Using a Static Baseline 10-19
Configuring a Basic Set of Thresholds 10-20
Summary 10-21
Practice Overview: Using AWR Baselines 10-22
11. Managing Automated Maintenance Tasks
Objectives 11-2
Automated Maintenance Tasks 11-3
Maintenance Windows 11-4
Default Maintenance Plan 11-5
Automated Maintenance Task Priorities 11-6
Configuring Automated Maintenance Tasks 11-7
Summary 11-8
Practice Overview 11-9
12. Using ADDM to Analyze Performance
Objectives 12-2
ADDM Performance Monitoring 12-3
ADDM and Database Time 12-4
DB Time-Graph and ADDM Methodology 12-5
Top Performance Issues Detected 12-7
ADDM Recommendations 12-8
Creating a Manual ADDM Task 12-9
ADDM Tasks in a Multitenant Architecture Database 12-10
Changing ADDM Attributes 12-11
Retrieving ADDM Reports by Using SQL 12-12
Compare Periods ADDM: Analysis 12-13
Workload Compatibility 12-14
Configuring Automatic ADDM Analysis at the PDB Level 12-15
Using the DBMS_ADDM Package to Compare Periods 12-16
Example: Using the DBMS_ADDM Package to Compare Periods 12-17
Summary 12-18
Practice Overview 12-19
13. Using Active Session History Data for First Fault System Analysis
Objectives 13-2
Active Session History: Overview 13-3
Active Session History: Mechanics 13-4
ASH Sampling: Example 13-5
Accessing ASH Data 13-6
Analyzing the ASH Data 13-7
Using Enterprise Manager to Generate ASH Reports 13-8
Using the ASH Report Script to Generate a Report 13-9
ASH Report Structure 13-10
Determining the Source of Data 13-11
Performing Skew Analysis 13-12
Additional Automatic Workload Repository Views 13-13
Summary 13-14
Practice Overview 13-15
14. Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues
Objectives 14-2
Emergency Monitoring: Challenges 14-3
Emergency Monitoring: Goals 14-4
Using Real-Time ADDM to Perform a Root-Cause Analysis 14-5
Using Real-Time ADDM 14-6
Real-Time ADDM in the Database 14-7
Using Real-Time ADDM 14-9
Viewing Real-Time ADDM Results 14-10
Summary 14-11
15. Overview of SQL Statement Processing
Objectives 15-2
SQL Statement Processing Phases 15-3
Parsing 15-4
SQL Cursor Storage 15-5
Session Cursor Cache 15-6
Cursor Usage and Parsing 15-7
SQL Statement Processing Phases: Bind 15-8
SQL Statement Processing Phases: Execute and Fetch 15-9
Processing a DML Statement 15-10
Commit Processing 15-12
Identifying Poorly Performing SQL Statements 15-13
Top SQL Reports 15-14
SQL Monitoring 15-15
Monitored SQL Execution Details 15-16
Summary 15-17
16. Maintaining Indexes
Objectives 16-2
Creating Indexes 16-3
Using Invisible and Unusable Indexes 16-4
Dropping Indexes 16-5
Reducing the Cost of SQL Operations 16-6
Index Maintenance 16-7
Using Advanced Index Compression 16-9
Other Index Options 16-10
SQL Access Advisor 16-11
Quiz 16-12
Automatic Indexing Task 16-13
Automatic Index Task Workflow 16-15
Automatic Indexing Task Reporting 16-16
Automatic Indexing Views 16-17
Summary 16-18
Practice Overview 16-19
17. Maintaining Tables
Objectives 17-2
Reducing the Cost of SQL Operations 17-3
Table Maintenance for Performance 17-4
Table Reorganization Methods 17-5
Space Management 17-6
Extent Management 17-7
Locally Managed Extents 17-8
Large Extents: Considerations 17-9
How Table Data Is Stored 17-11
Anatomy of a Database Block 17-12
Minimize Block Visits 17-13
Block Allocation 17-14
Free Lists 17-15
Block Space Management 17-16
Block Space Management with Free Lists 17-17
Automatic Segment Space Management 17-19
Automatic Segment Space Management at Work 17-20
Block Space Management with ASSM 17-21
Creating an Automatic Segment Space Management Segment 17-22
Migration and Chaining 17-23
Guidelines for PCTFREE and PCTUSED 17-25
Detecting Migration and Chaining 17-26
Selecting Migrated Rows 17-27
Eliminating Migrated Rows 17-28
Shrinking Segments: Overview 17-30
Shrinking Segments: Considerations 17-31
Shrinking Segments by Using SQL 17-32
Segment Shrink: Basic Execution 17-33
Segment Shrink: Execution Considerations 17-34
Data Compression 17-35
Advanced Row Compression: Overview 17-37
Advanced Row Compression: Concepts 17-38
Using Advanced Row Compression 17-39
Advanced Row Compression for DML Operations 17-40
Advanced Index Compression 17-41
How Does Hybrid Columnar Compression Work? 17-42
Using the Compression Advisor 17-43
Using the Compression Advisor for Indexes 17-44
Viewing Table Compression Information 17-45
Quiz 17-46
Summary 17-47
Practice Overview 17-48
18. Introduction to Query Optimizer
Objectives 18-2
Role of the Oracle Optimizer 18-3
Functions of the Query Optimizer 18-5
Selectivity 18-7
Cardinality and Cost 18-8
Changing Optimizer Behavior 18-9
Setting and Viewing Optimizer Parameters 18-10
Using Initialization Parameters to Control Optimizer Behavior 18-11
Enabling Query Optimizer Features 18-13
Influencing the Optimizer Approach 18-14
Optimizing SQL Statements 18-15
Access Paths 18-16
Choosing an Access Path 18-17
Summary 18-18
19. Understanding Execution Plans
Objectives 19-2
What Is an Execution Plan? 19-3
Methods for Viewing Execution Plans 19-4
Uses of Execution Plans 19-5
DBMS_XPLAN Package: Overview 19-6
EXPLAIN PLAN Command 19-8
EXPLAIN PLAN Command: Example 19-9
EXPLAIN PLAN Command: Output 19-10
Reading an Execution Plan 19-11
Using the V$SQL_PLAN View 19-12
Querying V$SQL_PLAN 19-13
V$SQL_PLAN_STATISTICS View 19-14
Querying the AWR 19-15
SQL*Plus AUTOTRACE 19-16
Using SQL*Plus AUTOTRACE 19-17
SQL*Plus AUTOTRACE: Statistics 19-18
Quiz 19-19
Adaptive Execution Plans 19-20
Dynamic Plans 19-21
Dynamic Plan: Adaptive Process 19-22
Dynamic Plans: Example 19-23
Continuous Adaptive Query Plans 19-24
Automatic Re-Optimization 19-25
Comparing Execution Plans 19-26
Summary 19-27
Practice Overview 19-28
20. Viewing Execution Plans by Using SQL Trace and TKPROF
Objectives 20-2
SQL Trace Facility 20-3
How to Use the SQL Trace Facility 20-5
Initialization Parameters 20-6
Enabling SQL Trace 20-8
Disabling SQL Trace 20-9
Formatting Your Trace Files 20-10
TKPROF Command Options 20-11
Output of the TKPROF Command 20-13
TKPROF Output with No Index: Example 20-18
TKPROF Output with Index: Example 20-19
Generating an Optimizer Trace 20-20
Summary 20-21
Practice Overview 20-22
21. Managing Optimizer Statistics
Objectives 21-2
Optimizer Statistics 21-3
Types of Optimizer Statistics 21-4
Optimizer Statistics Collection 21-5
Dynamic Statistics 21-7
Gathering Statistics and Setting Optimizer Statistics Preferences 21-8
Setting Statistic Preferences 21-9
Viewing and Managing Optimizer Statistics Preferences 21-11
Extended Statistics 21-12
Maintaining Optimizer Statistics 21-13
Automated Maintenance Tasks 21-14
Optimizer Statistics Advisor 21-15
Optimizer Statistics Advisor Report 21-16
Executing Optimizer Statistics Advisor Tasks 21-17
Restoring Statistics 21-18
Deferred Statistics Publishing: Overview 21-19
Deferred Statistics Publishing: Example 21-21
Managing Real-Time Statistics 21-22
Configuring High-Frequency Automatic Optimizer Statistics Collection 21-23
Summary 21-24
Practice Overview 21-25
22. Using Automatic SQL Tuning
Objectives 22-2
Automatic SQL Tuning: Overview 22-3
SQL Statement Profiling 22-4
Plan Tuning Flow and SQL Profile Creation 22-5
SQL Tuning Loop 22-6
Using SQL Profiles 22-7
Summary 22-8
23. Using the In-Memory Column Store Feature to Improve SQL Performance
Objectives 40-2
Query Benefits 40-3
Testing and Comparing Query Performance 40-4
Queries on In-Memory Tables: Simple Predicate 40-5
MINMAX Pruning Statistics 40-6
IM Column Store Statistics 40-7
Execution Plan: TABLE ACCESS IN MEMORY FULL 40-8
Queries on In-Memory Tables: Join 40-9
Execution Plan: JOIN FILTER CREATE / USE 40-10
Queries on In-Memory Tables: Join Groups 40-11
Population of Expressions and Virtual Columns Results 40-12
In-Memory Expression Unit (IMEU) 40-14
Populating In-Memory Expression Results 40-15
Populating In-Memory Expression Results Within a Window 40-17
Waiting for In-Memory Segments to be Populated 40-18
Views 40-19
Summary 40-20
Practice Overview 40-21
24. Using In-Memory Column Store with Oracle Database Features
Objectives 41-2
Interaction with Other Products 41-3
Optimizer 41-4
IM Column Store and Real Application Clusters 41-6
IM Column Store and Data Pump 41-7
Data Pump TRANSFORM Names 41-8
Automatic Data Optimization Interaction 41-9
Managing Heat Map and Automatic Data Optimization Policies 41-10
Creating ADO In-Memory Policies 41-12
Summary 41-13
Practice Overview 41-14
Requirements
- A comprehensive understanding of Oracle Database architecture
- Practical experience with SQL and PL/SQL
- Familiarity with Oracle Database administration
Audience
- Database administrators for government
- IT professionals responsible for database performance in the public sector
- Developers working with Oracle Database in government environments
