Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    Les13 RW

    Uploaded by

    Israa
    This document discusses optimizer hints in Oracle. It describes how hints can be used to influence the optimizer's decisions and execution plans. It provides examples of different types of hints for specifying optimizer mode, access paths, query transformations, join orders, and more. Guidelines are given for carefully using hints to avoid maintenance issues from hard-coded plans becoming invalid over time.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Les13 RW

    Uploaded by

    Israa
    13 views26 pages
    This document discusses optimizer hints in Oracle. It describes how hints can be used to influence the optimizer's decisions and execution plans. It provides examples of different types of hints for specifying optimizer mode, access paths, query transformations, join orders, and more. Guidelines are given for carefully using hints to avoid maintenance issues from hard-coded plans becoming invalid over time.

    Original Title

    Les13_RW

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses optimizer hints in Oracle. It describes how hints can be used to influence the optimizer's decisions and execution plans. It provides examples of different types of hints for specifying optimizer mode, access paths, query transformations, join orders, and more. Guidelines are given for carefully using hints to avoid maintenance issues from hard-coded plans becoming invalid over time.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    13 views26 pages

    Les13 RW

    Uploaded by

    Israa
    This document discusses optimizer hints in Oracle. It describes how hints can be used to influence the optimizer's decisions and execution plans. It provides examples of different types of hints for specifying optimizer mode, access paths, query transformations, join orders, and more. Guidelines are given for carefully using hints to avoid maintenance issues from hard-coded plans becoming invalid over time.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 26

    Optimizer Hints

    Copyright © 2006, Oracle. All rights reserved.


    Objectives

    After completing this lesson, you should be able to


    specify hints for:
    • Optimizer mode
    • Query transformation
    • Access path
    • Join orders
    • Join methods

    13 - 2 Copyright © 2006, Oracle. All rights reserved.


    Optimizer Hints: Overview

    Optimizer hints:
    • Are used to alter execution plans
    • Influence optimizer decisions
    • Provide a mechanism to instruct the optimizer to
    choose a certain query execution plan

    13 - 3 Copyright © 2006, Oracle. All rights reserved.


    Types of Hints

    Single-table hints Specified on one table or view


    Specify more than one table or
    Multitable hints
    view
    Query block hints Operate on a single query block

    Statement hints Apply to the entire SQL statement

    13 - 4 Copyright © 2006, Oracle. All rights reserved.


    Specifying Hints

    MERGE
    SELECT
    INSERT /*+ hint */
    UPDATE comment
    text
    DELETE
    MERGE
    SELECT
    INSERT --+ hint
    UPDATE comment
    text
    DELETE

    13 - 5 Copyright © 2006, Oracle. All rights reserved.


    Rules for Hints

    • Place hints immediately after the first SQL


    keyword of a statement block.
    • Each statement block can have only one hint
    comment, but it can contain multiple hints.
    • Hints apply to only the statement block in which
    they appear.
    • If a statement uses aliases, hints must reference
    aliases rather than table names.

    13 - 6 Copyright © 2006, Oracle. All rights reserved.


    Hint Recommendations

    • Use hints carefully because they imply a high


    maintenance load.
    • Be aware of the performance impact of hard-coded
    hints when they become less valid.

    13 - 7 Copyright © 2006, Oracle. All rights reserved.


    Optimizer Hint Syntax: Example

    UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/


    products p
    SET p.prod_min_price =
    (SELECT
    (pr.prod_list_price*.95)
    FROM products pr
    WHERE p.prod_id = pr.prod_id)
    WHERE p.prod_category = 'Men'
    AND p.prod_status = 'available, on stock'
    /

    13 - 8 Copyright © 2006, Oracle. All rights reserved.


    Hint Categories

    There are hints for:


    • Optimization approaches and goals
    • Access paths
    • Query transformations
    • Join orders
    • Join operation
    • Parallel execution

    13 - 9 Copyright © 2006, Oracle. All rights reserved.


    Optimization Goals and Approaches

    ALL_ROWS Chooses cost-based approach with


    a goal of best throughput
    FIRST_ROWS(n) Instructs Oracle Server to optimize
    an individual SQL statement for fast
    response

    13 - 10 Copyright © 2006, Oracle. All rights reserved.


    Hints for Access Paths

    FULL Performs a full table scan


    ROWID Accesses a table by ROWID

    INDEX Scans an index in ascending order

    INDEX_ASC Scans an index in ascending order

    INDEX_COMBINE Explicitly chooses a bitmap access


    path

    13 - 11 Copyright © 2006, Oracle. All rights reserved.


    Hints for Access Paths

    INDEX_JOIN Instructs the optimizer to use an index


    join as an access path
    INDEX_DESC Chooses an index scan for the
    specified table
    INDEX_FFS Performs a fast-full index scan
    NO_INDEX Disallows using a set of indexes
    AND_EQUAL Merges single-column indexes

    13 - 13 Copyright © 2006, Oracle. All rights reserved.


    INDEX_COMBINE Hint: Example

    SELECT --+INDEX_COMBINE(CUSTOMERS)
    cust_last_name
    FROM SH.CUSTOMERS
    WHERE ( CUST_GENDER= 'F' AND
    CUST_MARITAL_STATUS = 'single')
    OR CUST_YEAR_OF_BIRTH BETWEEN '1917'
    AND '1920';

    13 - 15 Copyright © 2006, Oracle. All rights reserved.


    INDEX_COMBINE Hint: Example

    Execution Plan
    ---------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=491
    Card=10481
    Bytes =167696)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
    (Cost=491 …)
    2 1 BITMAP CONVERSION (TO ROWIDS)
    3 2 BITMAP OR
    4 3 BITMAP AND
    5 4 BITMAP INDEX (SINGLE VALUE) OF
    'CUST_MARITAL_BIX'
    6 4 BITMAP INDEX (SINGLE VALUE) OF
    'CUST_GENDER_BIX'
    7 3 BITMAP MERGE
    8 7 BITMAP INDEX (RANGE SCAN) OF
    'CUST_YOB_BIX'

    13 - 16 Copyright © 2006, Oracle. All rights reserved.


    Hints for Query Transformation

    USE_CONCAT Rewrites OR into UNION ALL and


    disables INLIST processing
    NO_EXPAND Prevents OR expansions

    13 - 17 Copyright © 2006, Oracle. All rights reserved.


    Hints for Query Transformation

    MERGE Merges a view for each query


    NO_MERGE Prevents merging of mergeable views
    STAR_ Makes the optimizer use the best
    TRANSFORMATION plan in which the transformation can
    be used
    FACT Indicates that the hinted table should
    be considered as a fact table
    NO_FACT Indicates that the hinted table should
    not be considered as a fact table

    13 - 18 Copyright © 2006, Oracle. All rights reserved.


    Hints for Join Orders

    ORDERED Causes the Oracle Server to join


    tables in the order in which they
    appear in the FROM clause
    LEADING Uses the specified table as the first
    table in the join order

    13 - 20 Copyright © 2006, Oracle. All rights reserved.


    Hints for Join Operations

    USE_NL Joins the specified table using a


    nested loop join
    NO_USE_NL Does not use nested loops to
    perform the join
    USE_MERGE Joins the specified table using a
    sort-merge join
    NO_USE_MERGE Does not perform sort-merge
    operations for the join
    USE_HASH Joins the specified table using a
    hash join
    NO_USE_HASH Does not use hash join

    13 - 22 Copyright © 2006, Oracle. All rights reserved.


    Other Hints

    APPEND Enables direct-path INSERT


    NOAPPEND Enable regular INSERT
    ORDERED_PREDICATES Forces the optimizer to
    preserve the order of predicate
    evaluation
    CURSOR_SHARING_EXACT Prevents replacing literals with
    bind variables
    DYNAMIC_SAMPLING Controls dynamic sampling to
    improve server performance

    13 - 24 Copyright © 2006, Oracle. All rights reserved.


    Hints for Suppressing Index Usage

    Hint Description
    NO_INDEX Disallows use of any indexes

    FULL Forces a full table scan


    INDEX or Forces the optimizer to use a
    INDEX_COMBINE specific index or a set of listed
    indexes

    13 - 26 Copyright © 2006, Oracle. All rights reserved.


    Hints and Views

    • Do not use hints in views.


    • Use view-optimization techniques:
    – Statement transformation
    – Results accessed like a table
    • Hints can be used on mergeable views and
    nonmergeable views.

    13 - 27 Copyright © 2006, Oracle. All rights reserved.


    Hints for View Processing

    MERGE Merges complex views or subqueries


    with the surrounding query
    NO_MERGE Does not merge mergeable views

    13 - 29 Copyright © 2006, Oracle. All rights reserved.


    Global and Local Hints

    • Extended hint syntax enables the specifying of


    (global) hints through views.
    • References a table name in the hint with a dot
    notation
    CREATE view city_view AS
    SELECT *
    FROM customers c
    WHERE cust_city like 'S%';

    SELECT /*+ index(v.c cust_credit_limit_idx) */


    v.cust_last_name, v.cust_credit_limit
    FROM city_view v
    WHERE cust_credit_limit > 5000;

    13 - 30 Copyright © 2006, Oracle. All rights reserved.


    Specifying a Query Block in a Hint

    Explain plan for


    1
    SELECT employee_id, last_name
    FROM hr.employees e
    WHERE last_name = 'Smith';

    SELECT PLAN_TABLE_OUTPUT
    FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL,
    'ALL'));

    SELECT /*+ QB_NAME(qb) FULL(@qb e) */


    2
    employee_id, last_name
    FROM hr.employees e
    WHERE employee_id = 100;

    13 - 31 Copyright © 2006, Oracle. All rights reserved.


    Specifying a Full Set of Hints

    SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1


    emp_emp_id_pk) USE_MERGE(j) FULL(j) */
    e1.first_name, e1.last_name, j.job_id,
    sum(e2.salary) total_sal
    FROM hr.employees e1, hr.employees e2,
    hr.job_history j
    WHERE e1.employee_id = e2.manager_id
    AND e1.employee_id = j.employee_id
    AND e1.hire_date = j.start_date
    GROUP BY e1.first_name, e1.last_name, j.job_id
    ORDER BY total_sal;

    13 - 33 Copyright © 2006, Oracle. All rights reserved.


    Summary

    In this lesson, you should have learned how to:


    • Set the optimizer mode
    • Use optimizer hint syntax
    • Determine access-path hints
    • Analyze hints and their impact on views

    13 - 34 Copyright © 2006, Oracle. All rights reserved.

    You might also like