Oracle RBO.CBO Profile

2011-08-29  来源:本站原创  分类:Database  人气:162 

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 stated in ORACLE9i later version (ORACLE 10G), RBO will no longer support. Therefore chosen to CBO is an inevitable trend.

CBO and RBO as a different SQL optimizer, SQL statement execution plan for a major impact, if the existing applications from RBO to CBO transplant, you must take full account of these effects, avoid sharp decline in performance of SQL statements; However, for new applications, consider the direct use CBO, the CBO mode SQL statement preparation, analysis, implementation plan, performance testing, etc., which requires the developer more familiar with the characteristics of the CBO. The following summary points to write SQL statements in the CBO Notes:

1, RBO from ORACLE 6 version has been adopted, with the use of a set of strict rules, as long as you follow it to write SQL statements, regardless of how the data contents of the table, it will not affect your "implementation plan" that is that the data is not "sensitive"; CBO calculating possible "implementation plan" and "price", ie cost, choose from the lowest cost solution, as the actual running program. The "implementation plan" is calculated according to cost, data-dependent statistical distribution of data in the table, ORACLE database itself is not clear that the statistical distribution, we must analyze the table and related indexes (using the ANALYZE command) in order to gather CBO the required data.

2, using the CBO, the write SQL statements, you do not consider the "FROM" clause of the table or view behind the order and "WHERE" clause of the conditions behind the order; ORACLE version 7 has been used since many new technologies are based on CBO , such as star-connected arrangement of queries, hash join queries, functions indexes, and parallel inquiries.

3, in general, CBO selected the "implementation plan" are no more than RBO's "implementation plan" poor, but relatively speaking, CBO did not request the programmer RBO so harsh, in order to save the programmers from multiple possible "implementation plan", select an optimal solution and the time spent debugging, but in some cases there will be problems. A more typical problem: sometimes, that there is an index built out, but apparently did not use the query process-related index, causing the query takes a long process, taking up huge resources, then you need to carefully analyze the execution plan, find out why. For example, you can see the connection sequence is allowed to use the relevant index. Assume table emp has an index on the deptno column, table dept is no index on the deptno column, WHERE statement has emp.deptno = dept.deptno conditions. In doing NL connection, emp as appearance, was first visited, the connection mechanism causes the appearance of the data access method is a full table scan, emp.deptno obviously do not have access on the index, the index up to do its full scan or index fast full scan.

4, if a statement execution plan using the RBO does better than the CBO, you can add "rule" prompt, mandatory use of RBO.

5, using the CBO, SQL statements "FROM" clause after the table, must all be analyzed using the ANALYZE command, if the "FROM" clause is the back view, this view of the underlying table, it must all be analyzed using the ANALYZE command over; otherwise, ORACLE will execute this SQL statement, you automatically analyze the ANALYZE command, which will cause the SQL statement is executed very very slow.

6, using the CBO, SQL statements "FROM" clause of the number of tables should not be behind too much, because the CBO in the choice of table join order, would "FROM" clause factorial operation behind the table, choose the best a connection order. If the "FROM" clause of the table after six, then the choice of join order is 6 * 5 * 4 * 3 * 2 * 1 = 720 kinds, CBO choose one, and if the "FROM" clause after 12 tables, then the choice of join order is 12 * 11 * 10 * 9 * 8 * 7 * 6 * 5 * 4 * 3 * 2 * 1 = 479,001,600 kinds, it is conceivable to choose one, will consume much CPU time? If you really want to access many of the table, it is best to use the ORDER hint to force use of "FROM" clause of the fixed table access order.

7, using the CBO, SQL statements can not refer to system data dictionary table or view, because the system data dictionary tables have not been analyzed, could lead to very poor "implementation plan." But do not do without analysis of the data dictionary table, or it may lead to a deadlock, or a serious decline in system performance.

8, using the CBO, we should pay attention to see what type of table using a connection. ORACLE total of Sort Merge Join (SMJ), Hash Join (HJ) and the Nested Loop Join (NL). CBO sometimes emphasis on SMJ and HJ, but in the OLTP system, NL is generally better, because it is efficient use of the index. In the two-table joins, and the target table columns in an index built on, only to effectively use Nested Loop to the index. SMJ built even if the relevant index on the column, can only exist because of the index, to avoid the data sorting process. HJ HASH operations due to be done, there is an index of the speed of data queries had little effect.

9, using the CBO must ensure that the relevant index for the table and collect adequate statistical data. Data often add, delete, change the table the best on a regular basis to analyze tables and indexes, SQL statements can "analyze table xxx compute statistics for all indexes;" ORACLE mastered fully reflect the actual statistics, made possible the right choice.

10, using the CBO, we should pay attention to the value of the indexed data in the field of distribution, will affect the SQL statement execution plans. For example: the table emp, a total of one million rows of data, but the emp.deptno column, only four different data values, such as 10,20,30,40. Although there are many emp rows, ORACLE found the default table column value is evenly distributed in all rows, that is 250,000 each deptno value of each corresponding data line. Assuming SQL search conditions DEPTNO = 10, use the index on the deptno column data search efficiency is often higher than full-table scan, ORACLE course of the index "blind", that the selectivity of the index is not high.

We consider another situation, if one million rows of data is not actually deptno value in four equally among which 99 million lines correspond to the line corresponding to the value of the value of 10,5000 20,3000 30,2000 row line corresponding to the value of the corresponding value of 40 . In such a data distribution pattern on the outside than the other deptno is 10 the value of search, no doubt, if the index can be applied, then the efficiency will be much higher. We can use a separate analysis of the index column, or columns with the analyze statements to establish the histogram, the column to collect enough statistical data to ORACLE in the search for higher value can be used selectively on the index.

相关文章
  • 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 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 user privileges. profile and role management 2011-09-07

    oracle user permissions, profile and role management In the oracle database, database users and roles and permissions are closely related, the correct grasp of the user and role management for the database installation is very important, this paper s

  • 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和内存.优化器在判断

  • oracle pl sql profile 2011-09-26

    172.16.0.2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.0.2) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

  • 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

  • [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

  • Oracle Profile use Xiangjie 2010-05-23

    Any restrictions on the user oracle in the use of resources (including the connect time and idle time, etc.) You can create different resource constraints, it is best to ALTER SYSTEM SET RESOURCE_LIMIT = TRUE Written in the init file: RESOURCE_LIMIT

  • Oracle Profile Detailed use 2011-05-24

    Oracle Profile Detailed one with the purpose of: Oracle System user profile can be used to limit the use of database resources, use the Create Profile command to create a Profile, use it to implement restrictions on the use of database resources, if

  • 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 Index Analysis and Comparison (2) 2010-07-12

    26.5 Schedule (when the index does not work) First of all, to declare two knowledge points: (1) RBO & CBO. There are two implementation of the Oracle optimizer, one is RBO (Rule Based Optimizer) rule-based optimizer, this optimizer is based on the wo

  • Oracle Index and create various types of indexing described method [transfer] 2010-12-02

    Overview of the index in a variety of relational database systems are important part of its search for improving the speed of data play a vital role. In Oracle, the index base is divided into the following: B * Tree index, inverted index, descending

  • Oracle Index and create a way to introduce various types of index [turn] 2010-12-02

    Overview of the index in a variety of relational database systems are an important part of its search for improving the speed of data play a vital role. In Oracle, the index base is divided into the following categories: B * Tree index, inverted inde

  • Oracle Parallel Query (OPQ) 2010-12-20

    Oracle Parallel Query (OPQ) can be a SQL statement into multiple pieces (chunks), then the CPU alone on multiple process (sub query) to run in parallel. Each sub-query also read a piece of a large table. After completion of all sub-queries, Oracle pa

  • Analysis and Comparison of Oracle indexes 2011-02-20

    Analysis and Comparison of Oracle indexes ORACLE experts Hai wrote, even turn to learn Kazakh, this thank you handsome! Chapter 26 Analysis and Comparison of Oracle indexes 26.1 Overview <br /> index in a variety of relational database systems are a

  • 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) ================================================== ===

  • Oracle 分析及动态采样 2012-03-09

    之前在说Oracle Optimizer中的CBO时讲到,当表没有做分析的时候,Oracle 会使用动态采样来收集统计信息. 获取准确的段对象(表,表分区,索引等)的分析数据,是CBO存在的基石,CBO的机制就是收集尽可能多的对象信息和系统信息,通过对这些信息进行计算,分析,评估,最终得出一个成本最低的执行计划. 所以对于CBO,数据段的分析就非常重要. Oracle Optimizer CBO RBO http://blog.csdn.net/tianlesoftware/archive/20

  • Oracle索引分析与比较 2012-03-10

    索引在各种关系型数据库系统中都是举足轻重的组成部分,其对于提高检索数据的速度起至关重要的作用.在Oracle中,索引基本分为以下几种:B*Tree索引,反向索引,降序索引,位图索引,函数索引,interMedia全文索引等.本文主要就前6种索引进行分析,由于interMedia全文索引涉及的内容可以单独写一篇文章,所以不在此对其做分析. 首先给出各种索引的简要解释: b*tree index:几乎所有的关系型数据库中都有b*tree类型索引,也是被最多使用的.其树结构与二叉树比较类似,根据rid

  • oracle installation on linux system requirements 2010-02-27

    Requirements for Installing Oracle 10gR2 RDBMS on OEL 4 update 5 on AMD64/EM64T 1. Hardware: ========== * Minimum Hardware Requirements - 1 GB (1024 MB) of physical RAM - The following table describes the relationship between installed RAM and the co