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