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

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