fr
en
Oracle Database 19c: performance tuning
Database slowdowns affect applications and their users. With Oracle, structure your performance analysis and examine tuning options. Develop a diagnostic approach to support your decisions and verify their effects.
- Duration
- 3 days 21 hours
- Code
- ORA19-OPTI Code
Presentation
Among leading database management systems (DBMS), Oracle Database 19c is recognised for its performance, scalability and security, both on-premises and in the cloud. It is particularly effective at managing very large data volumes. This technical performance is the result of extensive research and Oracle's expertise. However, knowing how to tune the system is essential to getting the most from Oracle Database 19c.
Whether you are a database administrator or a developer, this course is designed for you. Over 3 days, you will learn to optimise Oracle Database 19c. You will explore database and server tuning, learn how to optimise SQL queries, and improve storage and diagnostic processes. You will also learn to use performance measurement tools effectively.
Objectives
By the end of this Oracle Database 19c performance tuning course, you will be able to:
- Understand and apply tuning settings to improve SQL query, database, memory, Oracle server and storage performance.
- Understand automatic performance tuning features.
- Use Oracle Database tuning tools.
- Use monitoring tools to manage Oracle Database 19c performance.
Program
Oracle 19c tuning principles
- Fundamental performance tuning procedures.
- The importance of following the different tuning procedures.
- Desired and acceptable performance targets.
Tuning Oracle 19c database memory
- Configuring the System Global Area (SGA).
- Optimising data cache performance.
- Managing memory data block sizes.
- Tuning and sizing the System Global Area (SGA).
- Optimising other caches.
- Implementing Automatic Shared Memory Management (ASMM).
- Configuring the Program Global Area (PGA).
- Implementing automatic SGA and PGA management.
SQL query processing with Oracle 19c
- How the shared SQL area works.
- Query processing stages.
- Using V$SQLAREA to monitor query performance.
- Query types.
Using performance measurement tools
- Using EXPLAIN PLAN to create an execution plan.
- Using tracing in the server process.
- Applying a tuning strategy.
- Configuring session autotrace, SQL Developer and Database Control for execution plans.
- Recording execution plans and reads.
- Types of execution plan.
- Configuring session autotrace, SQL Developer and Database Control for statistics.
- Tracing an SQL query.
- Analysing the current session and other instances.
- Using tracing with tkprof.
Understanding automatic performance tuning features
- Introduction to Automatic Workload Repository (AWR) reporting.
- Introduction to Automatic Database Diagnostic Monitor (ADDM) performance analysis.
- Using the DBMS_ADVISOR package.
- Understanding SQL Access Advisor and SQL Profile.
Optimising the Oracle 19c relational database model
- Creating and using a B-tree search structure.
- Using function-based and bitmap indexes.
- Using clustered storage: indexed or hash clusters.
- Using index-organised tables (IOT).
- Partitioning tables and indexes.
Tuning the Oracle 19c server
- Introduction to Oracle Optimizer.
- Selecting an access path for optimisation.
- Calculating selectivity.
- Collecting statistics manually with DBMS_STATS.
- Collecting statistics automatically.
- Introduction to hash joins.
Optimising SQL queries
- Defining a tuning strategy.
- Generating SQL queries.
- Tuning SQL queries manually.
- Proposing optimisation ideas.
- Displaying the data processing structure.
- Using in-memory processing.
Tuning the Oracle 19c database
- Using the dbms_advisor package and UNDO tablespace.
- Creating temporary tables.
- Optimising logging and log file sizes.
Tuning Oracle 19c database storage
- Managing space in tables.
- Reorganising segments.
- Using the dbms_compression package.
Audience
This course is intended for:
- Database administrators, system administrators and developers experienced in using an Oracle database management system.
Prerequisites
To attend this Oracle Database 19c performance tuning course, you should:
- Have solid practical experience administering Oracle Database 12c and Oracle Database 18c.
- Have a good command of English and IT terminology.
Teaching and assessment methods
- Initial skills assessment
- Training materials provided to participants
- Continuous assessment throughout the course
- End-of-course feedback questionnaire
- Combination of theory and practical application
- Attendance records
- Post-course follow-up evaluation
- Quiz / multiple-choice questions
- Practical exercises
- Case study
Course highlights
Dates and sessions
Choose the date and delivery format that suit you.
No upcoming sessions are currently available.
Course content offered in partnership with Softeam Institute (in French).
Oracle is a registered trademark of Oracle Corporation® (in French) and/or its affiliates.