Oracle Database 11g: Optimizer Statistics, Hints, and Cursor Sharing
Support engineers, developers, technical consultants, data warehouse developers, and application developers.
Oracle Database 11g: Introduction to SQLOracle Database 11g: SQL Fundamentals I
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.
- recognize how to gather optimizer statistics
- recognize how to gather system statistics
- use dynamic sampling
- use system statistics
Bind Variables, Cursor Sharing, and Hints
- 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