Course Overview
Tune your databases with targeted queries and tuning methodology so you can identify problems before they arise.
Learn how to use advisors inside of an oracle database, as well as various tools provided by Oracle, in order configure the right settings for optimal performance. Gain skills in this Oracle database course to:
- use targeted query tuning and methodology to identify tuning problems in an Oracle database to take corrective action
- how to identify and tune a common database
- troubleshoot performance issues
- use advisors inside the database in order to understand in advance what settings need to be made and how to use the various tools provided by Oracle to configure the database
What are the skills covered
After completing this course, you will be able to:
- Describe Performance Management
- Use AWR Performance Statistics
- Use AWR Based Tools
- Improve SQL Statement Performance
- Influence the Optimizer
- Use Tuning Analyzers and Advisors
- Monitor Operations and applications
- Manage Memory
- Manage Database Memory
- Use In-Memory Column Store Feature
Who should attend this course
This course intended for Database administrators and technical support staff.
Course Curriculum
What are the Prerequisites
- Working knowledge of using SQL and administering an Oracle database 19c.
- Basic knowledge of Linux / UNIX is recommended but not essential.
Download Course Syllabus
Course Modules
- Overview
- Objectives
- What is performance management?
- Who manages performance?
- What does the DBA tune?
- Types of tuning
- Tuning methodology
- Effective tuning goals
- General tuning session
- Tuning a CDB
- Performance tuning: diagnostics
- Performance tuning: features and tools
- Tuning objectives
- Summary
- Objectives
- Defining the problem
- Limit the scope
- Determining tuning priorities
- Common tuning problems
- Tuning life cycle phases
- Tuning during the life cycle
- Application design and development
- Testing: database configuration
- Deployment
- Production
- Migration, upgrade, and environment changes
- ADDM tuning session
- Performance versus business requirements
- Monitoring and tuning tools: overview
- Summary
- Objectives
- Time model: overview
- DB time
- CPU and wait time tuning dimensions
- Time model statistics hierarchy
- Time model: example
- Top timed events
- Summary
- Objectives
- Dynamic performance views
- Dynamic performance views: usage examples
- Dynamic performance views: considerations
- Statistic levels
- Instance activity and wait event statistics
- System statistic classes
- Displaying statistics
- Displaying SGA statistics
- Wait events
- Using the V$EVENT_NAME view
- Wait classes
- Displaying wait event statistics
- Commonly observed wait events
- Using the V$SESSION_WAIT view
- Precision of system statistics
- Summary
- Objectives
- Viewing the alert log
- Using alert log information as an aid in managing performance
- Administering the DDL log file
- Understanding the debug log file
- User trace files
- Background process trace files
- Summary
- Objectives
- Enterprise Manager: overview
- Oracle Enterprise Manager Cloud Control components
- Using features of the Oracle management packs and options
- Oracle SQL Developer
- SQL Developer command line (SQLcl)
- Summary
- Objectives
- Introduction to Statspack
- Statspack scripts
- Installing Statspack
- Capturing Statspack snapshots
- Configuring snapshot data capture
- Statspack snapshot levels
- Statspack baselines and purging
- Reporting with Statspack
- Statspack considerations
- Statspack reports
- Reading a Statspack report
- Statspack report drilldown sections
- Report drilldown examples
- Load profile section
- Time model section
- Statspack and AWR
- Summary
- Objectives
- Automatic Workload Repository: overview
- Automatic Workload Repository data
- Workload repository
- AWR administration
- AWR snapshot purging policy
- Managing snapshots with PL/SQL
- AWR snapshot settings
- Manual AWR snapshots
- Managing AWR data in a multitenant environment
- AWR snapshots and ADDM in a multitenant architecture database
- Generating AWR reports
- Reading the AWR report
- AWR report: multitenant data
- Generating AWR reports by using SQL*Plus
- Statspack and AWR reports
- Reading a Statspack or an AWR report
- Compare periods: benefits
- Snapshots and periods comparisons
- Compare periods: results
- Compare periods: report
- Multitenant AWR views
- Summary
- Objectives
- Metrics and alerts
- Limitation of base statistics
- Typical delta tools
- Oracle Database metrics
- Benefits of metrics
- Viewing metric history information
- Viewing metric details
- Statistics histograms
- Histogram views
- Server-generated alerts
- Alert usage model
- Metric and alert views
- Summary
- Objectives
- Comparative performance analysis with AWR baselines
- Automatic Workload Repository baselines
- AWR baselines
- Types of baselines
- Moving window baseline
- Baseline templates
- Creating AWR baselines
- Creating a single AWR baseline
- Creating a repeating baseline and template
- Managing baselines using the DBMS_WORKLOAD_REPOSITORY package
- Generating a baseline template for a single time period
- Creating a repeating baseline template
- Baseline views
- Performance monitoring and baselines
- Summary
- Objectives
- Automated maintenance tasks
- Maintenance windows
- Default maintenance plan
- Automated maintenance task priorities
- Configuring automated maintenance tasks
- Summary
- Objectives
- ADDM performance monitoring
- ADDM and database time
- DB time graph and ADDM methodology
- Top performance issues detected
- ADDM recommendations
- Creating a manual ADDM task
- ADDM tasks in a multitenant architecture database
- Changing ADDM attributes
- Retrieving ADDM reports using SQL
- Compare period ADDM: analysis
- Workload compatibility
- Configuring automatic ADDM analysis at the PDB level
- Using the DBMS_ADDM package to compare periods
- Summary
- Objectives
- Active Session History: overview
- Active Session History: mechanics
- ASH sampling: example
- Accessing ASH data
- Analyzing the ASH data
- Using Enterprise Manager to view ASH analysis
- Using Enterprise Manager to generate ASH reports
- Using the ASH report script to generate a report
- ASH report structure
- Determining the source of data
- Performing skew analysis
- Additional Automatic Workload Repository views
- Summary
- Objectives
- Emergency monitoring: challenges
- Emergency monitoring: goals
- Using Real-Time ADDM to perform a root cause analysis
- Using Real-Time ADDM
- Real-Time ADDM in the database
- Viewing Real-Time ADDM results
- Summary
- Objectives
- SQL statement processing phases
- Parsing
- SQL cursor storage
- Session cursor cache
- Cursor usage and parsing
- Bind phase
- Execute and fetch phases
- Processing a DML statement
- Commit processing
- Identifying poorly performing SQL statements
- Top SQL reports
- SQL monitoring
- Monitored SQL execution details
- Summary
- Objectives
- Creating indexes
- Using invisible and unusable indexes
- Dropping indexes
- Reducing the cost of SQL operations
- Index maintenance
- Using advanced index compression
- Other index options
- SQL Access Advisor
- Automatic indexing task
- Automatic indexing task workflow
- Automatic indexing task reporting
- Automatic indexing views
- Summary
- Objectives
- Reducing the cost of SQL operations
- Table maintenance for performance
- Table reorganization methods
- Space management
- Extent management
- Locally managed extents
- Large extents: considerations
- How table data is stored
- Anatomy of a database block
- Minimizing block visits
- Block allocation
- Free lists
- Block space management
- Block space management with free lists
- Automatic segment space management
- Automatic segment space management at work
- Block space management with ASSM
- Creating an automatic segment space management segment
- Migration and chaining
- Guidelines for PCTFREE and PCTUSED
- Detecting migration and chaining
- Selecting migrated rows
- Eliminating migrated rows
- Shrinking segments: overview
- Shrinking segments: considerations
- Shrinking segments by using SQL
- Segment shrink: basic execution
- Segment shrink: execution considerations
- Data compression
- Advanced row compression: overview
- Advanced row compression: concepts
- Using advanced row compression
- Advanced row compression for DML operations
- Advanced index compression
- How hybrid columnar compression works
- Using the compression advisor
- Using the compression advisor for indexes
- Viewing table compression information
- Summary
- Objectives
- Role of the Oracle Optimizer
- Functions of the Query Optimizer
- Selectivity
- Cardinality and Cost
- Changing Optimizer Behavior
- Setting and Viewing Optimizer Parameters
- Using Initialization Parameters to Control Optimizer Behavior
- Enabling Query Optimizer Features
- Influencing the Optimizer Approach
- Optimizing SQL Statements
- Access Paths
- Choosing an Access Path
- Summary
- Objectives
- What is an execution plan?
- Methods for Viewing Execution Plans
- Uses of Execution Plans
- DBMS_XPLAN Package: Overview
- EXPLAIN PLAN Command
- EXPLAIN PLAN Command: Example
- EXPLAIN PLAN Command: Output
- Reading an Execution Plan
- Using the V$SQL_PLAN View
- Querying V$SQL_PLAN
- V$SQL_PLAN_STATISTICS View
- Querying the AWR
- SQL*Plus AUTOTRACE
- Using SQL*Plus AUTOTRACE
- SQL*Plus AUTOTRACE: Statistics
- Adaptive Execution Plans
- Dynamic Plans
- Dynamic Plan: Adaptive Process
- Dynamic Plans: Example
- Continuous Adaptive Query Plans
- Automatic Re-Optimization
- Comparing Execution Plans
- Summary
- Objectives
- SQL Trace Facility
- How to Use the SQL Trace Facility
- Initialization Parameters
- Enabling SQL Trace
- Disabling SQL Trace
- Formatting Your Trace Files
- TKPROF Command Options
- Output of the TKPROF Command
- TKPROF Output with No Index: Example
- TKPROF Output with Index: Example
- Generating an Optimizer Trace
- Summary
- Objectives
- Optimizer Statistics
- Types of Optimizer Statistics
- Optimizer Statistics Collection
- Dynamic Statistics
- Gathering Statistics and Setting Optimizer Statistics Preferences
- Setting Statistic Preferences
- Viewing and Managing Optimizer Statistics Preferences
- Extended Statistics
- Maintaining Optimizer Statistics
- Automated Maintenance Tasks
- Optimizer Statistics Advisor
- Optimizer Statistics Advisor Report
- Executing Optimizer Statistics Advisor Tasks
- Restoring Statistics
- Deferred Statistics Publishing: Overview
- Deferred Statistics Publishing: Example
- Managing Real-Time Statistics
- Configuring High-Frequency Automatic Optimizer Statistics Collection
- Summary
- Objectives
- Automatic SQL tuning: overview
- SQL profiling
- SQL tuning loop
- SQL profiles
- Summary
- Objectives
- SQL Plan Management: Overview
- SQL Plan Baseline: Architecture
- Loading SQL Plan Baselines
- Loading SQL Plan Baselines from AWR
- Evolving SQL Plan Baselines
- Important SQL Plan Baseline Attributes
- SQL Plan Selection
- Possible SQL Plan Manageability Scenarios
- SQL Performance Analyzer and SQL Plan Baseline Scenario
- Loading a SQL Plan Baseline Automatically
- Purging SQL Management Base Policy
- Enterprise Manager and SQL Plan Baselines
- Automatic SQL Plan Management
- SPM Evolve Advisor
- Summary
- Objectives
- SQL Tuning Process
- SQL Tuning Advisor: Overview
- SQL Access Advisor: Overview
- SQL Performance Analyzer: Overview
- Summary
- Objectives
- Real Application Testing overview
- Use cases
- SQL Performance Analyzer
- Database Replay
- Summary
- Objectives
- SQL Access Advisor: Overview
- Using the SQL Access Advisor
- Viewing Recommendations
- Viewing Recommendation Details
- Practice Overview
- Summary
- Objectives
- Real Application Testing: Overview
- Real Application Testing: Use Cases
- Summary
- Objectives
- SQL Performance Analyzer: Process
- Steps 6–7: Comparing/Analyzing Performance and Tuning Regressed SQL
- Capturing the SQL Workload
- Creating a SQL Performance Analyzer Task
- SQL Performance Analyzer Task Page
- SQL Performance Analyzer Comparison Report
- SQL Performance Analyzer: PL/SQL Example
- Tuning Regressed SQL Statements
- SQL Performance Analyzer: Data Dictionary Views
- Summary
- Objectives
- Using Database Replay
- The Big Picture
- System Architecture: Capture
- System Architecture: Processing the Workload
- System Architecture: Replay
- Database Replay Workflow in Enterprise Manager
- Accessing Database Replay in Enterprise Manager
- Capture Considerations
- Replay Considerations: Preparation
- Replay Considerations
- Replay Customized Options
- Replay Analysis
- Database Replay Packages
- Data Dictionary Views: Database Replay
- Database Replay: PL/SQL Example
- Calibrating Replay Clients
- Capturing and Replaying in a CDB and PDBs
- Reporting
- Summary
- Objectives
- Overview
- Use Cases
- Defining a DB Operation
- Scope of a Composite DB Operation
- Database Operation Concepts
- Identifying a Database Operation
- Enabling Monitoring of Database Operations
- Identifying, Starting, and Completing a Database Operation
- Monitoring Database Operations in Sessions
- Monitoring the Progress of a Database Operation
- Monitoring SQL Operation Details
- Database Operation View: V$SQL_MONITOR
- Database Operation Views
- Reporting Database Operations by Using Functions
- Database Operation Tuning
- Summary
- Objectives
- What is a service?
- Service Attributes
- Service Types
- Creating Services
- Using the DBMS_SERVICE Package to Manage Services
- Where are services used?
- Using Services with Client Applications
- Using Services with the Resource Manager
- Using Enterprise Manager to Manage Consumer Group Mappings
- Services and the Resource Manager: Example
- Using Enterprise Manager to Create a Job Class
- Using Enterprise Manager to Create a Job
- Services and the Scheduler: Example
- Using Services with Metric Thresholds
- Using Enterprise Manager to Change Service Thresholds
- Services and Metric Thresholds: Example
- Service Aggregation and Tracing
- Services Statistics in Performance Home Page
- Top Services Statistics Using ASH Analytics Performance Page
- Service Aggregation Configuration
- Service Aggregation: Example
- Client Identifier Aggregation and Tracing
- Using the TRCSESS Utility
- Service Performance Views
- Summary
- Objectives
- Managing Memory Caches and Structures
- Guidelines for Efficient Memory Usage
- Unified Memory
- Summary
- Objectives
- Shared Pool Architecture
- Shared Pool Operation
- Library Cache
- Latch and Mutex
- Latch and Mutex: Views and Statistics
- Diagnostic Tools for Tuning the Shared Pool
- AWR/Statspack Indicators
- Top Timed Events
- Time Model
- Load Profile
- Instance Efficiencies
- Library Cache Activity
- Avoid Hard Parses
- Are Cursors Being Shared?
- Candidate Cursors for Sharing
- Sharing Cursors
- Adaptive Cursor Sharing: Example
- Adaptive Cursor Sharing Views
- Interacting with Adaptive Cursor Sharing
- Reducing the Cost of Soft Parses
- Sizing the Shared Pool
- Shared Pool Advisory
- Shared Pool Advisory in an AWR Report
- Shared Pool Advisor
- Avoiding Fragmentation
- Large Memory Requirements
- Tuning the Shared Pool Reserved Pool
- Keeping Large Objects
- Data Dictionary Cache
- Dictionary Cache Misses
- SQL Query Result Cache: Overview
- Managing the SQL Query Result Cache
- Using the RESULT_CACHE Hint
- Using Table Annotation to Control Result Caching
- Using the DBMS_RESULT_CACHE Package
- Viewing SQL Result Cache Dictionary Information
- SQL Query Result Cache: Considerations
- Summary
- Objectives
- Buffer Cache: Highlights
- Database Buffers
- Buffer Hash Table for Lookups
- Working Sets
- Tuning Goals and Techniques
- Symptoms of a Buffer Cache Issue
- Cache Buffer Chains Latch Contention
- Finding Hot Segments
- Buffer Busy Waits
- Buffer Cache Hit Ratio
- Buffer Cache Hit Ratio is Not Everything
- Interpreting Buffer Cache Hit Ratio
- Read Waits
- Free Buffer Waits
- Solutions for Buffer Cache Issues
- Sizing the Buffer Cache
- Buffer Cache Size Parameters
- Dynamic Buffer Cache Advisory Parameter
- Buffer Cache Advisory View
- Using the V$DB_CACHE_ADVICE View
- Using the Buffer Cache Advisor
- Caching Tables
- Automatic Big Table Caching
- Configuring Automatic Big Table Caching
- Using Automatic Big Table Caching
- Monitoring Automatic Big Table Caching
- Memoptimized Rowstore
- In-Memory Hash Index
- Multiple Buffer Pools
- Enabling Multiple Buffer Pools
- Calculating the Hit Ratio for Multiple Pools
- Multiple Block Sizes
- Multiple Database Writers
- Multiple I/O Slaves
- Using Multiple Writers and I/O Slaves
- Private Pool for I/O-Intensive Operations
- Automatically Tuned Multiblock Reads
- Database Smart Flash Cache Overview
- Using Database Smart Flash Cache
- Database Smart Flash Cache Architecture: Overview
- Configuring Database Smart Flash Cache
- Sizing Database Smart Flash Cache
- Enabling and Disabling Flash Devices
- Specifying Database Smart Flash Cache for a Table
- Full Database In-Memory Caching
- Setting Up Force Full Database Caching
- Monitoring Full Database In-Memory Caching
- Flushing the Buffer Cache (for Testing Only)
- Summary
- Practice Overview
- Objectives
- SQL Memory Usage
- Performance Impact
- Automatic PGA Memory
- SQL Memory Manager
- Configuring Automatic PGA Memory
- Setting PGA_AGGREGATE_TARGET Initially
- Limiting the Size of the Program Global Area
- Managing the PGA for PDBs
- Monitoring SQL Memory Usage
- Monitoring SQL Memory Usage: Examples
- Tuning SQL Memory Usage
- PGA Target Advice Statistics
- PGA Target Advice Histograms
- Automatic PGA and Enterprise Manager
- Automatic PGA and AWR Reports
- Temporary Tablespace Management: Overview
- Temporary Tablespace: Locally Managed
- Configuring Temporary Tablespace
- Temporary Tablespace Group: Overview
- Temporary Tablespace Group: Benefits
- Creating Temporary Tablespace Groups
- Maintaining Temporary Tablespace Groups
- Viewing Tablespace Groups
- Monitoring Temporary Tablespace
- Shrinking a Temporary Tablespace
- Using the Tablespace Option When Creating a Temporary Table
- Summary
- Objectives
- Large Pool Overview
- Tuning the Large Pool
- Summary
- Objectives
- Oracle Database Architecture
- Granules
- Automatic Shared Memory Management: Overview
- SGA Sizing Parameters: Overview
- Dynamic SGA Transfer Modes
- Memory Broker Architecture
- Manually Resizing Dynamic SGA Parameters
- Behavior of Auto-Tuned SGA Parameters
- Behavior of Manually Tuned SGA Components
- Using the V$SYSTEM_PARAMETER View
- Resizing SGA_TARGET
- Disabling Automatic Shared Memory Management
- Using the SGA Advisor
- Monitoring ASMM
- Managing SGA for PDBs
- Summary
- Objectives
- Database In-Memory Feature Set
- Goals of In-Memory Column Store
- Benefits
- Overview
- Row Store Versus Column Store: 2D Vision
- In-Memory Column Unit
- Compare: In-Memory Column Store Cache and Buffer Cache
- Dual Format In-Memory
- Indexes Issues
- Process
- In-Memory Column Store: Dual Format of Segments in SGA
- Using OEM to Manage In-Memory Pool
- Summary
- Objectives
- Deploying IM Column Store
- Using OEM to Manage In-Memory Pool
- Deploying IM Column Store: Objects Setting
- Deploying IM Column Store: Columns Setting
- Defining IM Column Store Compression
- In-Memory Advisor
- IM Advisor or Compression Advisor?
- Computing Compression Ratio
- IM FastStart
- Automatic In-Memory: Overview
- AIM Action
- Configuring Automatic In-Memory
- Diagnostic Views
- Summary
- Objectives
- Query Benefits
- Testing and Comparing Query Performance
- Queries on In-Memory Tables: Simple Predicate
- MINMAX Pruning Statistics
- IM Column Store Statistics
- Execution Plan: TABLE ACCESS IN MEMORY FULL
- Queries on In-Memory Tables: Join
- Execution Plan: JOIN FILTER CREATE / USE
- Queries on In-Memory Tables: Join Groups
- Population of Expressions and Virtual Columns Results
- In-Memory Expression Unit (IMEU)
- Populating In-Memory Expression Results
- Populating In-Memory Expression Results Within a Window
- Waiting for In-Memory Segments to Be Populated
- Views
- Summary
- Objectives
- Interaction with Other Products
- Optimizer
- IM Column Store and Real Application Clusters
- IM Column Store and Data Pump
- Data Pump TRANSFORM Names
- Automatic Data Optimization Interaction
- Managing Heat Map and Automatic Data Optimization Policies
- Creating ADO In-Memory Policies
- Summary
Request More Information
Training Options
- ILT: Instructor-Led Training
- VILT: Virtual Instructor-Led Training
Intake: 6-10 Apr 2026
Duration: 5 Days
Guaranteed: TBC
Modality: ILT
Price:
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
Exam:
[yith_ywraq_button_quote product="144442"]
[yith_ywraq_button_quote product="144443"]
Intake: 6-10 Apr 2026
Duration: 5 Days
Guaranteed: TBC
Modality: VILT
Price:
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
Exam:
[yith_ywraq_button_quote product="144444"]
[yith_ywraq_button_quote product="144445"]
Intake: 8-12 Jun 2026
Duration: 5 Days
Guaranteed: TBC
Modality: ILT
Price:
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
Exam:
[yith_ywraq_button_quote product="144446"]
[yith_ywraq_button_quote product="144447"]
Intake: 8-12 Jun 2026
Duration: 5 Days
Guaranteed: TBC
Modality: VILT
Price:
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
Exam:
[yith_ywraq_button_quote product="144448"]
[yith_ywraq_button_quote product="144449"]
Intake: 3-7 Aug 2026
Duration: 5 Days
Guaranteed: TBC
Modality: ILT
Exam:
[yith_ywraq_button_quote product="144450"]
[yith_ywraq_button_quote product="144451"]
Intake: 3-7 Aug 2026
Duration: 5 Days
Guaranteed: TBC
Modality: VILT
Exam:
[yith_ywraq_button_quote product="144452"]
[yith_ywraq_button_quote product="144453"]
Intake: 5-9 Oct 2026
Duration: 5 Days
Guaranteed: TBC
Modality: ILT
Exam:
[yith_ywraq_button_quote product="144454"]
[yith_ywraq_button_quote product="144455"]
Intake: 5-9 Oct 2026
Duration: 5 Days
Guaranteed: TBC
Modality: VILT
Exam:
[yith_ywraq_button_quote product="144456"]
[yith_ywraq_button_quote product="144457"]
Intake: 7-11 Dec 2026
Duration: 5 Days
Guaranteed: TBC
Modality: ILT
Exam:
[yith_ywraq_button_quote product="144458"]
[yith_ywraq_button_quote product="144459"]
Intake: 7-11 Dec 2026
Duration: 5 Days
Guaranteed: TBC
Modality: VILT
Exam:
[yith_ywraq_button_quote product="144460"]
[yith_ywraq_button_quote product="144461"]
Exam & Certification
Oracle AI Database: Performance Management and Tuning exam 1Z0-183
The Oracle AI Database Administration Professional Certification recognizes advanced skills in deploying, managing, tuning, and securing Oracle AI Database environments. Candidates demonstrate expertise in multitenant architecture (CDBs/PDBs), RMAN backup and recovery, SQL performance tuning, and implementing new features such as Lock-Free Reservations, True Cache, and Blockchain Tables. This certification is ideal for DBAs working with on-premises, cloud, or hybrid infrastructures.
Training & Certification Guide
Exam Format: Multiple Choice
Duration: 120 Minutes
Number of Questions: 65
Passing Score: 60%
