• Online, Self-Paced

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 NICE Framework Task, Knowledge, and Skill statements identified within the indicated NICE Framework component(s):

Specialty Areas

  • Data Administration
  • Systems Administration
  • Systems Development

Specialty Areas have been removed from the NICE Framework. With the recent release of the new NICE Framework data, updates to courses are underway. Until this course can be updated, this historical information is provided to give better context as to how it can help you with your cybersecurity goals.

Feedback

If you would like to provide feedback for this course, please e-mail the NICCS SO at NICCS@hq.dhs.gov.