Programming, Database, For Corporates, For Working Professionals

Oracle Database 19c: Performance Management and Tuning

Course Duration: 40 hours

Course Level: Intermediate

About Course

Language: English

Awarding Body: Oracle

Duraration: 40 Hrs

Associated Exam: 1Z0-084

Course Description:

The Oracle Database 19c: Performance Management and Tuning OCP has demonstrated a strong understanding of performance analysis, management and tuning for optimal performance of an Oracle Database environment. They can apply a database tuning methodology by using the appropriate tools in order to monitor, diagnose and tune instance and SQL performance problems. This candidate possesses a profound knowledge of the Oracle Database architecture, great familiarity with day-to-day database administration, knowledge of which advisors can be used to inspect various aspects/areas of the Oracle Database environment, and a good working knowledge of SQL, its executions plans and generated statistics.

Pre-Requisite:

Oracle Database 12c Administrator Certified Professional; or
Oracle Database Administration 2019 Certified Professional

Course Objectives:

After taking this course, you should be able to:

Use AWR Based Tools
Improve SQL Statement Performance
Influence the Optimizer
Use Tuning Analyzers and Advisors
Monitor Operations and applications
Manage Memory

Course Content:

Course Overview
Performance Management Overview
Defining the Scope of Performance Issues
Using the Time Model to Diagnose Performance Issues
Using Statistics and Wait Events to Diagnose Performance Issues
Using Log and Trace Files to Monitor Performance
Using Enterprise Manager Cloud Control and SQL Developer to Monitor Performance
Using Statspack to View Performance Data
Using Automatic Workload Repository
Using Metrics and Alerts
Using Baselines
Managing Automated Maintenance Tasks
Using ADM to Analyze Performance
Using Active Session History Data for First Fault System Analysis
Using Emergency Monitoring and Real-Time ADDM to Analyze Performance Issues
Overview of SQL Statement Processing
Maintaining Indexes
Maintaining Tables
Introduction to Query Optimizer
Understanding Execution Plans
Viewing Execution Plans by Using SQL Trace and TKPROF
Managing Optimizer Statistics
Using Automatic SQL Tuning
Using the SQL Plan Management Feature
Overview of the SQL Advisors
Using the SQL Tuning Advisor
Using the SQL Access Advisor
Overview of Real Application Testing Components
Using SQL Performance Analyzer to Determine the Impact of Changes
Using Database Replay to Test System Performance
Implementing Real-Time Database Operation Monitoring
Using Services to Monitor Applications
Overview of Memory Structures
Managing Shared Pool Performance
Managing Buffer Cache Performance
Managing PGA and Temporary Space Performance
Configuring the Large Pool
Using Automatic Shared Memory Management
Introduction to In-Memory Column Store
Configuring the In-Memory Column Store Feature
Using the In-Memory Column Store Feature to Improve SQL Performance
Using In-Memory Column Store with Oracle Database Features

Benefits of the course

It teaches hands-on techniques for SQL optimization, including execution plan analysis, use of SQL Trace/Autotrace, and tuning with SQL Tuning Advisor
The course empowers professionals to diagnose and resolve performance issues by using advanced tools like AWR, ASH, ADDM, and Enterprise Manager
It equips DBAs with the skills to optimize database I/O, manage statistics, indexes, and resource usage for sustained enterprise performance
The program enables learners to set up proactive performance monitoring and alerting using metrics, baselines, and performance advisors
Participants learn how to effectively tune memory structures such as SGA, PGA, buffer cache, and shared pool to improve database responsiveness