Oracle Database 11g: Problem SQL Statements and Oracle Optimizer


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


Overview/Description
Oracle Database 11g helps to identify problem SQL statements using Oracle Optimizer and execution plans. This course discusses SQL statement processing and the role of the Oracle Optimizer and execution plans. It also explains how to generate and view execution plans using the DBMS_XPLAN package, the EXPLAIN PLAN command, SQL*Plus AUTOTRACE command options, and the V$SQL_PLAN view. The functions of the Optimizer and how to influence and control the optimizer's behavior is discussed in detail, including the various query access paths that can be used, when to use different join methods and sort operations, how to manage and maintain indexes, and how to reorganize tables to optimize the processing of SQL statements.

Target Audience
A range of in-training or established IT professionals; Database Administrators; Support Engineers; Technical Consultants.

Prerequisites
Oracle Database 11g: Administration Workshop I Oracle Database 11g: Administration Workshop II

Expected Duration (hours)
2.5

Lesson Objectives

Oracle Database 11g: Problem SQL Statements and Oracle Optimizer

  • match each phase in SQL statement processing with its features
  • identify the steps in DML processing
  • identify the operations performed by the Oracle optimizer
  • recognize the features of execution plans
  • recognize how to generate and view execution plans
  • recognize how to use the SQL*Plus AUTOTRACE command options to view execution plans and statistics
  • identify the steps involved in using the SQL Trace facility
  • identify the functions of the query optimizer
  • recognize how to influence and control the optimizer's behavior
  • identify the scan types for retrieving data from the database
  • recognize how to work with bitmap and B*Tree indexes
  • identify when to use different join methods
  • identify common sort operations
  • recognize how to manage and maintain indexes
  • recognize how to reorganize tables
  • use AUTOTRACE to analyze statistics and execution plans in a given scenario

    • Course Number:
    od_odpt_a04_it_enus