Course Details
Duration: 5 Days; Instructor-led
Audience
- Administrator
- Database Administrator
Prerequisites
- Oracle Database 12c: Install and Upgrade Workshop Ed 1
Methodology
This program will be conducted with interactive lectures, PowerPoint presentation, discussion and practical exercise.
Course Objectives
In this course, you will learn to:
- Describe Performance Management
- Use AWR Performance Statistics
- Use AWR Based Tools
- Improve SQL Statement Performance
- Influence the Optimizer.
- Use Tuning Analysers and Advisors
- Monitor Operations and applications
- Manage Memory
- Manage Database Memory
- Use In-Memory Column Store Feature
Outlines
Module 1: Overview
- Overview
- Practice 1-1: Preparing the databases
Module 2: Defining the Scope of Performance Issues
- Defining the scope of performances issues objectives
- Tuning life cycle phases
- Practice 2-1: using enterprise manager to identify OS issues.
Module 3: Using the time model to diagnose performance issues.
- Using the time model to diagnose performance issues.
- Practice 3-1: Viewing the top wait events and the time model.
Module 4: Using statistics and wait events to diagnose performance Issues.
- Using statistics and wait events to diagnose performance issues.
- Insurance activity and wait event statistics.
- Practice 4-1: viewing system statistics and wait events.
Module 5: Using log and trace files to monitor performance.
- Using log and trace files to monitor performance.
- Practice 5-1: viewing performance information in the alert log.
Module 6: Using enterprise manager cloud control and SQL developer to monitor performance.
- Using enterprise manager cloud control and SQL developer to monitor performance.
- Practice 6-1: using enterprise manager to monitor performance.
- Practice 6-2: using SQL developer to monitor performance part 1
- Practice 6-2: using SQL developer to monitor performance part 2
Module 7: Using statspack to view performance data.
- Using statspack to view performance data.
- Practice 7-1: installing statspack.
- Practice 7-2: creating snapshots.
- Practice 7-3: generating statspack reports.
- Practice 7-4: using statspack to examine segment statistics (optional).
Module 8: Using automatic workload repository.
- Using automatic workload repository
- Managing AWR data in a multitenant environment
- Practice 1-1: creating and managing AWR snapshots.
- Practice 1-2: generating and viewing and AWR report.
- Practice 1-3: generating and viewing a compare period report.
Module 9: Using Metrics and Alerts
- Using metrics and alerts
- Practice 2-1: setting up and viewing server-generated alerts.
Module 10: Using Baselines
- Using Baselines
Module 11: Managing Automated Maintenance Tasks
- Managing Automated Maintenance Tasks
- Practice 1-1: Configuring Automatic Maintenance Tasks (Part 1)
- Practice 1-1: Configuring Automatic Maintenance Tasks (Part 2)
Module 12: Using ADDM to Analyze Performance
- Using ADDM to Analyze Performance
- Compare Periods ADDM: Analysis
- Practice 2-1: Using AWR-Based Tools to Identify Performance Issues
- Practice 2-2: Performing an ADDM Analysis of a PDB
Module 13: Using Active Session History Data for First Fault System Analysis
- Using Active Session History Data for First Fault System Analysis
- Practice 3-1: Generating and Reviewing an ASH Report to Identify Performance Issues
Module 14: Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues
- Using Emergency Monitoring and Real-Time ADDM to Analyze Performance
Module 15: Overview of SQL Statement Processing
- Overview
- Processing a DML Statement
Module 16: Maintaining Indexes
- Maintaining Indexes
- Automatic Indexing Task
- Practice 2-1: Coalescing an Index
Module 17: Maintaining Tables
- Maintaining Tables
- Block Space Management with Free Lists
- Shrinking Segments: Overview
- Advanced Index Compression
- Practice 3-1: Analyzing the Impact of Excess Blocks
Module 18: Introduction to Query Optimizer
- Introduction
- Using Initialization Parameters to Control Optimizer Behavior
Module 19: Understanding Execution Plans
- Understanding Execution Plans
- SQL*Plus AUTOTRACE
- Practice 2-1: Using AUTOTRACE and EXPLAIN PLAN
Module 20: Viewing Execution Plans by Using SQL Trace and TKPROF
- Viewing Execution Plans by Using SQL Trace and TKPROF
- Practice 3-1: Using SQL TRACE and the TKPROF Utility
Module 21: Managing Optimizer Statistics
- Managing Optimizer Statistics
- Extended Statistics
- Practice 4-1: Capturing Extended Statistics
- Practice 4-2: Determining the Impact of Stale Statistics
- Practice 4-3: Using the Optimizer Statistics Advisor to Improve Statistics Collection Quality
Module 22: Using Automatic SQL Tuning
- Using Automatic SQL Tuning
Module 23: Using the SQL Plan Management Feature
- Using the SQL Plan Management Feature
- Practice 6-1: Using SQL Plan Management (Part 1)
- Practice 6-1: Using SQL Plan Management (Part 2)
Module 24: Overview of the SQL Advisors
- Overview of the SQL Advisors
Module 25: Using the SQL Tuning Advisor
- Using the SQL Tuning Advisor
- Practice 2-1: Using the SQL Tuning Advisor
Module 26: Using the SQL Access Advisor
- Using the SQL Access Advisor
- Practice 3-1: Using the SQL Access Advisor to Improve SQL Performance
Module 27: Overview of Real Application Testing Components
- Overview of Real Application Testing Components
Module 28: Using SQL Performance Analyzer to Determine the Impact of Changes
- Using SQL Performance Analyzer to Determine the Impact of Changes
- Practice 2-1: Using SQL Performance Analyzer (Part 1)
- Practice 2-1: Using SQL Performance Analyzer (Part 2)
- Practice 2-2: Seeding SQL Plan Baselines from SQL Performance Analyzer
Module 29: Using Database Replay to Test System Performance
- Using Database Replay to Test System Performance
- Database Replay Packages
- Practice 3-1: Configuring and Using Database Replay at the PDB Level
Module 30: Implementing Real-Time Database Operation Monitoring
- Implementing Real-Time Database Operation Monitoring
- Practice 1-1: Monitoring a Composite Database Operation
- Practice 1-2: Monitoring a PL/SQL Operation
Module 31: Using Services to Monitor Applications
- Using Services to Monitor Applications
- Creating Services
- Service Aggregation Configuration
- Practice 2-1: Using Services in a Single-Instance Oracle Database (Part 1)
- Practice 2-1: Using Services in a Single-Instance Oracle Database (Part 2)
- Practice 2-2: Tracing Services in a Single-Instance Environment
Module 32: Overview of Memory Structures Overview
- Practices for Lesson 1: Overview
Module 33: Managing Shared Pool Performance
- Managing Shared Pool Performance
- Avoid Hard Parses
- Sizing the Shared Pool
- Practice 2-1: Sizing the Shared Pool
- Practice 2-2: Tuning a Hard-Parse Workload
- Practice 2-3: Tuning a Soft-Parse Workload
- Practice 2-4: Keeping Objects in the Shared Pool
Module 34: Managing Buffer Cache Performance
- Managing Buffer Cache Performance
- Buffer Cache Hit Ratio
- Caching Tables
- Multiple Block Sizes
- Practice 3-1: Sizing the Buffer Cache
- Practice 3-2: Using the Keep Pool
- Practice 3-3: Using Force Full Database Caching
Module 35: Managing PGA and Temporary Space Performance
- Managing PGA and Temporary Space Performance
- Monitoring SQL Memory Usage
- Practice 4-1: Adjusting the Value of PGA_AGGREGATE_TARGET
Module 36: Configuring the Large Pool
- Configuring the Large Pool
Module 37: Using Automatic Shared Memory Management
- Using Automatic Shared Memory Management
- Using the V$SYSTEM_PARAMETER View
- Practice 6-1: Enabling Automatic Shared Memory Management
Module 39: Configuring the In-Memory Column Store Feature
- Configuring the In-Memory Column Store Feature
- Practice 2-1: Configuring In-Memory Column Store
- Practice 2-2: Configuring In-Memory Objects
- Practice 2-3: Querying In-Memory Objects and Viewing Execution Plans
Module 40: Using In-Memory Column Store with Oracle Database Features
- Using In-Memory Column Store with Oracle Database Features
- Practice 4-1: Exporting and Importing In-Memory Objects
