Oracle 12c Performance Tuning: Tuning Problem SQL Statements Overview/Description Target Audience Prerequisites Expected Duration Lesson Objectives Course Number Expertise Level Overview/Description
SQL is at the heart of any relational database management system, and optimizing SQL typically provides the largest performance gains in real-world situations. This course will discuss the Cost-Based Optimizer, which is key in determining the execution path of a SQL statement. You will also learn how to optimize the retrieval of data. 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).
Target Audience
Database Administrators, Application Developers, Technical Consultants.
Prerequisites
None
Expected Duration (hours)
1.7
Lesson Objectives Oracle 12c Performance Tuning: Tuning Problem SQL Statements
start the course
describe the tasks of the query optimizer during SQL parsing
describe the concepts of selectivity, cardinality and cost
describe statistics used by the optimizer
list the database parameters which influence the behavior of the optimizer
describe adaptive and dynamic execution plans
describe the cardinality feedback feature
describe how manipulating the optimizer_mode parameter affects SQL operations
describe the ways in which data is retrieved from the database
describe the most commonly used index access paths
describe the concept of join operations
list the types of join operations used by the optimizer
describe the conditions under which each join operation works best
describe the basics of sorting and briefly touch on tuning sort performance
describe how adding indexes can improve SQL performance
describe how index maintenance operations can improve SQL performance
describe how space usage of tables can impact performance
define the concept of extents and how they relate to the performance of SQL operations
describe the structure of a database block
list the ways of reducing block visits and describe the methods of block allocation
describe block space management with freelists
describe the concept of ASSM
describe block space management with ASSM
define the concepts of block migration and chaining
describe how shrinking segments can improve the performance of SQL statements
describe how data compression can assist in improving the performance of SQL statements
describe the concept of HCC and using the compression advisor
examine how inaccurate statistics can mislead the optimizer and rectifying the problem
Course Number: od_pmte_a05_it_enus
Expertise Level
Intermediate