Oracle 12c Performance Tuning: Tuning Problem SQL Statements

Database Administrators, Application Developers, Technical Consultants.

Prerequisite
None

Expected Duration
103 minutes

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).

Objective

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

MONTHLY SUBSCRIPTION

$129/month
 

ANNUAL SUBSCRIPTION

$1295/year

Multi-license discounts available for Annual and Monthly subscriptions.