Oracle 19c Database Tuning (TTOR21519)

Course Objectives

Course Prerequisites

This course is geared for professionals seeking a solid foundation in database management and optimization. The training is perfect for experiences database administrators / DBAs, data analysts, and software developers who are involved in data-intensive operations or wish to enhance their skills in database tuning.

This hands-on course is geared for experienced DBAs already working with Oracle. Incoming students should possess:

• Familiarity with basic database concepts such as tables, records, and relationships between data is crucial before diving into the specifics of Oracle Database Tuning.
• Practical knowledge of basic SQL commands like SELECT, INSERT, UPDATE, and DELETE. Knowing how to write simple SQL queries will provide a solid foundation for learning more advanced optimization techniques.
• A general understanding of how Oracle Database functions, including its structure and primary components.

Take Before: Attendees should have skills equivalent to or should have attended this course as a pre-requisite:

• TTOR20519   Oracle 19c Database Administration I (Oracle DBA I)

Course Agenda

1. Overview Oracle Database Architecture

• Instance Definition
• Define SGA
• Define Background Processes
• Datafile Definition

2. Query Optimizer

• SQL Parsing
• Optimizing Terms
• Optimizing Methods
• Query Plan Generation
• Query Plan Control

3. Tuning Container Databases and Pluggable Databases

• Pluggable tuning parameters
• Define Container tuning structure
• Using PDB$SEED
• Create a new PDB
• Plug and unplug a PDB

4. Oracle Tuning features

• Identifying and Using Oracle's Heat Map
• 19c Compression Levels and Types

5. Evaluating Execution Plans

• Defining SQL execution plans
• Automatic Workload Repository
• Reading execution plans

6. Oracle Tuning Tools

• Monitoring tools overview
• Enterprise Manager
• Dynamic Performance Views
• Automatic Workload Repository
• Automatic Database Diagnostic Monitor
• Sql Tuning Advisor
• SQL Access Advisor
• Sql Access Advisor
• DB operation Tuning
• DB operation Active Reporting

7. Using Automatic Workload Repository

• Defining AWR
• AWR Settings
• Creating AWR Baselines

8. Metrics, Alerts, and Thresholds

• Defining Metrics
• Setting Alerts
• Setting Corrective Action
• User Defined Metrics
• Metric Dynamic Views

9. Join Types

• Nested Loops Join
• Sort Merge join
• Hash Join and Cartesian Join
• Equijoins and Nonequijoins
• Outer Joins
• Semijoins

10. AWR Using Baselines

• Creating AWR baselines
• Creating AWR Repeating baselines
• Moving Window Baseline

11. Additional AWR performance tools

• Automatic Maintenance Tasks
• Segment Advisor
• Statistics Gathering
• Automatic Tuning Optimizer
• Automatic Database Diagnostic Monitor
• Active Session History (ASH)

12. Optimizer Statistics

• Optimizer Statistics Overview
• Table and Index Statistics
• Statistic Preferences
• Statistics Gathering
• e) Locking Statistics, Export/Import Statistics
• Pending and published statistics
• Optimizer Hints
• Optimizer Paths
• Cost Base Optimization

13. Monitoring a Service

• Overview of what is an Oracle Service
• Creating an Oracle Service for Single instance and RAC
• Monitoring a Service
• Resource Management and a Service
• Enterprise Manager and a Service

14. Bind Variables and database parameters

• Bind variable definition
• Cursor_sharing parameter
• Adaptive Cursor Sharing

15. Oracle's Real Application Testing (RAT)

• Sql Performance Analyzer overview
• Sql Performance Analyzer Options
• Database Parameter changes
• Database version changes
• Creating SQL Tuning Sets
• Database Replay Overview
• Database Replay Configuration
• Database Replay Options

16. SQL Tuning Advisor

• SQL Tuning Advisor: Overview
• SQL Tuning Advisor Limited Mode
• Sql Tuning Advisor Comprehensive mode
• Sql Tuning Profiles

17. SQL Access Advisor

• SQL Access Advisor: Overview
• Sql Access Advisor options
• SQL Access Advisor and Sql Tuning Sets
• Sql Access Advisor and AWR
• Results and Implementation

18. Automatic Sql Tuning

• Automatic Sql Tuning Maintenance Task
• Automatic Tuning Optimization implementation(ATO)
• Automatic Tuning Optimization Results
• Enable/Disable Automatic Tuning Optimization

19. Sql Plan Management

• Sql plan Management and baseline overview
• Enable sql plan management
• Loading Sql Plan baselines into the SGA
• Adaptive plan management

20. Shared Pool Tuning

• Shared pool architecture
• Shared pool parameters
• Library Cache
• Dictionary cache
• Large pool considerations and contents

21. Tuning the database buffer cache

• Database buffer cache overview
• Database buffer cache parameters
• Oracle and Dirty reads and writes
• Automatic Shared Memory Management (ASMM)
• Buffer Cache goals and responsibility
• Buffer Cache pools

22. Tuning the PGA (Program Global Area)

• PGA Overview
• PGA Database Parameters
• Temporary Segments
• Temporary Tablespace
• Sizing the PGA

23. Automatic Memory Management (AMM)

• Oracle's Automatic Memory Management Overview
• Database Auto-tuned Parameters
• Database Non Auto-tuned Parameters
• Automatic Memory Management Hints and Sizing suggestions
• AMM versus ASMM

24. Tuning Segment space Utilization (ASSM)

• Overview of Automatic Segment Space Management
• Defining the DB_BLOCK_SIZE
• Defining DB_nk_CACHE_SIZE parameter
• The DB_BLOCK_SIZE Parameter
• Overview of table compression, block chaining, and block migration

25. Automatic Storage Management

• Overview of ASM
• Definition of Grid Infrastructure
• ASM Instance
• ASM Diskgroups
• ASM Diskgroup parameters and templates
• ASMCMD

Course Materials

Student Materials: Each participant will receive a digital Student Guide and/or Course Notes, code samples, software tutorials, step-by-step written lab instructions (as applicable), diagrams and related reference materials and resource links. Students will also receive the project files (or code, if applicable) and solutions required for the hands-on work.

Hands-On Setup Made Simple! Our dedicated tech team will work with you to ensure our ‘easy-access’ cloud-based course environment, or local installation, is accessible, fully-tested and verified as ready to go well in advance of the course start date, ensuring a smooth start to class and effective learning experience for all participants. In some cases we can also help you install this course locally if preferred. Please inquire for details and options.

Every-Course Extras = High-Value & Long-Term Learning Support! All Public Schedule courses include our unique EveryCourse Extras package (Course Recordings, Live Instructor Follow-on Support, Free *Live* Course Refresh Re-Takes, early access to Special Offers, Free Courses & more). Please inquire for details.