DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: oracle 8.1.6.3.0 and optimizer_features_enabled

  1. #1
    Join Date
    Jan 2001
    Posts
    10

    Angry

    we have recently installed the release oracle 8.1.x for a SAP system release 40B. Our system is on 32 bits and SAP suggested us dissable some features for the optimizer (8.1.6).

    We have had many problems with sequential read on differents tables and yesterday we have a crash of the system and we don´t know the reason.

    Are There any one who explain me the new features of the optimizer for release 8.1.X and if these can be disabled to improve the performance and eliminate the probability of crash in our system again.
    oal

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Here is an article from metalink, and hope that this would help you,



    Explanation for Cost-based Optimizer and Rule-based Optimizer
    =============================================================

    To most, the best plan is the one that results in the fastest response time
    (first_rows hint). To others, the best plan is the plan that uses minimum
    amount of database resources (all_rows hint). In general, common goals of SQL
    tuning include :

    * Alleviating full-table scans by creating indexes whenever the cost of the
    index is smaller than the cost of waiting for the full-table scan.

    * Ensuring that the Oracle SQL optimizer is using all index resources
    properly.

    * Using bitmapped indexes whenever appropriate.

    * Using cost-based hints to improve the performance of SQL queries.

    -----------------------------------------------------------------------

    SQL Optimizers
    ==============

    Cost-based optimizer (henceforth CBO) can be used from release 7.2 and
    above, while rule-based optimizer (henceforth RBO) is recommended for
    releases 7.1 and below. When using the rule-base optimizer, the indexing of
    tables and order of clauses within an SQL statement control the access path.
    The CBO automatically determines the most efficient execution path, and hints
    can be added to a query to alter the access path. CBO or RBO can be set in the
    "init.ora" using the following parameter and values associated with it :

    + optimizer_mode=rule This will invoke the rule-based optimizer
    regardless of the presence of statistics on tables and indexes.

    + optimizer_mode=all_rows or first_rowsThis will use the cost-based
    optimizer and will estimate statistics at runtime if table and index
    statistics do not exist.

    + optimizer_mode=choose The cost-based optimizer will be invoked whenever
    statistics exist for any of the tables in a query. The rule-based
    optimizer will be used to service a query if all the tables in the query
    do not contain statistics.

    When you give Oracle the ability to choose the optimizer mode, it will
    favour the CBO if any table in a query has statistics (in other words table is
    analyzed ). And if this happens, Oracle will issue an ANALYZE TABLE ESTIMATE
    STATISTICS at runtime, slowing down any queries.

    With the RBO, table names are read from right to left. In a CBO, table
    names are read from left to right. Hence, it is important to know the driving
    table, which has the smallest number of rows in a query. Despite enhancements
    to CBO, many systems still perform better with RBO. Enhancements such as
    bitmapped indexes and STAR query hints have been offered for CBO in Oracle8,
    useful in data warehousing kind of applications

    Oracle Applications users please note that the CBO is used by default
    only from version 11i. All earlier versions require optimizer_mode=choose in
    "init.ora".


    Cost-Based Optimizer
    ====================

    CBO works by weighing the relative 'costs' for different access paths to the
    data, and choosing the path with the smallest relative cost. The CBO uses
    statistics derived from tables and indexes, and, in 7.3 and above column value
    distribution statistics can also be gathered. There are three ways to invoke
    the CBO :

    o set the optimizer_mode = all_rows or first_rows
    o ALTER SESSION SET OPTIMIZER_GOAL=all_rows or first_rows
    o Use of hints /*+ all_rows */ or /*+ first_rows */

    The 'costs' for a query are determined with the aid of table and index
    statistics that are computed with the following commands :

    sql>analyze table estimate statistics sample 10%;
    sql>analyze index compute statistics;

    NOTE: It is important that the statistics are refreshed periodically,
    especially when the distribution of data changes frequently. Hints override all
    settings for optimizer_mode and optimizer_goal , while optimizer_goal overrides
    optimizer_mode settings. The optimizer_mode settings only take effect when
    neither optimizer_goal settings nor hints are present. Because hints are coded
    into queries as comments, SQL will not indicate an error if a hint is
    improperly entered. So it is necessary to ensure that hints are properly stated
    within your queries.

    When issuing the ALTER SESSION SET OPTIMIZER_GOAL command, it is important to
    remember that the command will not take effect on SQL that is already in shared
    pool because Oracle will directly load the plan from the shared pool, ignoring
    the new setting for OPTIMIZER_GOAL. The two ways to get around this problem :

    o Issue the ALTER SYSTEM FLUSH SHARED POOL command.
    o Slightly alter (by adding spaces or changing the case of the characters) the
    SQL statement to make it different from the one in the shared pool.


    Rule-Based Optimizer
    ====================

    In RBO, the ordering of the table names in the FROM clause determines the
    driving table. The driving table is important because it is retrieved first,
    and the rows from the second table are then merged into the result set from the
    first table. Following are some tips while using RBO :

    + Try changing the order of the tables listed in the FROM clause. Joins
    should be driven from tables returning fewer rows and they should be
    listed last in the FROM clause.

    + Try changing the order of the statements in the WHERE clause. Oracle
    parses the SQL from the bottom of the SQL statement in the reverse order
    with boolean expressions separated by ANDs. Therefore, the most
    restrictive boolean expression should be on the bottom.

    + Be careful when creating indexes, especially while using RBO. RBO
    does not know the about the selectivity or distribution of the index
    column, as in the case of CBO where the tables and indexes are analyzed.

    + Always use EXPLAIN PLAN to examine the access path.

    + Understand which query paths are the fastest. For example, accessing a
    table by ROWID is the fastest access method, whereas a full-table scan is
    listed at the end for the ranking of query paths.

    + In Oracle8, the use of arrays significantly reduce database I/O. If there
    are 1000 rows to be selected an array size of 100 will reduce the reads to
    10 as opposed to 1000 if arrays are not used.

    RBO fails at times to recognize the most effective index that needs to be
    used while doing a select. This would lead to a slow-running query and would
    be necessary to disable the index by mixing data type on the index (where
    numeric_column=123||'') or use the INDEX hint. With CBO, the issue is resolved
    when the tables and indexes are analyzed, allowing the CBO to choose the best
    execution path based on the statistics available.

    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width