Oracle Database 19c: Performance Management and Tuning - course RUB 76,490. from Specialist, training, Date: May 14, 2023.
Miscellaneous / / December 04, 2023
You will become familiar with Oracle tuning methodology and learn basic tools for monitoring and diagnosing SQL and database instance performance problems. Learn to diagnose and tune database components. Learn how to proactively manage with built-in performance analytics and tools. Each course topic is necessarily reinforced with workshops based on real work situations. After completing the training, you will immediately be able to apply the acquired skills in business.
The course is suitable for versions of Oracle Database 12c, 18c, 19c and is intended for technical support engineers, technical administrators, data warehouse administrators familiar with the basics of working with Oracle.
Want to gain comprehensive knowledge about managing and tuning Oracle Database 19c performance? We recommend this course to you!
You will learn:
use the full range of Oracle tools to identify SQL and database instance performance problems;
use SQL Performance Analyzer;
use Enterprise Manager Cloud Control to monitor instance performance;
monitor databases and applications in real time;
address key lifecycle performance tuning issues;
use database consultants when diagnosing performance problems;
use metrics and alerts;
configure the performance of a database instance;
manage SQL performance;
Enable or disable automatic system global scope management.
6
coursesPractical teacher, experienced Oracle DBMS specialist. He knows how to convey his wealth of experience to every listener and do it in simple and accessible language, using many practical examples. Grateful listeners note his ability to show a wide variety of nuances of working with the Oracle DBMS.
Mikhail Fokin has ten years of experience working with Oracle DBMS (versions 8, 8i, 9i, 10g, 11g), including administration experience and performance settings of the Oracle DBMS on Unix and Windows platforms, including in the Real Application configuration Clusters. Professionally fluent in SQL, PL/SQL languages. Fluent in database backup and recovery technologies - both using RMAN and manually (user managed backup and recovery). Provides reliable data storage and fault tolerance using Data Guard‚ RAC technologies. Resolves database performance issues and also performs tuning of SQL commands and PL/SQL code. In addition, he has seven years of experience teaching courses on Oracle DBMS administration, performance tuning, security, application development and reporting using Oracle Forms Developer and Oracle Reports.
Currently, he is an Oracle DBMS administrator at a large Russian bank.
Module 1. Performance Tuning Basics (2 ak. h.)
Performance Tuning Diagnostics, Features, and Tools
Database time
CPU and Wait Time Tuning Dimensions
Temporary model
Dynamic Performance Views
Cumulative System Statistics
Wait Events
Log and trace files
Module 2. Using Automatic Workload Repository (2 ac. h.)
Automatic Workload Repository Overview
Automatic Workload Repository data
Enterprise Manager Cloud Control and AWR
Pictures
Reports
Comparison of reports
Module 3. Identification of performance problems (2 ac. h.)
Identifying Performance Issues
Setting Priorities
Reports on the SQL statements that consumed the maximum amount of resources
Basic Performance Tuning Issues
Lifecycle performance tuning
Configuration session using ADDM
Performance Tuning Resources
Overview of monitoring and configuration tools
Module 4. Using metrics and alerts (2 ak. h.)
Overview of metrics and alerts
Limitations of Basic Statistics
Advantages of metrics
View the history of metric values
View histograms
Server-generated warnings
Setting thresholds
Dynamic views for metrics and alerts
Module 5. Basic ranges (2 ac. h.)
Working using a basic range of metrics
Basic 'sliding window' ranges
Base Range Templates
Creating Basic Ranges
Dynamic views for base ranges
Performance Monitoring and Baseline Ranges
Configuring warning thresholds using static base ranges
Adaptive Threshold Configuration
Module 6. Using tools based on AWR (2 ac. h.)
Automatic tracking tasks
Performance Monitoring with ADDM
Active session history
Module 7. Real-time database monitoring (2 ac. h.)
Overview and Intended Use
Defining Database Operations
Database Operations Concepts
Enable Database Operation Monitoring
Identifying, starting, and ending a database operation
Monitoring the progress of a database operation
Dynamic views of database operations
Setting Up Database Operations
Module 8. Application monitoring (2 ac. h.)
Attributes and types of services
Creation of services
Using services for client applications
Services and resource manager
Services and batch scheduler
Services and Metric Thresholds
Aggregation and tracing of services
Module 9. Identification of problematic SQL statements (2 ac. h.)
Steps to Execute SQL Commands
SQL Monitoring
Execution Plans
DBMS_XPLAN package
EXPLAIN PLAN command
Execution Plan Interpretation
Using SQL Trace
Generating an optimizer trace
Module 10. Impact on the optimizer (2 ac. h.)
Query Optimizer Tasks
Managing optimizer statistics
Initialization parameters that affect optimizer behavior
Enabling optimizer features
Using optimizer hints
Data Access Paths
Connection Operations
Sorting Operations
Module 11. Reducing the price of SQL statements (2 ac. h.)
Index maintenance
Access Path Optimization Consultant (SQL Access Advisor)
Maintenance and reorganization of tables
Extent Management
Data storage
Migrated and concatenated strings
Reducing segments
Table compression
Module 12. Using SQL Performance Analyzer (2 ac. h.)
SQL Performance Analyzer Overview
Overview and Intended Use of Real Application Testing
Load Capture
Create a SQL Performance Analyzer task
Comparative reports
Setting up regressed statements
SQL Performance Analyzer Dynamic Views
Module 13. SQL Performance Management (2 ac. h.)
Maintenance of optimizer statistics
Automated maintenance tasks
Statistics collection settings
Deferred statistics collection
Automatic SQL Tuning
SQL Tuning Advisor
Access Path Optimization Consultant (SQL Access Advisor)
Execution Plan Management
Module 14. Using Database Replay (2 ac. h.)
Database Replay Architecture
Load Capture Features
Playback options
Playback Analysis
Database Replay in Enterprise Manager
Database Replay Packages and Procedures
Dynamic views for load reproduction
Load Replay Client Calibration
Module 15. Setting up a shared pool (2 ac. h.)
Shared Pool Architecture
Latches and mutexes
Shared Pool Diagnostic Tools
Soft and hard analyzes
Changing the shared pool size
Shared pool fragmentation
Data dictionary cache
Query result cache
Module 16. Configuring the buffer cache (2 ak. h.)
Buffer Cache Architecture
Work sets
Goals and techniques for tuning buffer cache performance
Symptoms of buffer cache performance problems
Solutions to Buffer Cache Performance Issues
Database Smart Flash Cache
Resetting the buffer cache
Module 17. Configuring PGA and temporary table space (2 ac. h.)
Auto PGA Allocation Configuration
PGA automatic distribution histograms and statistics
Part of the AWR report regarding automatic PGA allocation
Managing temporary tablespaces
Temporary table space groups
Monitoring temporary table spaces
Reducing the size of a temporary table space
Module 18. Automatic memory management (2 ac. h.)
Dynamic system global scope
Overview of automatic system global scope management
Initialization parameters affecting SGA size
Enable or disable automatic system global scope management
System Global Area Sizing Consultant
Automatic Memory Management Overview
Enable automatic memory management
Monitoring automatic memory management
Module 19. I/O setup (2 ac. h.)
I/O architecture
I/O Modes
Database-Relevant I/O Metrics
I/O Calibration
I/O Statistics
I/O Diagnostics
Configuring Database I/O
Automatic Storage Management (ASM)
Module 20. Results of performance tuning (2 ac. h.)
Initialization parameters and their impact on performance
Initial sizes of memory structures
Setting up a large pool
Best practices for different types of tablespaces
Block Dimensions
Configuring the size of the online log buffer and online log files
Automatic statistics collection
Most commonly observed wait events