に つ い て oracle optimizer

2010-11-26  来源:本站原创  分类:Database  人气:132 

I. Purpose:
1, talk about Oracle's Optimizer and related some of the knowledge.
2, the answer is sometimes why a field of a table even when the index, when the observed number of SQL execution plan and found that the index does not take issue.
3, if you FIRST_ROWS, ALL_ROWS these two models can also be in doubt look at this article.

Before executing a SQL Oracle, we must first analyze the statement execution plan, then the implementation plan to implementation. Statement of the implementation plan of the work by the optimizer (Optimizer) to complete. Different situations, one may have a variety of SQL execution plans, but at some point, some only one execution plan is optimal to spend the least time.

Second,

1, the optimizer optimization method:

Oracle's Optimizer Optimization There are two methods, namely, rule-based optimization method (Rule-Based Optimization, referred to as RBO) and cost-based optimization method (Cost-Based Optimization, referred to as CBO).

A, RBO mode: SQL statement optimizer in the analysis, the follow is the Oracle within a predetermined number of rules.

The most common example, when a where clause in an index to go with the index.

B, CBO ways: by meaning we can see, it is to look at the cost of statement (Cost), here mainly refers to the cost of Cpu and memory. Optimizer to determine whether this way, the main reference was to the table and index statistics. Given the size of the table statistics, the number of lines, each the length of the other information. At first, these statistics are not in the database, it is only after you analyze that appears to do, a lot of statistical information at the right time expired optimizer will make a wrong execution plan, because we shall be updated with more information. In Oracle8 and later, Oracle CBO recommended way out.

Not necessarily take the index is excellent, such as a table with only two rows, one IO to complete the whole table to retrieve, at which point you will need to take the index twice IO, then on the table to do a full table scan (full table scan) is the best.

2, the optimizer optimized mode (Optermizer Mode)
Optimization models, including Rule, Choose, First rows, All rows that are four ways, which we mentioned above.

Rule: Needless to say, that is, rule-based way to go.

Choose: This is the view we should note, the default Oracle is used in this way. Means that when a table or index has statistical information, the CBO way to go, if not the table or index statistics, tables are not particularly small, and the corresponding index lists, then the left index, the way to go RBO .

First Rows: Choose ways it is similar, the difference is that when a table has statistics, it is the fastest way to return to the query the first few lines, reducing overall response time.

All Rows: Cost is the way we say, when a table with statistical information, it is the fastest way to return to the table all the rows, the query from the overall increase in throughput. No statistical information is rules-based way to go.

3, how to choose which set the optimization mode
A, Instance level:
We can in the init <SID>. Ora file set OPTIMIZER_MODE = RULE, OPTIMIZER_MODE = CHOOSE, OPTIMIZER_MODE = FIRST_ROWS, OPTIMIZER_MODE = ALL_ROWS referred to the selection of three of four way, if you do not set the default parameters used OPTIMIZER_MODE Choose this approach is.

B, Sessions Level:
By SQL> ALTER SESSION SET OPTIMIZER_MODE = <Mode>; to set.

C, the statement level:
Hint of these need to use

Third, why sometimes a table even when the index a field, when the observed number of words in the implementation plan does not follow the index it? Solution?

A, do not take the index generally have the following reasons:
1, at the Instance level you are used to the way all_rows 2, your table of statistics (the most likely cause)
3, your table is small, mentioned above, Oracle's optimizer that the index is not worth taking

B, solution:
1, can modify the init <SID>. Ora in OPTIMIZER_MODE this argument, turning it into Rule or Choose, restart the database. You can also use 4

Mentioned Hint.
2, delete the statistics:
SQL> analyze table table_name delete statistics;
3, the table index is small does not go right, do not tune.

Fourth, related to:

A, to see whether a table or index statistics

SELECT * FROM user_tables
WHERE table_name = <table_name>
AND num_rows is not null;

SELECT * FROM user_indexes
WHERE table_name = <table_name>
AND num_rows is not null;

B, if we will start with CBO's way, we should be timely to update the table and index statistics, so that students do not meet the actual form of implementation plan:

ANALYZE TABLE table_name COMPUTE STATISTICS;
ANALYZE INDEX index_name ESTIMATE STATISTICS;

相关文章
  • oracle optimizer RBO and CBO 2010-10-15

    During this time, do not torture oracle, silent friends, know that their oracle, do the work in the use frequently, but the configuration, optimization of the things that are in a hazy state. Taiqing say, do not quite understand, so take the time to

  • Oracle Optimizer CBO RBO 2011-08-29

    Before finishing an article on the CBO and RBO: Oracle CBO and RBO http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5709784.aspx Oracle Database optimizer (Optimizer) analysis and implementation of SQL optimization tools, which is responsible f

  • Oracle optimizer optimization method (reproduced) 2011-09-19

    Oracle is the world's leading information management software developer, database products because of their complex relationship is known. This article describes the Oracle optimizer, it is a non- Often useful tools. Before running a SQL Oracle, firs

  • ORACLE optimizer Total 3 2010-03-23

    a. RULE (rule) b. COST (based on cost) c. CHOOSE (optional) Set the default optimizer, you can file OPTIMIZER_MODE init.ora parameters on the various statements, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. You certainly are SQL sentence-level o

  • Oracle Optimizer Optimizer 2010-04-23

    Oracle SQL in the implementation of a prior statement must first analyze the execution plan, and then to carry out the implementation plan. Analysis of the implementation plan of the work statement by the optimizer (Optimizer) to complete. Different

  • Analysis of the implementation plan optimization SQL <2> ORACLE optimizer (change) 2010-05-27

    Optimizer is sometimes called the query optimizer, which is affecting database query performance because the most important part, do not think that only the SELECT statement is the query. In fact, with any WHERE conditions DML (INSERT, UPDATE, DELETE

  • に つ い て oracle optimizer 2010-11-26

    I. Purpose: 1, talk about Oracle's Optimizer and related some of the knowledge. 2, the answer is sometimes why a field of a table even when the index, when the observed number of SQL execution plan and found that the index does not take issue. 3, if

  • Understanding of the Oracle optimizer 2011-07-26

    A: SQL tuning category 1: enumerate several table connection 2: do not use third-party tools, how to view sql execution plan 3: how to use the CBO, CBO and the difference RULE 4: how to locate important (resource consuming and more) of the SQL 5: How

  • oracle optimizer に つ い て 2010-11-26

    I. PURPOSE: 1, to talk about Oracle's Optimizer and some related knowledge. 2, a table to answer why sometimes there is clearly an index of a field, when observing some of the SQL execution plan and found that the index does not take issue. 3, if you

  • oracle optimizer (rpm) 2011-04-01

    Before running a SQL Oracle, first analyze the statement execution plan, then the execution plan to execute. Analysis of statement execution plan of work is to optimize device (Optimizer) to complete. Different circumstances, there may be a variety o

  • RBO and CBO optimizer oracle 2010-10-15

    During this time, do not oracle tortured, silent friends, know that their oracle, the work is indeed often used, but configured to optimize things, in a hazy state. Taiqing say no, do not quite understand, so take your time and studies, and will see

  • Oracle SQL optimizer 34 (b) 2010-09-16

    (17) with an index to improve efficiency: Index is a conceptual part of the table, used to improve the efficiency of data retrieval, ORACLE uses a complex self-balancing B-tree structure, usually through an index query data faster than full table sca

  • Oracle SQL optimizer and share 2011-08-30

    1, oracle optimizer ============================ There are three optimizer oracle RULE COST CHOOSE Parameters set by the optimizer OPTIMIZER_MODE type, default is optional optimizer, both CHOOSE SQL> show parameter OPTIMIZER_MODE NAME TYPE VALUE ----

  • Oracle SQL Optimization 2010-02-15

    SQL optimization should be adjusted from five aspects: 1. Remove unnecessary large table full table scans 2. Cached small table full table scans 3. Inspection optimize the use of the index 4. Test the connection optimization technology 5. Cost to min

  • ORACLE EXPLAIN PLAN Summary 2010-02-22

    In the ORACLE database, need to optimize the SQL statements would need to know its implementation plan, which targeted adjustments. ORACLE access implementation plan There are several ways to sum up the following under the 1, EXPLAIN the use of Oracl

  • Oracle Performance Optimization Basics and Tips 2010-03-13

    Oracle recently learned some basic knowledge and sql performance optimization of a number of techniques, although each time a project or daily, DBA will conduct our sql audit sql written, but the understanding of relevant knowledge, enable us to more

  • Oracle Sql Optimization Notes 2010-03-16

    Note prepared by the basic Sql Minimize the use of IN operator, essentially all of the IN operator can be replaced with EXISTS. NOT IN operators do not, you can use NOT EXISTS or external link + alternatives. IN subquery in the implementation of Orac

  • (Switch) Oracle SQL Optimization 2010-02-09

    SQL optimization should be adjusted from five aspects: 1. Remove unnecessary large table full table scans 2. Cached small table full table scans 3. Inspection optimize the use of the index 4. Test the connection optimization technology 5. Cost to min

  • ORACLE SQL statement optimization summarized in 2010-02-23

    ORACLE SQL statement optimization summarized in (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name

  • rules of an oracle statement optimization 2010-02-25

    Reprinted from: IT expert network of: Peak Wong Oracle sql performance optimization and adjustment 1. The types of ORACLE optimizer ORACLE optimizer There are 3 kinds: a. RULE (rule) b. COST (based on cost) c. CHOOSE (optional) Set the default optimi