Course Details

Previous Page


Oracle 12c Performance Tuning: Tuning Problem SQL Statements


Overview/Description
Target Audience
Prerequisites
Expected Duration
Lesson Objectives
Course Number



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