Course Details

Previous Page


Oracle Database 11g: Optimizer Statistics, Hints, and Cursor Sharing


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



Overview/Description
Optimizer statistics describe details about the database and the objects in the database and are used by the query optimizer to select the best execution plan for each SQL statement. This course covers gathering optimizer and system statistics, setting statistic preferences, using dynamic sampling, and manipulating optimizer statistics. if you use SQL statements where literal values are provided for the WHERE clause conditions, you get results in many versions of almost identical SQL to be stored in the library cache. If, instead of issuing different statements for each literal, you use a bind variable, then in theory that extra parse activity is eliminated. Listing the benefits of using bind variables, as well as using bind peeking and adaptive cursor sharing are examined in this course. Optimizer hints enable you to influence decisions made by the optimizer and provide a mechanism to direct the optimizer to select a certain query execution plan based on the specific criteria. The use of optimizer hints, including specifying hints for optimizer mode, query transformation, access path, join orders, and join methods is also covered.

Target Audience
Support engineers, developers, technical consultants, data warehouse developers, and application developers.

Prerequisites
Oracle Database 11g: Introduction to SQL Oracle Database 11g: SQL Fundamentals I

Expected Duration (hours)
3.0

Lesson Objectives

Oracle Database 11g: Optimizer Statistics, Hints, and Cursor Sharing

  • recognize how to gather optimizer statistics
  • recognize how to gather system statistics
  • use dynamic sampling
  • use system statistics
  • identify the features of bind variables
  • use cursor sharing
  • use optimizer hints
  • use hints and views
  • recognize how adaptive cursor sharing, the CURSOR_SHARING initialization parameter, and hints are used
  • Course Number:
    od_ortw_a04_dt_enus