• Online, Self-Paced
Course 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).

Learning Objectives

Influencing the Optimizer

  • 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

Optimizer Access Paths and Join Orders

  • 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

Indexing and Block Visits

  • 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

Compressing Segments

  • 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

Practice: Influencing the Optimizer

  • examine how inaccurate statistics can mislead the optimizer and rectifying the problem


Framework Connections

The materials within this course focus on the Knowledge Skills and Abilities (KSAs) identified within the Specialty Areas listed below. Click to view Specialty Area details within the interactive National Cybersecurity Workforce Framework.