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