Once poorly performing SQL is identified, and changes proposed, those changes need to be validated to ensure that they will yield the desired effect. This course will show how tools such as the SQL Performance Analyzer and SQL Tuning Advisor can be used to achieve this goal. This course is one of a series in the Skillsoft learning path that covers the objectives for the Oracle Database 12c: Performance Management and Tuning exam (1ZO-064).
Learning Objectives
Using the SQL Performance Analyzer
- start the course
- use Real Application Testing
- use SQL Performance Analyzer
- create and compare SQL Performance Analyzer tasks
- use the SQL Tuning Advisor to assist in tuning regressing SQL statements
- describe the PL/SQL functions used to manipulate the SQL Performance Analyzer
SQL Performance Management
- describe the fundamental methods for maintaining SQL performance
- describe the options available when configuring Optimizer statistics
- describe the concept of deferred statistics publishing
- recognize the concept of automatic SQL tuning
- describe the use of the SQL Tuning Advisor
- identify the use of the SQL Access Advisor
SQL Plan Management
- recognize the concept and architecture of SQL plan baselines
- describe how SQL plan baselines are created and evolve over time
- describe adaptive SQL plan management
- recognize how SQL plan selection works and list scenarios under which it comes into play
- list the methods available to configure the SPM infrastructure and show the interaction with Enterprise Manager
Using Database Replay
- define the concept of Database Replay
- describe the architecture of the components that make up Database Replay
- describe the workflow implementation of Database Replay within Enterprise Manager
- describe considerations for capturing a workload
Executing a Replay and Viewing Results
- describe how to properly cater for Database Replay tasks
- describe the process of replaying a workload
- compare the results of a workload replay report
- list the packages and views associated with using and configuring Database Replay
Practice: SQL Plan Management
- use the DBMS_SPM package to fix a particular execution plan for a given SQL statement