Oracle Table Analysis, CBO RBO detailed __oracle

Oracle CBO RBO Detailed

url:http://blog.163.com/love-love-l/blog/static/21078304201010175035270/

Http://blog.csdn.net/cnham/archive/2010/04/13/5482318.aspx

First part

What is Table analysis. Simply put, it is the information that collects tables and indexes, based on which the CBO determines the best execution path for SQL. By analyzing the tables, you can generate some statistical information that Oracle's optimizer can optimize.

Using Analyze or Dbms_stats packet analysis, analysis can be used to determine the use of the table, such as row migration situation. Look at this table before, mainly the data of each column.

SELECT * from Dba_tables where table_name= ' TEST11 ';
Analyze table test11 compute statistics;
Query the table dba_tables and you'll find the problem.
SELECT * from Dba_tables where table_name= ' TEST11 '; (Note: Table name to capitalize)

        Oracle's online documentation describes the ANALYZE:
Use the ANALYZE statement to collect Non-optimizer statistics, for example, to:
   -Use the Analyze feature to collect statistics that are not optimized, such as:
Collect or Delete statistics about an index or index partition, table or table partition,
   --Aggregate or delete indexes or index partitions, tables or table partitions
index-organized table, cluster, or scalar the object attribute.
   -Statistics such as index table, string, or cursor target properties
Validate The structure of an index or index partition, table or table par Tition, index-organized table,
   --increases the validity of the following structures: indexed or indexed partitions, table or table partitions, indexed tables
cluster, or object reference ( REF).
   --string, target reference
identify migrated and chained rows of a table or cluster.
   -Identifies a table or string of columns that have been moved or locked.

The role of Dbms_stats is mainly to replace the Analyze collection of statistical information, and in this respect has done a considerable degree of enhancement.
Take your analyze table ABC compute statistics; This example, the generated statistics will exist in the User_tables view, see
SELECT * from User_tables where table_name= ' ABC ';

Look at the Num_rows,blocks,avg_space,avg_row_len columns and you'll see that this is the change. The purpose of collecting statistics is to make the CBO implementation plan more accurate.

After analyzing the table, you will find that many of the previous column values in the Dba_tables view are empty and are now starting to have data.
This data is what you get from the analysis table. As to what the use of these data, depends on your initialization parameters Optimal_mode how to set, details please check the relevant documents. Analyzing the statistics for the Update table may result in changes to the execution plan.

Part II

[Q] How to parse a table or index.
[A] command-line methods can take the analyze command
such as analyze table TableName compute statistics;
Analyze Index|cluster indexname estimate statistics;
ANALYZE table tablename COMPUTE STATISTICS
for table
to all [local] INDEXES
to all [INDEXED] COLUMNS;
ANALYZE table tablename DELETE STATISTICS
ANALYZE table tablename VALIDATE REF UPDATE
ANALYZE table tablename TE STRUCTURE
[cascade]|[ INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [into TableName]
and so on.
If you want to analyze an entire user or database, you can also use a toolkit that can be analyzed in parallel
Dbms_utility (8i previous toolkit)
Dbms_stats (available after 8i kits)
such as
Dbms_ Stats.gather_schema_stats (user,estimate_percent=>100,cascade=> TRUE);
Dbms_stats.gather_table_stats (user,tablename,degree => 4,cascade => true);

This is a summary of the commands and toolkits
1. For partitioned tables, it is recommended to use Dbms_stats instead of using analyze statements.
A) can be done in parallel, for multiple users, multiple table
b) The data for the entire partitioned table and the data for a single partition can be obtained.
c) can be compute at different levels Statistics: Single partition, sub partition, whole table, all partitions
D) can be poured out of statistical information
E users can collect statistical information automatically
2, Dbms_stats's shortcomings
A) cannot validate Structure
b) can not collect chained ROWS, can not collect cluster table information, these two still need to use the Analyze statement.
c) Dbms_stats The index is not analyze by default because the default cascade is false and needs to be manually specified as True
3, for Oracle 9 inside the external table,analyze can not be used, can only use Dbms_stats to collect information.

Questions about the dbms_stats.gather_table_stats.
I used dbms_stats.gather_table_stats to analyze a table, and my CBO now uses its analysis to process a table, but I'm not using the analysis as it is now.
The reason is that after using this analysis, many of my query efficiency suddenly greatly reduced, and the CPU occupancy rate soared, I want to revert to the original, or the application is very dangerous AH
And ask everybody for help.
Analyze table table_name Delete statistics;
Delete the analyzed statistics. You should be able to use analyze table tablename Delete statistics;
Rarely used dbms_stats, so unexpectedly did not expect to use Delete_table_stats to deletes system statistics. It's best to use this way.
SELECT table_name, last_analyzed from Dba_tables WHERE last_analyzed are not NULL;
Check to see if there are any parsed tables

Part III CBO

Rule Based Optimizer (RBO) based on rules
Cost Based Optimizer (CBO) based on costs, or statistical information

ORACLE provides the CBO, Rbo two kinds of SQL optimizer. The CBO was introduced in ORACLE7, but matured in oracle8i. Oracle has explicitly stated that in the Oracle9i version (Oracle 10G), RBO will no longer support it. Therefore, the choice of CBO is an inevitable trend.

CBO and RBO as different SQL optimizer, have a significant impact on the execution plan for SQL statements, and if you want to migrate existing applications from RBO to CBO, you must take these effects into account and avoid a sharp decline in SQL statement performance, but for new applications, consider using the CBO directly, Executing SQL statements, analyzing execution plans, performance tests, etc. in CBO mode requires developers to be familiar with the characteristics of the CBO.
Here are some tips for writing SQL statements under the CBO:

1, Rbo since Oracle 6 edition has been adopted, there is a strict rule of use, so long as you follow it to write SQL statements, no matter what the contents of the data table, it will not affect your "execution plan", that is, not "sensitive" to the data, the CBO calculates a variety of possible "implementation Plan" of the "price", Cost, from which the lowest cost of the scheme, as the actual operation of the program. The calculation of the cost of each "execution plan" depends on the statistical distribution of the data in the data table, the Oracle database itself is not clear about the statistical distribution, and the tables and associated indexes (using the Analyze command) must be analyzed to gather the data required by the CBO.

2. When using the CBO, when you write an SQL statement, you do not have to consider the order of the table or view following the "from" clause and the order of the conditions following the "WHERE" clause; many of the new technologies that Oracle has adopted since version 7 are based on CBO, such as star-linked array queries, hash-join queries, function and parallel queries.

3. In general, the CBO chooses the "Implementation plan" is not worse than the Rbo "execution plan", and relatively, the CBO's requirements for programmers is not rbo so harsh, saving the programmer to choose from a number of possible "execution plan" to select an optimal solution to spend the debugging time, But there are also problems in some situations.
Some of the more typical problems are: Sometimes it shows that there are indexes in Ming Dynasty, but the query process obviously does not use the relevant indexes, which results in a lengthy and resource-intensive query process that requires careful analysis of the execution plan to find out why. For example, you can see whether the connection order permits the use of related indexes. Assuming that there is an index on the Deptno column of the table EMP, there is no index on the column deptno the table dept and the WHERE statement has emp.deptno=dept.deptno conditions. In the NL connection, the EMP as the appearance, first accessed, because of the connection mechanism, the appearance of the data access is a full table scan, the index on the Emp.deptno is obviously not used, at most on its index full scan or index fast full scan.

4, if a statement using Rbo execution plan is better than the CBO, you can by adding "rule" hint, forced to use Rbo.

5. When using the CBO, the table after the SQL statement "from" clause must all be parsed using the Analyze command, and if the "from" clause is followed by a view, the underlying table of the view must also be parsed using the analyze command; otherwise, ORACLE The Analyze command analysis is performed automatically before executing this SQL statement, which greatly results in extremely slow execution of the SQL statement.

6, the use of the CBO, the SQL statement "from" clause after the number of tables should not be too much, because the CBO in the selection of table join order, the "from" clause after the table to the factorial operation, select the best connection order. If there are 6 tables after the "from" clause, the optional connection order is 6*5*4*3*2*1 = 720, and the CBO chooses one, and if there are 12 tables after the "from" clause, the optional connection order is 2*11*10*9*8*7*6*5*4*3*2*1 = 479001600 species, you can imagine how much CPU time it would take to select one from it. If you really want to access many tables, it is a good idea to use the order prompt to enforce a fixed access sequence from the "from" clause.


7. When using the CBO, the System data dictionary table or view cannot be referenced in SQL statements because the system data dictionary tables are not parsed and can result in extremely poor execution plans. However, do not arbitrarily analyze the data dictionary table, otherwise it may lead to deadlock, or the system performance seriously degraded.

8, when using the CBO, pay attention to see what type of table to join the way. Oracle's total sort Merge join (SMJ), Hash join (HJ), and nested Loop join (NL). The CBO sometimes tends to focus on SMJ and HJ, but in OLTP systems, NL is generally better because it uses indexes efficiently.
When two tables are connected and an index is built on the target column of the inner table, only the nested loop can effectively utilize the index. SMJ even if indexed on related columns, the data ordering process can only be avoided by the presence of indexes. Because the HJ has to do the hash operation, the existence of the index has little effect on the speed of the data query.

9. When using the CBO, it is necessary to ensure that sufficient statistical data are collected for the table and related indexes. The table and index are best regularly analyzed by the tables and indexes that are often added, deleted, and modified by the data, and SQL statements "analyze table XXX compute statistics for all indexes are available." Oracle has the ability to fully reflect the actual statistical data to make the right choice.

10. When using the CBO, you should pay attention to the data distribution of the values of the fields being indexed, which will affect the execution plan of the SQL statement. For example: The table EMP has 1 million rows of data, but the Emp.deptno column has only 4 different values, such as 10, 20, 30, 40. Although there are many EMP data rows, the value of the columns in the Oracle Default determination table is evenly distributed across all data rows, i.e. each DEPTNO value has 250,000 rows of data corresponding to it. Assuming that the SQL search conditions deptno=10, using the index on the Eptno column for data search efficiency, often not higher than the full table scan, Oracle naturally to the index "blind", that the index is not high selectivity.

We consider the other case, if the 1 million data row is not actually among 4 kinds of deptno values evenly allocated, of which 990,000 rows correspond to the value 10,5000 row corresponding value 20,3000 row corresponding value 30,2000 row corresponding value 40. In this data distribution pattern when searching for other deptno values except the value 10, there is no doubt that if the index can be applied, the efficiency will be much higher. We can use the indexed column for a separate analysis, or use the Analyze statement to establish a histogram of the column, collect enough statistics on the column, so that Oracle in search selectivity higher values can be indexed.

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Cost-based optimizer (CBO)
1. Background background

To execute any one of the SQL statements, Oracle has to lead an execution plan (execution).
It is essentially how Oracle retrieves the execution plan for data that meets the requirements of a given SQL statement.
Oracle7 and Oracle8 have two optimizer types that can export execution plans for SQL statements:

   -rule-based optimizer (RBO)
inherits from Oracle6, which uses a series of strict rules to decide Set the execution plan for each SQL statement. If you know these rules,
you can construct an SQL query that accesses the data in a specified way. The contents of the table have no effect on the execution plan.
The optimizer is no longer being enhanced, so many Oracle8 features are not available.

   -cost-based optimizer (CBO)
introduced from Oracle7, which tries to find the lowest-cost Access data method for maximum throughput or the fastest initial response time. The
calculates the cost of using a different execution plan and selects one with the lowest cost. Statistics about the contents of a table's data are used to determine the execution plan.
Fundamental Points Basic points
for each SQL statement, there are a number of possible execution plans. "Best Plan" is always the "best plan", no matter how it arrives.
The best plan can be two meanings:
1 This plan uses the smallest resources to handle all the lines involved in this statement. [Called All_rows]
2 This plan returns the first line of the statement in the shortest possible time. [Called First_rows] The
CBO does not understand the relevant features of the application, nor does it fully understand the impact of the complex relationships between the relational tables. Only a limited amount of information can be used to determine the best plan. The
CBO determines the best plan by calculating the estimated cost of different execution scenarios and chooses the lowest cost plan. Because this is related to the cost of the assumption that the
selected plan is not necessarily the best plan. This situation is often reported as a bug to Oracle technical support because the CBO does not choose the best plan for a given scenario.
People can usually verify that a given input statistic is valid and the default "cost" is involved. The selected plan is calculated as the best plan, although it is not.
No matter how the CBO improves, there is always a choice of plans that are not optimal. Therefore, you must always prepare the optimization statements.

The RBO function is no longer enhanced. This means that some implementation plans are only valid for the CBO. However, RBO will continue to exist in Oracle 8.

Before you Continue before you go on
~~~~~~~~~~~~~~~~~~~
It is not recommended that you use the CBO in Oracle releases 7.0.X.
The information in this article applies to Oracle releases more than 7.1 (including Oracle 8.0).
Base Statistics Basic Statistics
~~~~~~~~~~~~~~~

In order to give the CBO the most information (the opportunity to choose a good execution plan), you must analyze all the tables that will be queried.
The analyze operation with the estimate option can produce incorrect results for some tables, especially those with smaller sampling tables.
This is not a bug, but a feature of each statistical sampling method. If the selected sample does not represent the entire dataset, you cannot expect to produce the correct statistics.
In Oracle 7.1 and 7.2, the values of the columns are assumed to be evenly distributed. This is an important limitation in these editions, and full and accurate statistics cannot be noted
The distribution of actual data. This limit is partially resolved in Oracle release 7.3 and can hold distribution information for column values
-but these additional information may not actually help some types of queries, see the following sections on the bind Variables considerations.

When considering using analyze, consider the following important issues:
-The Analyze of a table with an index will be analyzed for its associated index. (It is possible to parse a table in Oracle 7.3 without parsing the index.) ）
-If you analyze a table ... Estimate analysis, then it is advisable to do analyze compute analysis on its associated index.
This ensures that the statistics for the indexed fields are accurate.
-Analysis index not to temporary tablespace
-If an index is parsed without parsing its base table, the CBO will not be selected on this single basis.
-If you need to use estimate-estimates (for example, due to time limitations), it is recommended that you perform ANALYZE on several different sample sizes ... Estimate,
To determine the desired sample size for each object. The overall goal is to find a sample size that produces accurate statistics in the shortest amount of time. The better starting point is 10%-15%.
-More than 50% analyze ... Estimate will cause/become analyze ... COMPUTE.
-7.1.6 Previous version of the Analyze ... When estimate, there will be Ora 600 wrong.
Optimizer Goal/mode optimization goals and patterns

What kind of optimizer and how it operates is determined by the following factors:
Type Object
-some object types are not known by rule based optimizations. For example: The index Table (IOT) Rbo does not know at all, in the query involving IOT will automatically use the CBO.
Parallel Degree > 1 The degree of parallelism on a table is greater than 1
-If a table in the query has a greater degree of parallelism than one, the CBO will be adopted regardless of whether the hint, optimizer_mode, or optimizer_goal value is rule.
Applies to Oracle over 7.3.
-The CBO is also used in Oracle 8.0.5 and Oracle 8.1.5 releases if any index has a parallelism of more than 1. Applies only to Oracle 8.0.5 and Oracle 8.1.5.
Hints Tips
-Any hint other than rule will lead to the use of the CBO. It is important that hint not be turned off by any parameters.
Session level Sessions Optimizer_goal
-If the above conditions are not given, the selection of the optimizer is determined by the session-level parameter optimizer_goal. If one of the above conditions is given, the optimizer_goal will not work.
If Optimizer_goal is set to rule, the RBO is used, regardless of the statistics of any table.
If the optimizer_goal is set to choose, All_rows will be selected for any query that has a table that has been parsed.
Init.ora Optimizer_mode Parameters
-The default setting for the session-level Optimizer_goal parameter is the value of the Optimizer_mode in the Init.ora file.
Pl/sql blocks (including anonymous blocks and stored procedures) should use an explicit hint (hint) to determine the actual optimization method. Without specifying a hint, parallel, or "cbo-only" object,
Pl/sql block in the SQL statement used by the optimizer, see below:
Init. ORA Optimizer_mode
Mode used in Plsql
Rule
Rule
CHOOSE
All_rows
All_rows
All_rows
First_rows
All_rows
Summary Optimizer Mode: A summary of the optimization model
~~~~~~~~~~~~~~~~~~~~~~~
For the above article let us clearly determine what kind of optimizer to use some things, summed up as follows:
Description
Table Statistics
Mode Used
Non-rbo Object (Eg:iot)
N/A
#1
Parallelism > 1
N/A
#1
Rule hint
N/A
Rule
All_rows hint
N/A
All_rows
First_rows hint
N/A
First_rows
*other Hint
N/A
#1
Optimizer_goal=rule
N/A
Rule
Optimizer_goal=all_rows
N/A
All_rows
Optimizer_goal=first_rows
N/A
First_rows
Optimizer_goal=choose
NO
Rule
Optimizer_goal=choose
YES
All_rows
#1 unless Optimizer_goal is set to First_rows, All_rows is used. In Plsql, All_rows will be used.
*other Hint Other hints mean a hint other than rule, all_rows or first_rows.
General Optimizer Notes Optimizer common considerations
~~~~~~~~~~~~~~~~~~~~~~
When looking at the optimizer problem, consider the following points:
-All_rows is prone to full table scans (scans).
-First_rows tends to index access (index access).
-CBO uses all_rows to calculate costs by default.
-Before Oracle 7.3, the CBO would not adjust costs to cater to parallel queries (Parallel Queries).
-Prior to Oracle 7.3, the CBO considered that the value of the field was evenly distributed between the maximum and the minimum, after which the histogram statistics could be stored according to the request.
-All cases equal to Rbo, in the order in which the table is in the FROM clause from right to left (driving order).
The CBO determines the connection order (join orders) based on the cost that is derived from the statistics collected.
If there are no statistics, the CBO will be driven in the order of the table in the FROM clause left to right-driving order, just as opposed to Rbo.
-the CBO will use analyze information in conjunction with high level information for the current table. Therefore, the execution plan for a statement may vary by time.
-NOTE: Truncate resets the table's "High water level", but does not modify the table's statistics, but rather leaves the old CBO information for the table.
-When performing various connections, some connection combinations are excluded to reduce the overall time spent in determining an execution plan.
In short, each connection order is compared to the best one so far, and it is clear that some of the optimized scenarios will be excluded.
Problem SQL statements Problem SQL statement

——————————————————————————————

Show parameter Optimizer_mode-looking at Oracle's mode, the default setting for Oracle V7 should be "choose", that is, if you select the CBO for the parsed table query, select Rbo. If this parameter is set to rule , you choose Rbo, regardless of whether the table is analyzed, unless you force it in the statement using hint.

Analyze table XXX Compute statistics for all indexes-analyzes tables.


Oracle's optimizer has two optimizations, namely, rule-based optimization (rule-based optimization, short rbo) and cost based optimization (cost-based optimization, referred to as CBO), In Oracle8 and later versions, the Oracle Strong column recommends the CBO approach

Rbo: When the optimizer analyzes SQL statements, it follows some rules that are predetermined within Oracle. For example, we usually go to the index when a column in a WHERE clause has an index.

CBO way: It is to see the cost of the statement, where the cost is mainly CPU and memory. When the optimizer determines whether to use this method, the main reference is the statistics of tables and indexes. Statistics give information about the size of the table, the number of rows, the length of each line, and so on. These statistics are not at first in the library, is done after the analyze, many of the time delay statistics will cause the optimizer to make a wrong execution plan, because some should update the information in a timely manner.

Note: Indexing is not necessarily excellent, such as a table with only two rows of data, one IO can complete the whole table retrieval, and then go to the index when the need for two times Io, when the full table scan (scan) is the best

Optimization modes include rule, Choose, first rows, all rows four ways:

Rule: a rule-based approach.

Choolse: This is the way Oracle is used by default. Refers to when a table or index has statistics, the way of the CBO, if the table or index does not have statistical information, the table is not particularly small, and the corresponding columns indexed, then go to the index, walk Rbo way.

First rows: It's similar to the Choose approach, and the difference is that when a table has statistics, it will be the quickest way to return the initial rows of the query, reducing the response time overall.

All rows: That is what we mean by cost, when a table has statistics, it returns all the rows of the table in the quickest way, increasing the throughput of the query as a whole. There is no statistical information on the way to go rbo.

Set which optimization mode to choose:

A, instance level we can set in the Initsid.ora file optimizer_mode=rule/choose/first_rows/all_rows if not set Optimizer_ The mode parameter defaults to the Choose method.
B, the sessions level is set by alter session set Optimizer_mode=rule/choose/first_rows/all_rows.
C, the statement level with hint (/*+ ... *) to set

Why a field in a table is indexed, but the execution plan does not go to the index.

1, the optimization mode is all_rows way
2, the table has been analyze, has the statistical information
3, the table is very small, as mentioned above, the Oracle optimizer does not consider it worthwhile to go to the index.

first, how to use the Costbased optimizer to optimize query operations.

Oracle provides both a cost based (costbased) and rule-based (rulebased) optimizer, referred to as the CBO and Rbo, to determine the execution plan for the query operation. The Costbased Optimizer calculates the overhead of various execution plans and then selects the lowest cost execution plan. You can choose to use the CBO using the following methods:

1. Set the parameter Optimizer_mode=choose in the Init.ora file;

2. Set Optimizer_goal=first_rows or All_rows at the session level.

3, the use of hint in query statements, including choose, All_rows, First_rows and so on.

Second, why my plan of implementation is not the best.

The CBO relies on some statistical information on the table to select the lowest-cost execution plan, and when the statistics are inaccurate, the resulting plan may not be optimal. Therefore, the table should be analyzed and counted in time using the Analyze command.

third, my query last week's performance is very good, why now the query speed is very slow.

This is due to changes in the execution plan, and the following factors will change an execution plan:

1, INIT. The parameter Optimizer_mode in the Ora file is changed;

2, the table defines or changes the parallel query degree;

3, using the Analyze command to analyze the table, and the use of the estimate method, this way to choose a different percentage can produce different analysis results;

4, the Db_file_multiblock_read_count parameter is modified, 5, the Sort_area_size parameter is modified.