oracle of RBO / CBO optimizer

2011-09-02  来源:本站原创  分类:Database  人气:104 

Oracle's optimizer has two optimization methods, namely, rule-based optimization approach (Rule-Based Optimization, referred to as RBO) and cost-based optimization method (Cost-Based Optimization, referred to as CBO), in Oracle8 and later, Oracle strongly recommends using CBO's way

RBO mode: optimizer in the analysis of SQL statements, followed by a predetermined number of the Oracle internal rules. For example, our common, when a where clause in an index to go with the index.

CBO way: it is to look at the cost of the statement (Cost), where the cost mainly refers to the Cpu and memory. Optimizer to determine whether this way, the main reference was to the table and index statistics. Statistics given the size of the table, a small line, the length of each line and other information. At first, these statistics are not in the database, do analyze after the emergence of many of Hou-date statistics will make the optimizer execution plan to make a mistake, because these should be promptly update this information.

Note: take the index is not necessarily superior, such as a table with only two rows of data, one can complete the whole IO table to retrieve, index at a time when you need to twice take the IO, then a full table scan (full table scan) is the best

Optimization models, including Rule, Choose, First rows, All rows in four ways:

Rule: rule-based approach.

Choolse: By default Oracle is used in this way. Means that when there is a table or index or statistics, the CBO way to go, if not the table or index statistics, tables are not particularly small, and the corresponding columns are indexed, then left the index, take the RBO's way.

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, in general, reduce the response time.

All Rows: Cost is the way we said, 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. RBO no statistical information is the way to go.

Choice of which set optimization mode:

A, Instance-level file can be set by initSID.ora OPTIMIZER_MODE = RULE / CHOOSE / FIRST_ROWS / ALL_ROWS If you do not set the parameters OPTIMIZER_MODE Choose the default method is used.
B, Sessions level by ALTER SESSION SET OPTIMIZER_MODE = RULE / CHOOSE / FIRST_ROWS / ALL_ROWS set.
C, the statement level with Hint (/ * + ... * /) to set

Why there is obviously a field table index, but the implementation plan did not take the index?

1, the optimization model is the way all_rows
2, the table for over analyze, there are statistics
3, the table is small, the above mentioned, Oracle's optimizer that is not worth taking the index.

相关文章
  • oracle of RBO / CBO optimizer 2011-09-02

    Oracle's optimizer has two optimization methods, namely, rule-based optimization approach (Rule-Based Optimization, referred to as RBO) and cost-based optimization method (Cost-Based Optimization, referred to as CBO), in Oracle8 and later, Oracle str

  • 详介oracle的RBO/CBO优化器 2012-03-09

    Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO),在Oracle8及以后的版本,Oracle强列推荐用CBO的方式 RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则.比如我们常见的,当一个where子句中的一列有索引时去走索引. CBO方式:它是看语句的代价(Cost),这里的代价主要指Cpu和内存.优化器在判断

  • 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 RBO.CBO Profile 2011-08-29

    Rule Based Optimizer (RBO) rule-based Cost Based Optimizer (CBO) cost-based, or statistical information about ORACLE provides CBO, RBO two kinds of SQL optimizer. CBO in ORACLE7 introduction, but in ORACLE8i in to mature. ORACLE has been explicitly s

  • Oracle的RBO和CBO详细介绍和优化模式设置方法 2013-10-20

    这篇文章主要介绍了Oracle的RBO和CBO详细介绍和优化模式设置方法,RBO即基于规则的优化方式(Rule-Based Optimization),CBO即基于代价的优化方式(Cost-Based Optimization),需要的朋友可以参考下 Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO),在Oracle8及以后的版本,Oracle

  • oracle CBO optimizer learning 2010-08-29

    In CBO's optimization model, we can control the optimization model using optimizer_mode parameters. There are two modes, one is ALL_ROWS mode, the other is FIRST_ROWS mode. ALL_ROWS mode for the scene: I hope that as soon as possible optimization pro

  • Oracle的优化器(Optimizer) 2012-03-09

    导读: Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行.分析语句的执行计划的工作是由优化器(Optimizer)来完成的.不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的.相信你一定会用Pl/sql Developer.Toad等工具去看一个语句的执行计划,不过你可能对Rule.Choose.First rows.All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,

  • 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: implementation plan: access path connection table, suggesting Statistical information bind variables 2011-09-09

    SQL execution: Analysis: Hard analysis, soft analysis Implementation Plan: access path (access path), table join (table join), statistics (statistics), bind variable (bind variable), tips (hints) ================================================== ===

  • Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary) 2010-06-30

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentiment among many. In the DBA's daily work, to adjust individual performance to a less challenging when the SQL statement of work. The key lies

  • 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

  • [Transfer] oracle database optimizer CBO and RBO 2011-09-14

    ORACLE provides CBO, RBO two kinds of SQL optimizer. CBO in ORACLE7 introduction, but in ORACLE8i in to mature. ORACLE has been explicitly stated in ORACLE9i later version (ORACLE 10G), RBO will no longer support. Therefore chosen to CBO is an inevit

  • CBO, RBO application in ORACLE 2011-01-13

    ORACLE provides CBO, RBO two kinds of SQL optimizer. CBO in ORACLE7 introduced in ORACLE8i in to mature. ORACLE has been explicitly stated in ORACLE9i Later versions (ORACLE 10G), RBO is no longer supported. Therefore chosen to CBO is an inevitable t

  • Oracle's optimizer (Optimizer) (CBO optimization) to share 2011-09-01

    Oracle's optimizer (Optimizer) (CBO optimization) Before running a SQL Oracle, first analyze the statement execution plan, then the execution plan to execute. Analysis of statement execution plan by the optimizer work (Optimizer) to complete. Differe

  • 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

  • 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

  • RBO began to no longer support Oracle 10g 2011-02-23

    Oracle 10g began to no longer support the RBO (rule-based optimizer), but only support CBO (cost-based optimizer)

  • 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

  • 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