Oracle: implementation plan: access path connection table, suggesting Statistical information bind variables

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

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)

================================================== ===============================

Resolution:

================================================== ===============================

Implementation Plan:

Chapter 1 Overview Chapter 2 performance tuning application designed to efficiently SQL statements in Chapter 3 Chapter 4 dealing with the process of ORACLE ORACLE optimizer Chapter 5 the implementation plan to visit the path (method) - access path
How to generate the connection between the table plan how to perform analysis of the implementation plan to intervene in the execution plan - - hints tips using specific case studies in Chapter 6 Other Considerations Appendix

Chapter 1 Performance Tuning Overview

Oracle database is highly adjustable database products. This chapter describes the adjustment process and those staff should adjust the Oracle server, and associated with the adjustment of the operating system hardware and software. This chapter includes the following:
l Who adjustment system?
l When adjusted?
l Establish an effective adjustment of the target
l in the design and development of the adjustment when
l to adjust the product system
l System Monitoring Products

Who adjustment system:
In order to effectively adjust the system, certain categories of persons involved must exchange information and adjust to the system, such as:
l application design must convey the application system design, making everyone aware of the application data flow.
l Application developers must communicate their choice of implementation strategy, the process of making the statement to adjust quickly and easily identify problems and suspicious application module SQL statements.
l database management system must carefully monitor their activities and providing information, making the unusual system performance can be obtained quickly identified and corrected.
l hardware / software management must communicate the system's hardware, software configuration and provide them information so that stakeholders can effectively design and manage the system.

In short, everyone involved with the system are in the process of adjustment to play some role, as mentioned above, those who convey the characteristics of the system and provide their information, can be relatively easy to adjust and faster .

Unfortunately, the de facto result is: the database administrator to adjust bear the full or major responsibility. However, database administrators seldom have the appropriate systems information, and, in many cases, database administrators are often involved in the implementation stage database, which is working to adjust the many negative effects, because at the design stage defects can not be adjusted by the DBA has been solved, and the design phase of the defects often cause a great impact on database performance.
In fact, real mature development environment, developers write the code as a pure staff, with minimal impact on performance, this time most of the work done by the application designer, and database administrators tend to demand management in the early stage intervention, for the designers to provide the necessary technical support.
Is not a database administrator to adjust the patent, on the contrary should be designed and most developers work, which requires designers and developers need a database of specific knowledge, so as to form an efficient team, but in fact often not the case .

When adjusted?
Most people think that poor performance when the user feels when adjusted, the adjustment process which is used to adjust some of the most effective strategy is often too late. At this point, if you do not want to re-design applications, you can only re-allocate memory (adjusted SGA) and adjust I / O more or less the way to improve performance. Oracle provides a number of features that only apply to properly design the system only can greatly improve performance.

Application designers need to set up applications at the design stage performance expectations. Then during the design and development, application designers should consider what features the system can be good for Oracle, and using these features.
Through good system design, you can eliminate the application of life cycle costs and performance tuning of frustration. Figure 1-1 Figure 1-2 illustrates the life cycle of the application to adjust the relative costs and benefits, as you see, the most effective adjustment time at the design stage. Adjustments during the design at the lowest price to give you maximum benefits.

Figure 1-1 the application of life cycle costs of adjustment

Figure 1-2 the application of life cycle earnings adjustment

Of course, even in well-designed system, there may be performance degradation. However, these performance degradation should be controllable and predictable.

Adjust the target
Whether you are designing or maintaining the system, you should create a special performance objectives, it makes you know when to make adjustments. If you try to change initialization parameters or random SQl statement, you may adjust the system time to waste, but no big gains. Adjust your system the most effective way is as follows:
l to take into account when designing the system performance
l Adjust the operating system's hardware and software
l identify performance bottlenecks
l to determine the cause of the problem
l to take corrective action when you design a system, develop specific goals; for example, the response time of less than 3 seconds. When the application does not meet this target, identify bottlenecks caused by slow (eg, I / O competition), to determine the cause and take corrective action. During development, you should test the application of research to determine the application before taking it to meet the design performance goals.

When you are to maintain the production database system, there are several effective ways to quickly identify performance bottlenecks.
In any case, adjustment is usually a series of overhead. Once you have identified the bottleneck, you may have to sacrifice some other aspects of the target to achieve the desired results. For example, if the I / O problem, you may need more memory or disk. If you can not buy, you may want to limit the concurrency of the system to obtain the required performance. However, if you have clearly defined performance goals, then what in exchange for high-performance decision-making becomes very easy, because you have identified what is most important, such as me a target of high-performance, may sacrifice some space resources.

With the ever-growing applications, hardware performance, comprehensive application becoming costly to adjust the behavior, in this case, to achieve maximum investment / efficiency ratio, the better approach is to adjust the application of key part, to achieve high performance, so overall, the whole system performance is relatively high. This is the famous 20/80 principle, to adjust the application of 20% (critical part), 80% of the problem can be resolved.

In the design and development of systems for adjusting <br /> well-designed system can prevent the application of life cycle have performance problems. System designers and application developers must understand Oracle's query processing mechanism in order to write efficient SQL statements. "Chapter 2, effective application design," discusses the variety available in your system configuration, and each configuration is more suitable for the type of application. "Chapter 5 optimizer" to discuss Oracle's query optimizer, and how to write statement to get the fastest results.

When designing your system, use the following guidelines to optimize performance:
l elimination of client / server applications in unnecessary network traffic. - Use stored procedures.
l using the appropriate system for your Oracle server options (eg, parallel or distributed database queries).
l application unless you have special needs, or use the default Oracle lock.
l Remember to use the database application modules so that you can based on each module to track performance.
l Select the best of your data block size. - In principle, the larger the better performance.
l distribution of your data, allowing a node to use the data stored in the local node.

Adjust the product system <br /> This section describes the application quickly and easily identify performance bottlenecks, and determine corrective action approach. This method relies on the Oracle server architecture and features of the level of understanding. In trying to adjust your system, you should be familiar with the contents of Oracle tuning.

Have to adjust your system, follow the following steps:
l Adjust the operating system's hardware and software
l by querying the view V $ SESSION_WAIT, identify performance bottlenecks, the dynamic performance view lists the resulting session (session) to wait for the event.
l through the analysis of the data in V $ SESSION_WAIT determine the cause of the bottleneck.
l to correct the problems.

Monitoring applications <br /> This is mainly through monitoring oracle dynamic view to complete.
A variety of useful dynamic view: If v $ session_wait, v $ session_event and so on.

Chapter 2, effective application design <br /> We usually most common application is divided into two types: the type of online transaction processing (OLTP), decision support systems (DSS).

Online transaction processing (OLTP)
The type of application is high-throughput, insert, update, delete operations more and more systems, these systems to the growing high-capacity data for the characteristics, which provide simultaneous access to hundreds of users, a typical OLTP system is the booking system the bank's business systems, order systems. OTLP's main objective is the availability, speed, concurrency and recoverability.
When designing such systems, to ensure that a large number of concurrent users can not interfere with system performance. Also need to avoid excessive use of the index and cluster table, because these structures will slow down inserts and updates.

Decision Support (DSS)
The type of application form will be reported to extract large amounts of information to help decision makers make the right judgments. A typical situation is: OLTP application decision support system will collect large amounts of data to search. Typical applications for customer behavior analysis system (supermarkets, insurance, etc.).
The key objective is to support decision-making speed, accuracy and availability.
The types of design are often associated with OLTP design rationale is generally recommended to use data redundancy, a large number of indexes, cluster table, parallel inquiries.
In recent years, the gradual application of this type with OLAP, data warehousing closely linked to the formation of a new application direction.

Chapter 3, the process of SQL statement processing

Before the adjustments we need to know some background knowledge, and only know the background, we can better adjust to the sql statement.
This section describes the basic process of dealing with SQL statements, including:
* Query processing DML statement processing (insert, update, delete)
· DDL statement processing (create .., drop .., alter ..,)
* Transaction control (commit, rollback)

SQL statement execution (SQL Statement Execution)
Figure 3-1 shows the outline of the process and the need to run a sql statement each important stage. In some cases, Oracle may run the sql in the process listed below each stage a different order. DEFINE stage as possible before the FETCH stage, which is mainly dependent on how you write code.

Oracle is a tool for many, some of which phase is performed automatically. Most users do not need to care about the details of the various stages, however, know the various stages of implementation is still necessary, this will help you write more efficient SQL statements, but also allows you to guess the poor performance of the SQL statement mainly due to a stage which caused, then we focused on this particular stage, to find a solution.

Figure 3-1 SQL statement at all stages of processing

DML statement processing <br /> This section gives an example to illustrate the various stages of DML statement processing in the end what happened.
Assuming you are using Pro * C program to all staff for the designated sector wage increases. Procedures have been connected to the correct user, you can embed in your application the following SQL statement:
EXEC SQL UPDATE employees
SET salary = 1.10 * salary
WHERE department_id =: var_department_id;
var_department_id is the process variable, which contains department numbers, we want to modify the department's staff wages. When this SQL statement is executed, the use of the variable.
Each type of statement requires the following stages:
* Step 1: Create a Cursor to create a cursor Step 2: Parse the Statement Analysis Statement Step 5: Bind Any Variables bind variables Step 7: Run the Statement Run statement Step 9: Close the Cursor off If you use a parallel cursor function, but also includes the following phases:
* Step 6: Parallelize the Statement if the statement is executed in parallel query, you need the following additional steps, as shown in Figure 3:
* Step 3: Describe Results of a Query result set the query described in Step 4: Define Output of a Query defined query output data Step 8: Fetch Rows of a Query to take the query out of the line

The following specifically about each step of what happened:.
Step 1: Create a cursor (Create a Cursor)
From the program interface call creates a cursor (cursor). Any SQL statement will create it, especially when you run DML statements are automatically created the cursor, no developer intervention. Most applications, cursor creation is automatic. However, in the pre-compiler (pro * c) the subject of the middle to create, may be implicit, may also explicitly create. In the stored procedure is the same.

Step 2: Analysis Statement (Parse the Statement)
During the parsing, SQL statements sent from the user process to Oracle, SQL statements by grammatical analysis, SQL statement itself and analysis of the information is loaded into the shared SQL area. At this stage, you can solve many types of errors.

Syntax analysis were the following:
l translated SQL statement, verify that it is legitimate statement, that is written correctly
l find the data dictionary, to verify compliance with the definition of tables and columns
l in the required object for parsing the lock, making the process of parsing the statement does not change the definition of these objects
l Verify the access mode of the object involved in the required permissions to meet the
l determine the best execution plan for this statement
l it into the shared SQL area
l of the distribution statement, the statement of the route to include all or part of the remote node involved in any of the above data step error, an error will result in the statement to suspend the execution.

Only there is no equivalent in the shared pool SQL statements in the case in the best SQL statement for syntax analysis. In this case, the database kernel to re-allocate the statement of new shared SQL area, and the statement is parsed. Parsing takes more resources, so try to avoid syntax analysis, which is one of optimization techniques.

Syntax analysis phase includes this statement will execute regardless of how many times, and only one analysis of the processing requirements. Oracle translated only once for each SQL statement, the statement is executed again in the future, as long as the statement is also shared SQL area, you can avoid re-parsing the statement, that is, at this time can use the corresponding execution plan for data access. This is mainly through the binding variable (bind variable) to achieve, that is, we often shared SQL, SQL back will give the concept of sharing.

Although the syntax analysis verified the correctness of SQL statements, but the syntax analysis can only identify the SQL statement before execution can find the error (such as clerical error, insufficient privileges, etc.). Therefore, some error is not caught by parsing the. For example, in data conversion errors or the errors in the data (such as an attempt to insert duplicate primary key values), and deadlock and so is the only statement that can be encountered during the implementation phase and report the error or the situation.

<br /> Query processing query and other types of SQL statements is different, because as a result of a successful execution will return data. Other statements simply return success or failure, while the query is able to return one row or many rows of data. Query results are used in tabular form, the result is a row or rows to be retrieved quantities. From here we can see that the bulk of the fetch data can reduce network overhead, so the bulk of the fetch is one of optimization techniques.

Only some of the problems associated with query processing, query only refers to the SELECT statement, also included in other SQL statements in an implicit query. For example, each of the following statements are required to execute the query as a part of it:
INSERT INTO table SELECT ...
UPDATE table SET x = y WHERE ...
DELETE FROM table WHERE ...
CREATE table AS SELECT ...
Specifically, the query read consistency requirements may be used for intermediate processing may require a rollback SQL statement processing describe, define, and fetch stages

Step 3: Describe the results (Describe Results of a Query)
Describes the stage only in the query results is unknown when the needs of each column; for example, when a query entered by the user interaction required output column name. In this case, the decision to use the stage to describe the characteristics of query results (data type, length and name).

Step 4: Define the query output data (Define Output of a Query)
In the query definition phase, you specify the column with the check out to receive the corresponding value of the variable position, size and data types, so we can get through the receiver variable results. If necessary, Oracle will automatically convert data types. This is the type of the receiver variable and the corresponding column types compared to the decision.

Step 5: Bind variable (Bind Any Variables)
At this point, Oracle knows the meaning of the SQL statement, but still not enough information to execute the statement. Oracle needs to be listed in the statement the value of all variables. In this example, Oracle needs to be qualified on the department_id column value. Get the value of the process is called binding variables (binding variables)

This process is called the variable value tied in. Procedures must be pointed out that you can find the value variable name (the variable is called binding variables, variable name is essentially a memory address, the equivalent of a pointer). Application and end-user may find that they are not bound variables specified, because Oracle's program may simply indicate that they enter a new value, in fact, everything is done automatically in the program.

Because you specify the variable name, before you perform again without re-tied variables. You can change the bind variable, Oracle executes each time, just use the memory address to find this value.

If Oracle needs to achieve automatic data type conversion, then (unless they are hidden or the default), you must also specify the data type of each value and length. This information can refer to the oracle of the relevant documents, such as the Oracle Call Interface Programmer's Guide

Step 6: parallel execution statement (Parallelize the Statement)
ORACLE can SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs statement corresponding parallel query execution, some DDL operations such as create index, create a table with a subquery, the operation on a partitioned table, you can perform parallel operations. Parallelization can lead to multiple server processes (oracle server processes) to work with a SQL statement, so that the SQL statement can be quickly completed, but will consume more resources, so unless it is necessary, do not use parallel query.

Step 7: Execute the statement (Run the Statement)
Up to now this time, Oracle has all necessary information and resources, so you can actually run SQL statements. If the statement is a SELECT query or INSERT statement, you do not lock any rows because no data needs to be changed. However, if the statement is UPDATE or DELETE statement, the statement impact of all the rows are locked, preventing the user to commit or roll back before the other users of these data to be modified. This ensures data consistency.

For some statements you can specify the number of times, this is called batch (array processing). Specify the N times, then bind variables and definition of variables is defined as an array of size N, the start position, this method can reduce network overhead, is one of optimization techniques.

Step 8: Remove the query line (Fetch Rows of a Query)
In the fetch stage, rows are taken out, each subsequent access operation to retrieve the result set of the next row, until the last line was taken out. As mentioned above, batch fetch is one of optimization techniques.

Step 9: Close the cursor (Close the Cursor)
SQL statement handling the final stage is to close the cursor

DDL statement processing (DDL Statement Processing)
DDL statements and DML statements to perform different and query execution, this is because DDL statements after the successful implementation of the data dictionary, data need to be modified. For DDL statements, statements of fact, the analysis phase includes analysis, the data dictionary to find information and perform.

Management statement, session management statement, system management statement analysis and the implementation phase only, in order to re-execute the statement, will re-analyze and execute the statement.

Transaction control (Control of Transactions)
In general, only use the ORACLE application programming interface designers only care about the type of operation, and the related operations grouped together to form a transaction. In general, I must define the services the door, so that a logical unit of work can all be committed or rolled back, ensuring data consistency. A transaction should be logical units of all components, should not one more should not be one less.
* At the start and end of this time, all the referenced data in the table should be in a consistent state (or can be traced back to a consistent state)
* Service should only contain the same data can be changed (one consistent change to the data) of the SQL statement

For example, transfers between two accounts (which is a transaction or logical unit of work), should contain from one account to borrow money (from a SQL completion), then borrowed money into another account (by another SQL completed). These two operations as a logical unit, should also succeed or fail. Other non-related operations, such as deposit money to an account should not be included in the transfer transaction.

In design applications, in addition to need to decide what type of operations form a transaction, but also need to decide whether to use stored procedures to improve BEGIN_DISCRETE_TRANSACTIO small, non-performance of a distributed transaction role.

Chapter 4 ORACLE optimizer

Optimizer is sometimes referred to as the query optimizer, which is affecting database performance because the query is the most important part, do not think that only the SELECT statement is a query. In fact, with any WHERE conditions DML (INSERT, UPDATE, DELETE) statements are included in the query request, in a later article, when it comes to queries, not only refers to the SELECT statement, DML statements may also refer to the query part. Optimizer is a relational database engine for all of the most mysterious, one of the most challenging part, from a performance point of view is the most important part, it is directly related to the level of performance, database performance is good or bad.

We know, SQL statements with other languages ​​(like C language) statement is not the same, it is non-procedural (non-procedural) of the statement, that is, when you want to take the data, through what channels do not need to tell the database to fetch data If in the end by taking the index data, or should each row of data in the table are taken out, and then compare the way through eleven take the data (ie full table scan), which is determined by the database optimizer, which is non-procedural meaning, that is, how to get data is determined by the optimizer, not the application developers through the programming decisions. In dealing with the SQL SELECT, UPDATE, INSERT, or DELETE statement, Oracle must access the data involved in the statement, Oracle's optimizer to access some of the data used to determine a valid path, making the statement to perform the desired I / O and processing time is minimized .

In order to achieve a query, the kernel must be customized for each query a query strategy, or to remove the data to generate a qualified implementation plan (execution plan). Typically, for the same query may have several execution plans to meet the requirements, the data can be eligible. For example, in the connection table can have many different connection methods, depending on conditions and optimize the device to connect using the connection method. In order to implement the plan in more than one choose the best execution plan, the optimizer must use some indicators to measure the actual execution plan used by each resource (I / 0 times, CPU, etc.), these resources are what we call the price (cost). If an implementation plan to use more resources, we say the cost of using the implementation plan big. The cost to implement the plan as a measure of the size of the standard, the optimizer chooses the execution plan as the minimum cost of real implementation of the query execution plan and implementation plan to abandon the other.

In ORACLE's development process, developed over a total of two types of optimizer: rule-based optimizer and the cost-based optimizer. Optimizer these two key differences: the method to obtain the price of different sizes with measurable cost. Optimizer is for each to do some simple introduction:

Rule-based optimizer - Rule Based (Heuristic) Optimization (referred to as RBO):
In ORACLE7 before, mainly using rule-based optimizer. ORACLE in the rule-based optimizer used heuristic methods (Heuristic Approach) or rules (Rules) to generate the execution plan. For example, if a query where condition (where clause) contains a verb (predicate, in fact, a judge conditions, such as "=" ">" "<", etc.), and the predicate on the column referenced on the effective index , then the optimizer will use indexes to access the table, regardless of other factors, such as the amount of data in the table, the volatility of the data in the table, the index of choice and so on. At this point there is no database table and index data on the statistical description, such as how up table, each line of choice and so on. Optimizer does not consider the instance of the parameters, such as multi block i / o, the size of available memory, sort, so the optimizer will sometimes choose a sub-optimal plan as the real execution plan, resulting in system performance is not high.
For example, for
select * from emp where deptno = 10;
This query, if the use of rule-based optimizer, and the deptno column on the effective index on the deptno column is through the index to access the emp table. In most cases, this is more efficient, but in some special cases, access the index when there are relatively inefficient, is an example:
1) emp table is relatively small, only the table data stored in several data blocks. This time using the full table scan than use the index to access the emp table but better. Because the table is relatively small, most likely all the data in memory, so at this time to do full table scan is the fastest. If using an index scan, you need to start with the index found, record rowid, and then eleven rowid from emp will be based on these data taken out, under such conditions, the efficiency will be a full table scan than less efficient some.

2) emp table is too big, and deptno = 10 conditions can query the table as most of the data (50%). As the table a total of 40 million rows of data, were placed in a 500 000 data blocks, each block is 8k, then the table a total of about 4G, is not so much data in memory in all, the vast majority of needs on the hard disk. At this point the query if the query through the index, it is the beginning of your nightmare. db_file_multiblock_read_count parameter 200. If you use full table scan, you need to 500000/db_file_multiblock_read_count = 500000/200 = 2500 times I / O. However, if using an index scan, if the index on the deptno column have cache memory, so you can access the index overhead is negligible. As to read out the 40 million x 50% = 2000 million data, assuming that reading this 20 million data, 99.9 percent shooting, you still need 20000 I / O, than the above full table scan requires 2,500 times a lot of Therefore, in this case, use an index scan instead of worse performance. In such circumstances, with full table scan time is fixed, but will use an index scan time increase with the selected data corresponding to the time extension of the query.

The above assumptions are boring, give specific examples now validate:
Environment: oracle 817 + linux + array cabinets, tables SWD_BILLDETAIL have more than 32 million data;
Id column of the table, cn columns are indexed by the view the execution plan and found that the implementation of select count (id) from SWD_BILLDETAIL; use full table scan, executed with about 1.50 minutes (4 times the average executive to take, each 1.45 1.51 2.00 1.46). The implementation of select count (id) from SWD_BILLDETAIL where cn <'6 '; but with 2 small, had not been complete, an analysis of the statement on the use of the cn column index, then check out using the rowid in the query from the table data. Why do not I use select count (cn) from SWD_BILLDETAIL where cn <'6 '; it? Execution path in the analysis behind the index scan will always give instructions.

Here is a rule-based optimizer execution path and use the path corresponding to each level:
RBO Path 1: Single Row by Rowid (highest level)
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan (lowest level)

Implementation of the above path, RBO that the greater the cost of the more down to execution, that is, the lower level. Execution plan is generated in the RBO, if it is found that the high level of execution path is available, definitely will use high-level path, regardless of any other elements that affect performance, that RBO through the path above the level of the decision cost of the execution path, The higher the level of execution paths, then the cost of using the smaller of the execution path. If the above two examples above, if you use RBO, then certainly use the index to access the table, that is, choose a relatively poor implementation plan, so the database will bring great negative impact on performance. To solve this problem, starting from the oracle ORACLE 7 introduces a cost-based optimizer, the following gives the introduction.

Cost-based optimizer - Cost Based Optimization (referred to as CBO)
Oracle to an expensive engine (Cost Engine) integrated into the database kernel, used to estimate the cost of each execution plan requires that the cost of each implementation plan to quantify the resources spent, which CBO can choose the best according to this price implementation plan. A resource consuming query can be divided into three basic components: I / O costs, CPU costs, network costs. I / O cost is the data read from disk into memory, the required price. To access data includes the data file the contents of data blocks read into the data cache in the SGA in general the cost of processing a query as needed Zuizhu Yao costs so our optimization, a basic principle is to reduce the queries generated by the I / O total number. CPU cost of processing the required data in memory cost, such as when data is read into memory, then we identify the data we need, in order to perform these data (sort) or connect (join) operation, which requires consume CPU resources.

For the need to access cross-node (which is usually said server) database queries on the data, there are network costs, transfer operations to quantify the resources spent. Query the remote table or execute a distributed query connection query cost of the network to spend relatively large.

When using the CBO, the need for table and index statistics (analysis of data) as the underlying data, with these data, CBO execution plan can be calculated for each relative accuracy of the cost, so that the CBO choose the best execution plan. Therefore, periodic table, index analysis is absolutely necessary, so as to make the statistical data in the database to reflect the real situation. Otherwise the CBO choose poor implementation plan, impact on database performance. Analysis operations do not do too often, in general, once a week is sufficient. Remember if you want to use the CBO, you must regularly analyze tables and indexes.

For the analysis of command, with the database version upgrade, the command also occurred with the change, before the oracle 8i, mainly with the ANALYZE command. In the ORACLE 8I later, they introduced the DBMS_STATS package to storage for analysis. Fortunately, from the ORACLE 10G after the analysis becomes automatic, which reduce the burden of the DBA, but in some special cases, require some manual analysis.

If you use the CBO optimizer, but not on the table and index analysis, no statistical data, the ORACLE default statistics (at least in the ORACLE 9I is the case), which can be found on the document from the oracle. Use the default value of certain real statistics with the system is inconsistent, which may cause the optimizer to choose the wrong execution plan, the impact database performance.

It should be noted: Although the CBO's function with the introduction of a new version of ORACLE, more powerful, but it is not a magic pill to cure, or no longer need a DBA, I will suffer! ! ! In fact any one statement, as the hardware environment with different application data, the statement execution plan changes as may be required, so as to obtain the best performance. Sometimes, but not in the specific environment for SQL performance tuning is futile.

When launched in ORACLE8I, ORACLE strongly suggest that you use CBO, CBO said there are various benefits, but that is the ORACLE application development or use of rule-based optimizer, from this incident, we can conclude that: 1 ) If the teams are familiar with database applications and a high level of data characteristics, RBO can also get good performance. 2) CBO is not very stable, but a more promising optimizer, Oracle strongly recommends that you use to let everyone found it as soon as possible of the BUG, ​​in order to further improve, but ORACLE order to develop their own application system is responsible for, they still use the more familiar and mature RBO. From this matter to our inspiration is this: we in the future development, should maximize the use of the familiar and proven technology, and not blindly adopt new technologies, not necessarily blindly adopt new technologies can develop a good product. Fortunately, after from ORACLE 10G, CBO has enough power and intelligence, we can safely use the technology, because after ORACLE 10G, Oracle applications developed their own system using the CBO optimizer. ORACLE and requirements, from ORACLE 10G, I started scrap RBO optimizer. This sentence does not mean you can not use the ORACLE 10G RBO, but starting from the beginning ORACLE 10G, no longer provide repair services RBO's BUG.

No. 2 in the above example, if we adopt the CBO optimizer, it will consider the number of rows in the emp table, deptno column of the statistical data, found that the query will check out the column to do too much data, and consider the parameters db_file_multiblock_read_count setting, found that the cost of using full table scan than an index scan cost is smaller, so use full table scan to obtain good execution performance.

Determine what the optimizer to use the current database:
Is mainly determined by the initialization parameter optimizer_mode. The possible values ​​for the parameters: first_rows_ [1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule. Specific explanation is as follows:
RULE optimizer to use RBO.
CHOOSE is based on the actual situation, if the data dictionary table containing the referenced statistical data, that the referenced object has been analyzed, the optimizer will use the CBO, RBO otherwise optimizer.
ALL_ROWS as the CBO optimizer uses the first specific optimization method, based on the data throughput as the main target, so that you can use the least resources to complete the statement.
FIRST_ROWS for the optimizer to optimize the use of a second specific method, based on the data as the main target response time for quick check out the first few rows of data.
FIRST_ROWS_ [1 | 10 | 100 | 1000] In order to optimize the use of a third device specific optimization, the optimizer can choose a to minimize the response time of query execution plan to rapidly produce results of the first n rows. The parameters for the ORACLE 9I newly introduced.

Since from the ORACLE V7, optimizer_mode default setting for this should be "choose", that is, if the parsed query, then select the CBO, or select the RBO. In such settings, if using the CBO, then the default mode for the CBO in all_rows.

Note: Even if you specify the database optimizer to use RBO, but sometimes will use ORACLE database or CBO optimizer, this is not the ORACLE BUG, ​​mainly due from the ORACLE 8I after the introduction of many new features have to be under the CBO to use, and you SQL statement could just use these new features, then the database will automatically be converted to use the CBO optimizer executes them.

What is the optimal <br /> optimization is to choose the most effective implementation plan to execute SQL statements in the process, which is in any data processing statements (SELECT, INSERT, UPDATE, or DELETE) is an important step. For Oracle, the implementation of this statement there are many different ways, for example, as to what order to access the table or index which varies. Used by the statement execution plan can decide how fast to execute. In Oracle called the optimizer (Optimizer) is used to select the components that it considers the most effective implementation plan.

As a number of factors will affect the execution of the statement, the optimizer integrated weigh various factors, many of the implementation plan in selecting the best implementation of that plan. However, application designers often know better than the optimizer application-specific data on the characteristics. No matter how intelligent optimizer, in some cases, the developer than the optimizer can choose the best execution plan chosen also a good implementation plan. This manual intervention is needed to optimize the main database. The fact that, in some cases, I do need some statement DBA manual optimization.

Note: A version from Oracle to another version, the same statement, the optimizer may generate different execution plans. In future versions of Oracle, the optimizer may be based on it can be better, better information to make better decisions, leading to the statement produces better execution plans.

Chapter 5 ORACLE implementation plan
Background:
In order to better carry out the following content we need to understand some of the conceptual terms:
Shared sql statement

In order not to repeat the same SQL statement parsing (because of analytical operations more cost resources, will lead to performance degradation), after the first resolution, ORACLE will be parsed SQL statement and execution plan stored in memory. This is located in the System Global Area SGA (system global area) of the shared pool (shared buffer pool) the memory can be shared by all database users. Therefore, when you execute a SQL statement (sometimes called a cursor), if the statement and before the execution of a statement over the same, and executed before the statement and its execution plan still exists in memory, the ORACLE do not need to be analyzed, the statement directly from the execution path. ORACLE's this function greatly improves the performance of SQL execution and significant savings in memory usage. The key is to use this function will be executed in the statement as far as possible into memory, so this requires a large shared pool (shared buffer pool by setting the parameter values) and the possible use of bind variables method to execute SQL statements.

When you submit a SQL statement to ORACLE, ORACLE will first look in the shared memory has the same statement. It should be noted that, ORACLE is taken for both a strict match, to reach a share, SQL statements must be identical (including spaces, line breaks, etc.).

Here is the SQL statement to determine whether the shared memory in a SQL the same steps:
1) The text of the statement issued by strings hashed. If the hash value already in the shared pool SQL statements the same hash value, then proceed to Step 2:
2) The statement issued by the text string (including capitalization, white space and comments) and in the first step in identifying all the existing SQL statement compared.
For example:
SELECT * FROM emp WHERE empno = 1000;
Each is different and the following
SELECT * from emp WHERE empno = 1000;
SELECT * FROM EMP WHERE empno = 1000;
SELECT * FROM emp WHERE empno = 2000;
In the above statement, the column values ​​are SQL statements directly in the future we will be hard-coded SQL type of sql
Or a literal SQL

SQL statement with bind variables must use the same name in the bind variables (bind variables),
For example:
a. The two are considered the same sql statement
select pin, name from people where pin =: blk1.pin;
select pin, name from people where pin =: blk1.pin;
b. The two are not considered the same sql statement
select pin, name from people where pin =: blk1.ot_ind;
select pin, name from people where pin =: blk1.ov_ind;
The future, we will bind the above variables such statements as SQL.

3) the statement issued by the objects involved in step 2 and identify the objects involved in the statement already exists in comparison.
For example:
Such as user user1 and user2 users always have the EMP table, then
Statement issued by the user user1: SELECT * FROM EMP; and
Statement issued by the user user2: SELECT * FROM EMP; is considered to be not the same statement,
Because the two statements quoted EMP does not mean the same table.

4) In the SQL statement using the bundled bundled variable type must be consistent.

If the statement in the shared pool with the current statement is equivalent to the words of another, Oracle does not parse it. Directly execute the statement, and raising the efficiency of resource-consuming because parsing.

Note that starting from oracle 8i, introduced a new CURSOR_SHARING parameter that the main purpose is to solve the programming process have been widely used hard-coded SQL problem. Because in the actual development, many program staff in order to improve development speed, and the development of methods similar to the following:
str_sql string;
int_empno int;
int_empno = 2000;
str_sql = 'SELECT * FROM emp WHERE empno =' + int_empno;
... ... ... ...
int_empno = 1000;
str_sql = 'SELECT * FROM emp WHERE empno =' + int_empno;
The above code actually uses hard-coded SQL, so that we can not use a shared SQL function, the result is a database inefficient. However, the above two statements from the point of view, the resulting column value hard-coded SQL is different from the other parts are the same, if only because the column value is a result of these two different statements can not be shared is a great pity, in order to solve this problem , introduced CURSOR_SHARING parameters, so that such problems can also use shared SQL, so developers can also use this shared SQL. Sounds good, ORACLE true for the sake of users, so that the user does not change the code in the case can also use shared SQL function. Really true? The sky will not fall for no reason a pie, ORACLE done using the parameters described, it is recommended after the actual test and then change the parameter value (by default, this parameter is EXACT, exactly the same statement only use a shared SQL). Because of the possibility that changes the value, you can use hard-coded SQL is shared SQL, but it will decrease the performance of the database. I have encountered in the practical application of this situation. Stable operation it is recommended that written procedures need to be better developers start to use bind variables of SQL.

Rowid concept:
rowid is a pseudo-column, since it is pseudo-column, then this column is not user-defined, but the system of their own to add. For each table has a rowid pseudo-column, but the table does not physically store the ROWID column value. But you can use other columns so as to use it, but can not be deleted, and listed, it can not modify the column value, insert. Once the line data into a database, the rowid in the life cycle of the line is unique, that even if the line produce the migration, the line will not change the rowid.

Why use the ROWID
rowid to access a table on a given line provides the fastest access method, by ROWID can directly navigate to the corresponding data block, then read the memory. We create an index, the index only stores the index column values, but also to store the index value corresponding to the line of ROWID, so we quickly find the corresponding row index ROWID, through the ROWID, you can quickly check out the data . This is what we use the index query, the faster the reasons.

In ORACLE8 previous versions, ROWID from the FILE, BLOCK, ROW NUMBER composition. With the extension of the concept of objects oracle8, ROWID has changed, ROWID from the OBJECT, FILE, BLOCK, ROW NUMBER composition. Rowid can be used DBMS_ROWID broken down into various parts of the above, each of these can also be an effective part of rowid.

Recursive SQL concept <br /> sometimes in order to implement the user to issue a sql statement, Oracle must perform some additional statements, we will call these additional statements 'recursive calls' or 'recursive SQL statements'. For example, when a DDL statement is issued, ORACLE always implied a number of recursive SQL statements issued to modify the data dictionary information, so that users can successfully execute the DDL statement. When the data dictionary information is not in the shared memory, often in Recursive calls, these Recursive calls will read the data dictionary information from the hard disk into memory. Users concerned about these than the implementation of recursive SQL statements, when required, ORACLE automatically in the internal implementation of these statements. SELECT DML statement and of course, may cause recursive SQL. Simply put, we can trigger as recursive SQL.

Row Source (line source)
Used in the query, returned by a previous operation set of qualifying rows, which can be a table of all rows of data collection; also be part of the table row of data collection; also for the row source for the two join operations (such as the join connections) to get the line after the data collection.

Predicate (verb)
A query in the WHERE restrictions

Driving Table (table-driven)
The table also known as the outer table (OUTER TABLE). This concept is used in a nested connection with HASH. If the row source returns more rows, then for all subsequent operations have a negative impact. Note here that although the translation table for the driver, but in fact translated to drive the line source (driving row source) are more precise. In general, the application of query constraints, the return fewer rows of the table as the driving source of the table, so if a large table in the WHERE conditions are restrictions (such as the equivalent limit), the large table as the driving table is right, so not only the smaller table can be used as the driving table, the correct statement should check the restrictions for the application, return fewer rows of the table as the driving source table. In the implementation plan, should rely on that row source, the back will give specific instructions. In our description below, the general operation of the connection table as row source 1.

Probed Table (by probe table)
The table also known as the inner table (INNER TABLE). In our table from the drive line to get specific data in the table to find the rows meet the join condition. So the table should be large table (actually a large row source should return to the table) and the corresponding columns should be indexed. In our description below, the general operation of the connection table as row source 2.

Combination index (concatenated index)
Constituted by a number of columns the index, such as create index idx_emp on emp (col1, col2, col3, ... ...), then we say idx_emp index is a composite index. In the composite index has an important concept: guide columns (leading column), in the above example, col1 as a guide line. When we can use the query "where col1 =?", You can use "where col1 =? And col2 =?", Such restrictions will use the index, but "where col2 =?" Query will not use the index . So the restrictions contained in the pilot column, the restrictions will use the index portfolio.

Selectivity (selectivity):
More of the following only the number of keys and the number of rows in the table, we can determine the selectivity of the column. If the column "number of unique key / number of rows in the table," the ratio closer to 1, the higher the selectivity of the column, the column is more suitable for creating an index, choose the same index is also higher . In the optional column on the high query to return data on fewer, more suitable to use the index query.

With this background knowledge introduced after the beginning of the implementation plan. In order to execute the statement, Oracle may have to implement a number of steps. Each step of these steps may be physically retrieve data from the database rows, or rows of data in some way prepared for the user issuing the statement. Oracle to execute the statement of a combination of these steps is called an implementation plan. Implementation plan is the most complex SQL optimization is the most critical part of the ORACLE only know how to perform in-house in the end is the SQL statement, we can know that the optimizer chooses the execution plan is optimal. Implementation plan for the DBA, as the financial statements is as important for the finance staff. So the main problem we face is: how to get the execution plan; how to analyze the execution plan, in order to identify the main problems affecting performance. The following implementation plan to start analyzing the tree began to introduce, and how to get the execution plan, and then how to analyze the execution plan.

For example:
This example shows the following SQL statement on the implementation plan.
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = derpt.deptno
AND NOT EXISTS
(SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);
This statement queries the salary is not paid within the scope of any proposed names of all employees, job, salary, and department name.
The figure 5-1 shows a graphical representation of the execution plan:

Perform the steps <br /> every step of the execution plan returns a set of lines that are either used for the next step, or return to the last step in the SQL statement issued by the user or application. Returned by each step of a set of rows is called the line source (row source). Figure 5-1 shows a tree walk from one step to another data flow. The numbers reflect each step in the implementation of the plan shown you observe the sequence of steps (how to observe the implementation plan will be briefly out). In general this is not the order is executed at each step. Every step of the implementation plan, or retrieve rows from the database, or receive one or more rows from the row data as input source:
In red box by the steps that the data from the database physical file to retrieve data. This step is called the access path, described in detail later in Oracle can use the access path:
l Step 3 and 6, respectively, from the EMP table and SALGRADE table all rows are read.
l Step 5 Find the index in PK_DEPTNO returned from step 3 for each DEPTNO value. Find it associated with the DEPT table, those rows of ROWID.
l Step 4 from the DEPT table to retrieve the ROWID as the step 5 return those rows.
Word from the black boxes that operate on the steps to the line source, such as making the link between 2 tables, sorting, or filtering operations, the back will give a detailed description:
l Step 2 to achieve a nested loop operation (equivalent to C statements in nested loops), to receive from Step 3 and Step 4 to the line source, the source from step 3 for each line with its 4 step in the corresponding row together, the results of the line to return to Step 1.
l Step 1 to complete a filter operation. It receives from Step 2 and Step 6 of the line source, eliminated to step 2, in step 6 with the corresponding lines of those lines, and the remainder from Step 2 line is returned to the statement users or applications.

Implementation plan to achieve the sequence of steps in the implementation plan <br /> steps are not numbered according to their order to achieve: Oracle first to achieve graphical tree structure in Figure 5-1 appears as the leaves of those steps (such as step 3,5,6 ). Rows returned by each step as it is the next step of the line source. Then Oracle has implemented the parent step.

For example, in order to execute the statements in Figure 5-1, Oracle in the following order to achieve these steps:
l First, Oracle implementation steps 3 and the results line by line, the line returns to Step 2.
l Step 3 on each row returned, Oracle to achieve these steps:
- Oracle to achieve step 5, and the results ROWID returns to Step 4.
- Oracle to achieve step 4, and the results returned to the line of Step 2.
- Oracle to achieve Step 2, Step 3 will be accepted from the line and the line from step 4 and return to Step 1 row.
- Oracle to achieve step 6, if the results of the line, then it returns to step 1.
- Oracle to achieve step 1, if the return line from Step 6, Oracle will be the line from step 2 back to the user issuing the SQL statement.

Note that Oracle is returned by the step 3 for each row to achieve a step 5,4,2,6. Many steps in the parent before they can perform only a single step from their sub-lines. The parent of such steps, as long as the steps from the child to return a single row has an immediate parent steps (and possibly the rest of the implementation plan). If the parent step parent step also can be activated by a single row is returned, then it is also executed. Therefore, the implementation of the series can go up in the trees, may contain the remaining part of the implementation plan. For this, you can use first_rows so as to optimize the target for rapid response to user requests.
Followed by the sub-steps for each retrieved for each row, Oracle will achieve the parent step and all steps in a series together. Returned by the sub-steps for each row triggered by the parent steps include table access, index access, nested loop joins and filters.

Some steps in the parent before they are required to achieve all the lines from the sub-steps. The parent of this step until all rows returned from the sub-steps can not be achieved until the Oracle father steps. Such parent steps include sequencing, sorting a combined connection, and the total set of functions. For such operations, can not use first_rows as the optimization target, and can be used as the optimization objective all_rows, so that the type of operating cost in the fewest resources.

Sometimes the statement is executed, not, as stated above after the first step by step, the conduct, but may run in parallel, such as in a real environment, 3,5,4 step may run in parallel in order to achieve better efficiency. From the tree diagram above, is difficult to see the order of execution of each operation, and generated by ORACLE another form of execution plan, you can easily see which perform the first operation, after which the implementation of this implementation plan is that we really need, will give details later. Now let's see some prior knowledge.

Access path (method) - access path
Optimizer in the formation of the implementation plan needs to be done is an important choice is how to query a database the data needed. For SQL statements to access any table in any line, there may be many access paths (access method), through which you can locate and check out the required data. Optimizer choose one from the path that is optimal.
In the physical layer, oracle read data, the smallest unit of a read block for the database (the operating system by multiple consecutive blocks), the maximum time to read once by the operating system I / O parameters of the maximum and multiblock co- decisions, even if only one line of data, but also blocks the line where the database is read into memory. Logically, oracle access to the data access methods with the following:

1) Full table scan (Full Table Scans, FTS)
To achieve full table scan, Oracle reads all rows in the table and check whether each line to meet the statement's WHERE constraints. Oracle assigned to the table in order to read each block of data, read the table until the waterline at the maximum (high water mark, HWM, identifies the last data block table). A multi-block read operation allows an I / O can read multiple data blocks (db_block_multiblock_read_count parameter setting), rather than just reading a block, which greatly reduces I / O total number, to improve the system throughput, so the use of multi-block reading method can achieve very efficient full-table scan, but only in the case of full table scan in order to use multi-block read operation. In this access mode, each data block is read only once. As the last piece of identification HWM data is read, and delete operation does not affect the HWM value, so a table is delete all the data after the time of its full-table scan will not improve, generally we need to make use of truncate command value HWM classified as 0. Fortunately, oracle 10G, you can manually shrink the value of the HWM.

By the FTS mode data is read into the cache Least Recently Used (LRU) end of the list, so you can quickly swap out their memory, so memory is not that important data is exchanged out of memory.
Prerequisite for using the FTS: In a large table full table scan is not recommended, unless the data are more removed, more than 5% of the total - 10%, or you want to use the parallel query feature.
Examples of using the full table scan:
~~~~~~~~~~~~~~~~~~~~~~~~
SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost =
TABLE ACCESS FULL DUAL

2) a table by ROWID access (Table Access by ROWID or rowid lookup)
ROWID line that the line where the data files, data blocks and line location of the block, so data can be accessed through ROWID to quickly locate the target data, is Oracle's fastest way to access a single row of data.
To access the table by ROWID, Oracle first to get the selected row ROWID, or from the statement's WHERE clause to be, or by one or more index table index scan to be. Oracle and then to get the ROWID based positioning each of the chosen line.
This access method does not use multi-block read operation, an I / O can only read a data block. We will often see the execution plan access methods, such as through the indexing query data.
Use the ROWID access methods:
SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

3) Scan the index (Index Scan or index lookup)
Let's look through the index to the data corresponding rowid values ​​(for non-unique index may return multiple rowid value), then rowid from table to receive specific data, this look is called an index scan or index lookup (index lookup). A rowid only that row, the row corresponding data block is an i / o to be, in this case the second i / o will read a database block.

In the index, in addition to storing the value of each index, the index value is also stored with the row corresponding ROWID value. Index scan can be composed by two steps: (1) Scan the index to get the corresponding rowid values. (2) by finding the rowid from table to read out specific data. Each step is a separate I / O, but for the index, due to frequent use, most have been CACHE into memory, so the first step of the I / O is often the logic I / O, the data from memory be. But for Step 2, if the table is relatively large, then the data can not be all in memory, so its I / O is likely that the physical I / O, which is a mechanical operation, the relative logical I / O, , is extremely time-consuming. So what if the table index scan, remove the data if the total is greater than 5% - 10% efficiency using an index scan will drop a lot.
As shown below:
SQL> explain plan for select empno, ename from emp where empno = 10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
Note TABLE ACCESS BY ROWID EMP part, indicating that this is not the path to access data through the FTS access, but access by rowid lookup data access path. In this case, the need is due to the rowid in the index to find the empno column is worth, this method is INDEX UNIQUE SCAN to find the back to give introduction, EMP_I1 to use the index to find the index name.

However, if the query data can all be found in the index, you can avoid the step 2, to avoid unnecessary I / O, then scan through the index out of even more data, efficiency is very high, because this only in the index reading. So I described above, rule-based optimizer, using a select count (id) from SWD_BILLDETAIL where cn <'6 ', but did not use select count (cn) from SWD_BILLDETAIL where cn <'6'. Because in reality, the only query is the value of the index columns the situation is extremely small, so if I use the query count (cn), is not representative.

SQL> explain plan for select empno from emp where empno = 10; - just check the value of empno column
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX UNIQUE SCAN EMP_I1

Further, if the sql statement in the column to sort the index because the index has been sorted well in advance, so in the implementation of the plan do not need to sort the columns of the index
SQL> explain plan for select empno, ename from emp
where empno> 7876 order by empno;
Query Plan
-------------------------------------------------- ------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
You can see from this example: Because index is already sorted, so will check out the order of the index rows that qualify, thus avoiding further sorting operation.

Where the index type and the different constraints, there are four types of index scan:
Index unique scan (index unique scan)
Index range scan (index range scan)
Index full scan (index full scan)
Index fast scan (index fast full scan)

(1) index unique scan (index unique scan)
Find a value through a unique index always returns a single ROWID. If the unique index has multiple columns (ie combination of the index), then at least in the composite index of the guide columns to the query, such as creating an index: create index idx_test on emp (ename, deptno, loc). Then select ename from emp where ename = 'JACK' and deptno = 'DEV' statement can use the index. If the statement returns only one row, the index unique scan access method is called. And select ename from emp where deptno = 'DEV' statement does not use the index, because the species does not lead where clause column. If there is a UNIQUE or PRIMARY KEY constraint (which guarantees access only the single statement), then, Oracle often achieve unique scanning.
A unique constraint example:
SQL> explain plan for
select empno, ename from emp where empno = 10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

(2) an index range scan (index range scan)
Use an index to access multiple rows of data, as above, if the index is a composite index, such as (1) below, and select ename from emp where ename = 'JACK' and deptno = 'DEV' statement returns multiple rows of data, although The statement or the use of the combination of indexes to search, you can access at this time is called the index range scan. Unique index on the index range scan using the typical case is the predicate (where restrictions) using the range operator (such as >,<,<>,>=,<=, between)
Use an index range scan example:
SQL> explain plan for select empno, ename from emp
where empno> 7876 order by empno;
Query Plan
-------------------------------------------------- ------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

In the non-unique index on the predicate col = 5 may return multiple rows of data, so use non-unique index on an index range scan.
Use index rang scan of three situations:
(A) in unique index column on the use of range operators (> <<>> = <= between)
(B) in the composite index on the query using only part of the column, resulting in a multi-line query
(C) the non-unique index column on any inquiry.

(3) index full scan (index full scan)
Correspond with the full table scan, but also the corresponding full index scan. In some cases, it may be a full index scan instead of range scan, should be noted that a full index scan only mode to be effective in the CBO. CBO based statistic that full-index scan is more than a full table scan effective only when the full index scan, and then check out the data can be obtained directly from the index.
Full index scan example:
An Index full scan will not perform single block i / o's and so it may prove to be inefficient.

eg
Index BE_IX is a concatenated index on big_emp (empno, ename)

SQL> explain plan for select empno, ename from big_emp order by empno, ename;
Query Plan
-------------------------------------------------- ------------------------------
SELECT STATEMENT [CHOOSE] Cost = 26
INDEX FULL SCAN BE_IX [ANALYZED]

(4) index fast scan (index fast full scan)
Scanning index of all the data blocks, and index full scan is very similar, but a significant difference is that it does not check out the sort of data, that data is not the sort order is returned. In this access method, you can use the multi-block read function can also be read using the parallel for maximum throughput and shorten the execution time.

Index quick scan of the examples:
BE_IX index is a multi-column index: big_emp (empno, ename)

SQL> explain plan for select empno, ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

Select only the first multi-column index 2:
SQL> explain plan for select ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

The connection between the table
Join an attempt to combine the two predicate table, one can only connect two tables, table join is also called table association. In the following description, we will use the "row source" instead of "table", because a number of more rigorous with row source, and will participate in the row source to connect the two are called row source1 and row source 2. Join the various steps in the process often is a serial operation, even if the row source can be related to concurrent access, which can be read in parallel to connect the two do join row source of the data, but in the table meet the constraints of the data read After the row source to memory formation, join the other steps are generally sequential. There are several ways you can connect the two tables together, of course, each method has its own advantages and disadvantages of each type of connection only in the specific conditions under which it will play out its greatest advantage.

row source (table) the connection between the efficiency of the query sequence has a very big impact. By first accessing a particular table, the table is about the table as a driver, which can be applied with certain restrictions, resulting in a smaller row source, so that connections are more efficient, which is what we often say that the first execution restrictions reasons. Generally in the table is read into memory, the application of the table where clause restrictions.
According to two row source of the connection conditions in the different operators can be connected into the equivalent connection (such as WHERE A.COL3 = B.COL4), non-equivalent connections (WHERE A.COL3> B.COL4), outer join (WHERE A.COL3 = B.COL4 (+))。 The above principles are all connected to basically the same connection, so for simplicity period, the following example to introduce the equivalent connection. In the following presentation, are:
SELECT A.COL1, B.COL2
FROM A, B
WHERE A.COL3 = B.COL4;
As an example, suppose A table Row Soruce1, then the operation associated with the corresponding connection as COL 3; B table Row Soruce2, then the operation associated with the corresponding connection as COL 4;

Connection Type:
So far, no matter how the connection operator, typically there are three connection types:
Sort - - merge join (Sort Merge Join (SMJ))
Nested loops (Nested Loops (NL))
Hash join (Hash Join)

Sort - - merge join (Sort Merge Join, SMJ)
Internal connection process:
1) First generate the row source1 data needed, and then operate according to the connection of the data associated with these columns (such as A.col3) sort.
2) Then generate row source2 data needed, and then the data in accordance with the sort source1 join operations associated with the corresponding column (such as B.col4) sort.
3) Finally, both sides of the line to be sorted together to perform the merge operation, about two row source link below in accordance with the join condition is a graphical representation of connection steps:
MERGE
/ \
SORT SORT
| |
Row Source 1 Row Source 2

Ru Guo row source is already connected association is sorted column, then the join operations do not need to sort on the operation, so this connection can greatly improve the connection speed operation, because the sort is an extremely cost resources operations, especially for larger table. The row source, including pre-sorting has been indexed column (such as a.col3 or b.col4 on index) or row source in the previous steps have been sorted. Although the process of merging the two row source is a serial, but parallel access to both row source (such as parallel read data, parallel sort).

SMJ connection examples:
SQL> explain plan for
select / * + ordered * / e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]

Sorting is a time-consuming, resource-intensive operations costs, particularly for large tables. For this reason, SMJ is often not a particularly effective way to connect, but if two row source have been pre-sorted, then the efficiency of this connection method is also quite high.

Nested loops (Nested Loops, NL)
The connection methods are the driving table (external table) concept. In fact, the connection process is a two-layer nested loop, so the goal is to minimize the number of times the outer loop, which is why we return to the small table or small table as the driving row source table (for the outer loop) theory. But this theory is only a general guideline, because, following this theory does not always ensure that the statement produced by I / O times at least. Sometimes do not follow this theoretical basis, but will get better efficiency. If you use this method to determine which table to use as a driving table is very important. Sometimes if the driver chose not to correct the table, the statement will result in poor performance, poor.

Internal connection process:
Row source1 of Row 1 -------------- - Probe -> Row source 2
Row source1's Row 2 -------------- - Probe -> Row source 2
Row source1 of Row 3 -------------- - Probe -> Row source 2
... ....
Row source1 of Row n -------------- - Probe -> Row source 2
The connection process from the inside point of view, need to use row source1 each row to match the row source2 all the rows, so this time as small as possible to keep row source1 and efficient access row source2 (usually achieved by the index) is the impact of this Connect the efficiency of key issues. This is only theoretical guiding principle, the purpose is to make the connection operation produces minimal physical I / O times, and if compliance with this principle, generally make the total physical I / O count at least. But if you do not adhere to this guiding principle, but can use fewer physical I / O operations to connect, that despite the violation of guidelines it! Because at least the physical I / O times is true that we should follow the guiding principles behind the specific case study on the given examples.

In the above connection, we called Row source1-driven table or external table. Row Source2 is known as being the internal probe table or tables.

In NESTED LOOPS connection, Oracle reads the row source1 in each row, then row sourc2 check whether a matching row, all matching rows are placed in the result set and then the row source1 in the next line. This process continues until the row source1 all the rows are processed. This is from the connection operations can be the first matching one of the fastest way line, this type of connection can be used in rapid response to the statement in the need to respond to the speed as the main target.

If the driving row source (external table) is relatively small, and in the inner row source (internal table) has a unique index, or a highly selective non-unique index, the use of this method can get better efficiency. NESTED LOOPS with other connection methods do not have an advantage: can be connected to return line, without having to wait for all operations processed before the connection returns data, which enables fast response time.

If you do not use the parallel operation, the best driving table is applied where those restrictions, you can return fewer rows of the table, so a large table may also be known as the driving table, the key constraints. For parallel query, we often choose a large table as the driving table, because a large table can take advantage of parallel functions. Of course, sometimes the query is not bound to use the parallel query operations do not use parallel operation, high efficiency, probably because the last few rows of each table meet the constraints, and depends on your hardware configuration can support parallel (such as Is there more than one CPU, more hard disk controller), so specific issues and problems.

NL connection examples:
SQL> explain plan for
select a.dname, b.sql
from dept a, emp b
where a.deptno = b.deptno;

Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost = 5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]

Hash join (Hash Join, HJ)
This connection is introduced later in oracle 7.3, in theory more efficient than the NL and SMJ, but only in the CBO optimizer.
Smaller row source to be used to build the hash table and bitmap, the second row source to be used by hansed, and with the first row source to match the generated hash table for further connections. Bitmap is used as a faster way to find, in the hash table to check if there are matching rows. In particular, when the hash table is relatively large and not all fit in memory, this search method is more useful. This connection is also connected in the NL so-called table-driven concept, was built for the hash table and the bitmap table-driven table, when the hash table is built up with the bitmap can be in memory when the efficiency of such a connection high.

HASH connection examples:
SQL> explain plan for
select / * + use_hash (emp) * / empno
from emp, dept
where emp.deptno = dept.deptno;

Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost = 3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP

For hash join effective, it needs to set HASH_JOIN_ENABLED = TRUE, by default this parameter is TRUE, the other, do not forget to also set hash_area_size parameters, so that efficient operation of hash join, hash join as specified in the parameter size of memory to run, too small hash join performance parameters make connections even lower than the other.

In summary, under what circumstances, in what connection method is better:
Sort - - merge join (Sort Merge Join, SMJ):
a) For non-equivalent connection, this connection efficiency is relatively high.
b) If the association has an index on the column, the better.
c) For the two large row source to do to connect, the connection method to connect is better than the NL.
d) However, if sort merge the returned row source is too large, it will again lead to excessive use of rowid in the table in the query data, database performance, because too many I / O.

Nested loops (Nested Loops, NL):
a) If the driving row source (external table) is relatively small, and in the inner row source (internal table) has a unique index, or a highly selective non-unique index, the use of this method can get better efficiency.
b) NESTED LOOPS with other connection methods do not have an advantage: can be connected to return line, without having to wait for all operations processed before the connection returns data,
This enables fast response time.
Hash join (Hash Join, HJ):
a) This method is introduced in oracle7 later, using a more advanced connection theory,
In general, the efficiency should be better than the other two kinds of connections, but this connection can only be used in
CBO optimizer, but also need to set appropriate hash_area_size parameters,
In order to achieve better performance.
b) In two large row source when a connection is made between the relatively good efficiency in a
small row source is able to achieve better efficiency.
c) can only be used for equivalent connections

Cartesian product (Cartesian Product)
When the two row source to do to connect, but no correlation between the conditions they will do in the two row source in the Cartesian product, which is usually caused by a coding oversight (that programmers forget to write the associated condition). Cartesian product is a table for each row in turn with another match all rows in the table. In exceptional cases we can use Cartesian product, such as in star connection, in addition, we want to make use of Cartesian product, otherwise, the result is what they want it!

Note the following statement, in no connection between the two tables.
SQL> explain plan for
select emp.deptno, dept, deptno
from emp, dept

Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost = 5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP

CARTESIAN keyword pointed out between the two tables do Cartesian product. If the table has n rows emp, dept table has m rows, the Cartesian product of the result is to be n * m rows of a result.

To generate the execution plan for a statement can have three ways:
1). The easiest way

Sql> set autotrace on
Sql> select * from dual;
Statement is executed, it will show explain plan and statistics.
The advantage of this statement is its drawback, so that the execution time with a longer view of the way the sql statement, you need to wait for the statement is executed successfully, will return to implement a plan for optimizing the cycle has increased considerably.

If you want to get the execution plan, but do not want to see the statement, the data generated can be used:
Sql> set autotrace traceonly
This would be executed statement. Set autotrace on than advantage is: do not show the query data, but will still output data to the client, so that when more long statement to query the data, the statement execution will take a lot of time, because it is most of the time used in the data transmitted from the database on the client. I generally do not have this method.

Sql> set autotrace traceonly explain
As with the explain plan command. For select statements, not executing select statements, but only produce an implementation plan. But for dml statements, or will execute the statement, different versions of the database may be a small difference. This optimizes execution time is longer in a select statement, greatly reducing the optimization time to solve the "set autotrace on" and "set autotrace traceonly" command is executed for a long time optimization problems, but also bring the question is: do not Statistics generated data, and data through tatistics physical I / O number of statements we can easily determine the efficiency of the pros and cons.

If the statement is executed when an error is encountered, the solution is:
(1) the user to analyze the following:
Sqlplus> @? \ Rdbms \ admin \ utlxplan.sql
(2) with the sys user login
Sqlplus> @? \ Sqlplus \ admin \ plustrce.sql
Sqlplus> grant plustrace to user_name; - - user_name is the user above mentioned analysis

2). With the explain plan command
(1) sqlplus> @? \ Rdbms \ admin \ utlxplan.sql
(2) sqlplus> explain plan set statement_id ='???' for select ... ... ... ... ... ...
Note that using this method does not execute sql statement, so the only list implementation plan does not list the statistics and execution plan exists only in plan_table. Therefore, the statement set autotrace traceonly availability than worse. Need to use the following command to format the output, so this way I use very little:

set linesize 150
set pagesize 500
col PLANLINE for a120
SELECT EXECORD EXEC_ORDER, PLANLINE
FROM (SELECT PLANLINE, ROWNUM EXECORD, ID, RID
FROM (SELECT PLANLINE, ID, RID, LEV
FROM (SELECT lpad ('', 2 * (LEVEL), rpad ('', 80, ''))||
OPERATION | | '' | | - Operation
DECODE (OPTIONS, NULL ,'','('|| OPTIONS | | ')') | | - Options
DECODE (OBJECT_OWNER, null ,'',' OF'''|| OBJECT_OWNER ||'.')|| - Owner
DECODE (OBJECT_NAME, null,'', OBJECT_NAME | |'''') | | - Object Name
DECODE (OBJECT_TYPE, null ,'','('|| OBJECT_TYPE | | ')') | | - Object Type
DECODE (ID, 0, 'OPT_MODE :')|| - Optimizer
DECODE (OPTIMIZER, null ,'',' ANALYZED','', OPTIMIZER) | |
DECODE (NVL (COST, 0) + NVL (CARDINALITY, 0) + NVL (BYTES, 0),
0, null, '(COST =' | | TO_CHAR (COST )||', CARD = '| |
TO_CHAR (CARDINALITY )||', BYTES = '| | TO_CHAR (BYTES )||')')
PLANLINE, ID, LEVEL LEV,
(SELECT MAX (ID)
FROM PLAN_TABLE PL2
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = PL1.ID
AND STATEMENT_ID = PL1.STATEMENT_ID) RID
FROM PLAN_TABLE PL1
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = 0
AND STATEMENT_ID = 'aaa')
ORDER BY RID,-LEV))
ORDER BY ID;

Above these two methods can only be running in this session of the statement execution plan generation, that we need to know which statement has been operating efficiency is poor, we have the sole purpose of these SQL statements to optimize. In fact, in many cases, we will only listen to a customer complained that the system is running very slow now, but we do not know what SQL is caused. At this point there are many ready-made statements can spend more resources to find the statement, such as:
SELECT ADDRESS,
substr (SQL_TEXT, 1,20) Text,
buffer_gets,
executions,
buffer_gets / executions AVG
FROM v $ sqlarea
WHERE executions> 0
AND buffer_gets> 100000
ORDER BY 5;
Thus find the statement for further optimization. Of course, we can also run a session is running all the SQL statements execution plan, which requires tracking of the session, resulting in trace file, and then process the file format used tkprof look, the way this implementation plan useful, because it contains other additional information, such as each stage of SQL statement execution (such as Parse, Execute, Fetch) were all consumed resources (such as CPU, DISK, elapsed, etc.).

3). With dbms_system stored procedure execution plan
Because stored procedures can be tracked using dbms_system another session to issue the sql statement, and record the use of the implementation plan, but also provides other useful information for performance tuning. Its use with some of the above two methods are not the same, so the separate presentation in the appendix. This method is useful for SQL adjust one of the ways, in some cases non-it is not. Details see Appendix.

How to analyze the execution plan

Example 1:
Suppose LARGE_TABLE is a large table, and no index on the username column, then run the following statement:
SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST';
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer = CHOOSE (Cost = 1234 Card = 1 Bytes = 14)
TABLE ACCESS FULL LARGE_TABLE [: Q65001] [ANALYZED]

In this example, TABLE ACCESS FULL LARGE_TABLE is the first operation, meaning that LARGE_TABLE to do full table scan on the table. When the operation is complete, the resulting row source of data is sent to the next steps for processing, in this case, SELECT STATEMENT operation is the final step of this query.

Optimizer = CHOOSE specify the query optimizer_mode, that optimizer_mode initialization parameter specifies the value, it really does not mean that the statement is executed using the optimizer. Decide what the optimizer to use the statement only way is to look behind the cost part. For example, if given the following form, this indicates that the CBO optimizer, cost, said here that the optimizer execution plan cost:
SELECT STATEMENT Optimizer = CHOOSE (Cost = 1234 Card = 1 Bytes = 14)

However, if given the execution plan is similar to the following information, then the optimizer to use RBO, as part of the cost value is empty, or are they not part of the cost.
SELECT STATEMENT Optimizer = CHOOSE Cost =
SELECT STATEMENT Optimizer = CHOOSE
So we followed the information from the Optimizer can be drawn with the statement is executed in the end, what kind of optimizer. In particular, if the Optimizer = ALL_ROWS | FIRST_ROWS | FIRST_ROWS_n, use the CBO optimizer; if the Optimizer = RULE, then the optimizer to use the RBO.

unit cost is not the time it is not cpu & i / o price-weighted value
The cost of different sql comparison is meaningless
Card = Cardinality: get the result set number of lines scanned (CBO assessed value)

cost value of the property is an oracle internal implementation plan to compare the cost of each cost value, so that the optimizer can choose the best execution plan. The cost value of the different statements can not be compared only with a statement on the implementation plan of the different cost values.

[: Q65001] indicates that the part of the query is run in parallel. The data indicates that the operation which is a parallel query slave process handle, so that the operation can be different from the serial execution of the operation.

[ANALYZED] that operate in the referenced object is analyzed, and in the data dictionary of the object of statistical information for the CBO to use.

Example 2:
Assume that A, B, C are not small table and in Table A on a composite index: A (a.col1, a.col2), as a index of attention a.col1 guide columns.
Consider the following query:
select A.col4
from A, B, C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

Statistics
-------------------------------------------------- --------
0 recursive calls
8 db block gets
6 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL * Net to client
430 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
2 sorts (memory)
0 sorts (disk)
6 rows processed

Make connections in the table, only two tables do first connection, then connect the result as a row source, make connections with the rest of the table, in the above example, the connection sequence for the B and A, the first connection, and then re-connect with the C:
B <---> A <---> C
col3 = 10 col3 = 5

If there is no implementation plan, analysis of the above three tables which should take a drive as the first table? From the SQL statement appears in Table B and C only have restrictions on the table, so the first table should be a driver for this two table one, in the end is which one?

Table B are predicates B.col3 = 10, Table B to do so in the full table scan when the restrictions will be in the where clause condition (B.col3 = 10) to spend, resulting in a smaller row source, so Table B should be a driver as the first table. And so, if we do associate with the Form A, Form A can effectively use the index (Table A col1 because as a leading column).

Of course, the above query on the table there predicate C (C.col3 = 5), one might think that the C drive as the first table the table can get better performance. Let us analyze this: If a driver C table as the first table, the table can guarantee the driver generates a small row source, but look at the join condition A.col2 = C.col2, then there is no opportunity to use the index of Table A , because A table col2 column is not leading column, the efficiency of this nested loop very poor, resulting in poor efficiency of the query. So for the NL select the correct drive connection table is very important.

So good to connect the above order of the query (B - -> A) - -> C. If the database optimizer is cost based, it will use to calculate the price to determine the appropriate form with the appropriate drive connection sequence. In general, CBO will choose the correct connection sequence, if the CBO choose the relatively poor connection to the order, we can also use the ORACLE CBO provided hints to make the connection using the correct order. As follows:

select / * + ordered * / A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5

Now select the correct driver the table is so important, so let us look at the execution plan, in the end how the various tables related to table implementation of the plan which should drive the table:
In the execution plan, you need to know which operation is performed first, after which the operation is performed, which determine which table to drive the table useful. Before a determination, if access to the table by rowid, and the value of the rowid from the index scan was more, then scan the index to start the implementation of the plan temporarily removed. Then the rest of the implementation plan in order to determine the implementation of the guiding principle is: the most right, most operations on the first execution. Specific explanation is as follows:
Prevent removal of the index to be scanned to determine the execution plan:
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Implementation plan to see the first three, namely, the letters section, the value of each column as the indentation left a space character. In the left column space, the more value, indicating the value of the indentation column the more value the more the right-hand column. Implementation plan as shown above: the first column is the most indented line 6, that is the most right-hand line; the first column is the line's indentation, like 4,5, and its right-hand of the same degree, but The first column is 4 rows than the first column of the row by the value of 5; talk about up and down relationship, only a continuous, consistent line indent effective.

From this figure we can see, the NESTED LOOPS part, the most right, most operations are on the TABLE ACCESS (FULL) OF 'B', so the first implementation of this operation, so the operation of Table B corresponding to the table for the first drive (external form), natural, A table on the internal table. Can also be seen from the figure, B and A table to do nested loops epigenetic become a new row source, to the row source for sorting, and C of the table corresponding to the sort of row source (applied C.col3 = 5 constraints) to MSJ connect operation. Therefore, the fact can be drawn from the above: B Table A table with the first to do nested loops, then the resulting row source to do with the C table sort - merge join.

By analyzing the implementation of the above program, we can not say that the table must be in C B, A table before being read, in fact, B and C table table could also be read into memory, because the table data is read into memory operation may be parallel. In fact, many operations may be interleaved, because the ORACLE reads the data, is needed if the row is the row where the entire data block is read into memory, and there may be multi-block read.
To see the execution plan, we look at what the key is not the first execution operation, which operation is executed, but the key to watch the connection between the order (such as that which is driven table, which requires a judge from the order of operations), use what type of association and specific access path (such as to determine whether use of the index)

Implementation plan from the table to determine which driver the table, based on our knowledge to determine the table as the driving table (as in the above table to determine the ABC did) is appropriate, if not appropriate, change the SQL statement, the optimizer can Select the right driver table.

For RBO Optimizer:
In the ORACLE documentation said: For the RBO, the with a from clause in the order from right to left select the drive table, the far right of the table for the first drive table, which is the original English text: All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT. However, I do the test, never verified this claim is correct. In my opinion, even in the RBO, is also a set of rules to decide which type of connection as the driving table and which table in the selection index will take into account the current situation may also consider where the limits, but is certainly and where the constraints of the position-independent.

Test:
If I create three tables:
create table A (col1 number (4,0), col2 number (4,0), col4 char (30));
create table B (col1 number (4,0), col3 number (4,0), name_b char (30));
create table C (col2 number (4,0), col3 number (4,0), name_c char (30));
create index inx_col12A on a (col1, col2);
Execute the query:
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

A table will be deleted on the index inx_col12A:
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'C'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'B'

Through these examples above, so that the oracle documentation on my "All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT" This sentence skeptical. At this point, I can not use hints to force the optimizer to use a nested loop, if you use the hints, so you automatically use the CBO optimizer, the optimizer instead of RBO.

For the CBO optimizer:
CBO select the drive according to the statistics table, if there is no statistical information, from left to right in the from clause in the order of selection-driven table. This is the order of selection with RBO opposite. This is the original English text (CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO). I still can not confirm the correctness of this statement. However, after verification: "If ordered hint (in this case certainly with the CBO), the from clause places the order from left to right choice driven by the table," this sentence is correct. In fact, CBO, if there is statistical data (ie tables and indexes were analyzed), the optimizer cost value will automatically decide which type of connection, and select the appropriate drive table, which is where each clause constraints of the location is not in any way. If we want to change the connection type chosen by the optimizer or the driving table, you need to use hints, and specifically the use of hints in the introduction will be given later.

Test:
If I create three tables:
create table A (col1 number (4,0), col2 number (4,0), col4 char (30));
create table B (col1 number (4,0), col3 number (4,0), name_b char (30));
create table C (col2 number (4,0), col3 number (4,0), name_c char (30));
create index inx_col12A on a (col1, col2);

Execute the query:
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 3 Card = 1 Bytes = 110)
1 0 NESTED LOOPS (Cost = 3 Card = 1 Bytes = 110)
2 1 MERGE JOIN (CARTESIAN) (Cost = 2 Card = 1 Bytes = 52)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)
4 2 SORT (JOIN) (Cost = 1 Card = 1 Bytes = 26)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)

select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 5 Card = 55 Bytes = 4620)
1 0 HASH JOIN (Cost = 5 Card = 55 Bytes = 4620)
2 1 HASH JOIN (Cost = 3 Card = 67 Bytes = 4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 82 Bytes = 1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 82 Bytes = 1066)

A table will be deleted on the index inx_col12A:
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 5 Card = 55 Bytes = 4620)
1 0 HASH JOIN (Cost = 5 Card = 55 Bytes = 4620)
2 1 HASH JOIN (Cost = 3 Card = 67 Bytes = 4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 82 Bytes = 1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 82 Bytes = 1066)

select / * + ORDERED * / A.col4
from C, A, B
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 3 Card = 1 Bytes = 110)
1 0 NESTED LOOPS (Cost = 3 Card = 1 Bytes = 110)
2 1 NESTED LOOPS (Cost = 2 Card = 1 Bytes = 84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)
This query prompts can be verified through the correct prompt ORDERED optimizer to select which table as the optimizer.

How to intervene in the execution plan - - to use hints tips
Cost-based optimizer is very clever, in most cases it will select the correct optimizer to reduce the burden on the DBA. But sometimes it s wisdom wrong, choose a poor execution plan, so that the execution of a statement becomes very unresponsive. At this point the DBA will need human intervention, to tell the optimizer to use our access to the specified path or connection type to generate the execution plan, so that the statement runs efficiently. For example, if we believe that for a particular statement, perform a full table scan than to perform an index scan is more effective, then we can instruct the optimizer to use full table scan. In ORACLE, the statement by adding hints (tips) to achieve the purpose of intervention to optimize optimizer.

oracle hints are provided a mechanism to tell the optimizer in accordance with the way we tell it to generate an implementation plan. We can use hints to achieve:
1) the type of optimization
2) cost-based optimizer optimization goal is all_rows or first_rows.
3) The table of access path, is a full table scan or index scan, or direct use of rowid.
4) the type of connection between the table
5) the connection between the order of the table
6) The statement of the degree of parallelism

In addition to "RULE" prompt, but once the use of other tips, the statement will be automatically changed to use the CBO optimizer, then the data dictionary if you are not statistics, it will use the default statistics. It is recommended that you use the CBO or if HINTS tips, the best of the periodic table and index analysis.

How to use hints:
Hints apply only where the sql statements in their block (statement block, the select, update, delete keyword identification), and for other SQL statement or statement does not affect other parts. Such as: using the union operation for the two sql statements, if only in a sql statement on the hints, the hints will not affect the other sql statements.

We can use the annotation (comment) to a statement, adding hints, a block can have only one comment, but comment only on the SELECT, UPDATE, or DELETE keyword behind the

With hints of the syntax:
{DELETE | INSERT | SELECT | UPDATE} / * + hint [text] [hint [text ]]... * /
or
{DELETE | INSERT | SELECT | UPDATE} - + hint [text] [hint [text ]]...
Note:
1) DELETE, INSERT, SELECT and UPDATE are beginning to identify a block of keywords, including tips comments can only appear in the back of these keywords, or prompt invalid.
2) "+" sign indicates that the comment is a hints, the plus sign must immediately follow the "/ *" in the back, without spaces.
3) hint is one of the following description of specific tips, if multiple tips, each between the need to prompt one or more spaces.
4) text is a description of the other comments texts hint

If you do not correct the specified hints, Oracle will ignore the hints, and will not give any errors.

Using the full set of hints:
When using hints, in some cases, to ensure that the optimizer to generate optimal execution plan, we may specify a full set of hints. For example, if a complex query with multiple tables connected to a table if you specify only the INDEX hint (instructions on the access path used in the table index), the optimizer needs to determine the access path should be used for other and the appropriate connection method. Therefore, even if you give a INDEX hint, the optimizer may feel no need to use the tips. This is because we let the optimizer choose the other connection methods and access paths, and methods based on these connections and access paths, the optimizer that the user is given the INDEX hint useless. To prevent this, we have to use a full set of hints, such as: not only specify the index to use, but also specify the connection order of connection methods and such.

Here is an example of a full set of hints, ORDERED that the connection prompted the order, but the table designated for different connection methods:
SELECT / * + ORDERED INDEX (b, jl_br_balances_n1) USE_NL (jb)
USE_NL (glcc glf) USE_MERGE (gp gsb) * /
b.application_id, b.set_of_books_id,
b.personnel_id, p.vendor_id Personnel,
p.segment1 PersonnelNumber, p.vendor_name Name
FROM jl_br_journals j, jl_br_balances b,
gl_code_combinations glcc, fnd_flex_values_vl glf,
gl_periods gp, gl_sets_of_books gsb, po_vendors p
WHERE ...

Instruct the optimizer approach and goal hints:
ALL_ROWS - cost-based optimizer to the target throughput
FIRST_ROWS (n) - cost-based optimizer to the response time goal
CHOOSE - depending on whether there are statistics, the optimizer choose a different
RULE - Use the rule-based optimizer

Example:
SELECT / * + FIRST_ROWS (10) * / employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

SELECT / * + CHOOSE * / employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

SELECT / * + RULE * / employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

Instructions stored in the path of hints:
FULL / * + FULL (table) * /
Specify the table with full table scan
ROWID / * + ROWID (table) * /
Use the rowid of the table specifies access method, the prompt use of less
INDEX / * + INDEX (table [index]) * /
Use the table on the specified index of the table index scan
INDEX_FFS / * + INDEX_FFS (table [index]) * /
Use fast full table scan
NO_INDEX / * + NO_INDEX (table [index]) * /
Do not use the specified indexes on the table access, you can still use other index index scan

SELECT / * + FULL (e) * / employee_id, last_name
FROM employees e
WHERE last_name LIKE: b1;

SELECT / * + ROWID (employees) * / *
FROM employees
WHERE rowid> 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

SELECT / * + INDEX (A sex_index) use sex_index because there are few
male patients * / A.name, A.height, A.weight
FROM patients A
WHERE A.sex = 'm';

SELECT / * + NO_INDEX (employees emp_empid) * / employee_id
FROM employees
WHERE employee_id> 200;

Indicates that the connection order of hints:
ORDERED / * + ORDERED * /
Table according to the order from left to right words to connect
STAR / * + STAR * /
Star indicates the query optimizer to use

SELECT / * + ORDERED * / o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name =: b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

/ * + ORDERED USE_NL (FACTS) INDEX (facts fact_concat) * /

Indicates the connection type hints:
USE_NL / * + USE_NL (table [, table, ...]) * /
Use nested connections
USE_MERGE / * + USE_MERGE (table [, table, ...]) * /
Use sort - - merge join
USE_HASH / * + USE_HASH (table [, table, ...]) * /
Using HASH connection Note: If the table has alias (alias), the above table refers to a table alias, not the real name of the table

Specific test cases:
create table A (col1 number (4,0), col2 number (4,0), col4 char (30));
create table B (col1 number (4,0), col3 number (4,0), name_b char (30));
create table C (col2 number (4,0), col3 number (4,0), name_c char (30));

select A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'B'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'C'

select / * + ORDERED * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 5 Card = 1 Bytes = 110)
1 0 HASH JOIN (Cost = 5 Card = 1 Bytes = 110)
2 1 HASH JOIN (Cost = 3 Card = 1 Bytes = 84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)

select / * + ORDERED USE_NL (AC) * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 4 Card = 1 Bytes = 110)
1 0 HASH JOIN (Cost = 4 Card = 1 Bytes = 110)
2 1 NESTED LOOPS (Cost = 2 Card = 1 Bytes = 84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)

Creating an index:
create index inx_col12A on a (col1, col2);
select A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

select / * + ORDERED * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 5 Card = 1 Bytes = 110)
1 0 HASH JOIN (Cost = 5 Card = 1 Bytes = 110)
2 1 HASH JOIN (Cost = 3 Card = 1 Bytes = 84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)

select / * + ORDERED USE_NL (AC) * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 4 Card = 1 Bytes = 110)
1 0 HASH JOIN (Cost = 4 Card = 1 Bytes = 110)
2 1 NESTED LOOPS (Cost = 2 Card = 1 Bytes = 84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)

select / * + USE_NL (AC) * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
We mean the query is to A, C NL tables do connect, and let A table as in table, but from the point of view the execution plan, did not meet our goal.
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 1 Bytes = 110)
1 0 NESTED LOOPS (Cost = 3 Card = 1 Bytes = 110)
2 1 MERGE JOIN (CARTESIAN) (Cost = 2 Card = 1 Bytes = 52)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 SORT (JOIN) (Cost = 1 Card = 1 Bytes = 26)
5 4 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)

Analysis of the object:
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 5 Card = 8 Bytes = 336)
1 0 HASH JOIN (Cost = 5 Card = 8 Bytes = 336)
2 1 MERGE JOIN (CARTESIAN) (Cost = 3 Card = 8 Bytes = 64)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 2 Bytes = 8)
4 2 SORT (JOIN) (Cost = 2 Card = 4 Bytes = 16)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 4 Bytes = 16)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 30 Bytes = 1020)

select / * + ORDERED * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 5 Card = 9 Bytes = 378)
1 0 HASH JOIN (Cost = 5 Card = 9 Bytes = 378)
2 1 HASH JOIN (Cost = 3 Card = 30 Bytes = 1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 4 Bytes = 16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 30 Bytes = 1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 2 Bytes = 8)

select / * + ORDERED USE_NL (AC) * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 7 Card = 9 Bytes = 378)
1 0 HASH JOIN (Cost = 7 Card = 9 Bytes = 378)
2 1 NESTED LOOPS (Cost = 5 Card = 30 Bytes = 1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 4 Bytes = 16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 30 Bytes = 1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 2 Bytes = 8)

select / * + USE_NL (AC) * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 7 Card = 9 Bytes = 378)
1 0 HASH JOIN (Cost = 7 Card = 9 Bytes = 378)
2 1 NESTED LOOPS (Cost = 5 Card = 30 Bytes = 1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 4 Bytes = 16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 30 Bytes = 1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 2 Bytes = 8)

select / * + ORDERED USE_NL (ABC) * / A.col4
from C, A, B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 35 Card = 9 Bytes = 378)
1 0 NESTED LOOPS (Cost = 35 Card = 9 Bytes = 378)
2 1 NESTED LOOPS (Cost = 5 Card = 30 Bytes = 1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 4 Bytes = 16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 30 Bytes = 1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 2 Bytes = 8)

For this query in any case I did not get the execution plan similar to the following:
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 35 Card = 9 Bytes = 378)
1 0 NESTED LOOPS (Cost = 35 Card = 9 Bytes = 378)
2 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 2 Bytes = 8)
3 1 NESTED LOOPS (Cost = 5 Card = 30 Bytes = 1140)
4 3 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 4 Bytes = 16)
5 3 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 30 Bytes = 1020)

From the above we can see these examples: to the statement by adding HINTS, allowed to perform in accordance with our wishes, sometimes a very difficult task, the need to constantly try different hints. For USE_NL and USE_HASH tip, suggestion, together with the ORDERED prompted, otherwise not easy to specify that the table-driven table.

Specific case studies:

Environment: oracle 817 + linux + array cabinet
swd_billdetail Table 50 million data
SUPER_USER data table 2800 are indexed on the join columns, and super_user corresponds swd_billdetail in a lot of records in the table table and index have been analyzed.

Practical application of the query:
select a.CHANNEL, B.user_class
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;

This analysis led to check out the data too much, is not convenient, so, count (a.CHANNEL | | B.user_class) instead, and count (a.CHANNEL | | B.user_class) operation itself does not take up too much time, so it can accept such an alternative.

Query using the index in the table all the records out SWD_BILLDETAIL method
SQL> select count (id) from SWD_BILLDETAIL;
COUNT (ID)
----------
53,923,574
Elapsed: 00:02:166.00
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 18051 Card = 1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SYS_C001851' (UNIQUE) (Cost = 18051 Card = 54863946)

Statistics
-------------------------------------------------- --------
0 recursive calls
1952 db block gets
158776 consistent gets
158779 physical reads
1004 redo size
295 bytes sent via SQL * Net to client
421 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

The use of full table scan to remove all from SWD_BILLDETAIL table data.
SQL> select count (user_class) from swd_billdetail;
COUNT (USER_CLASS)
-----------------
53,923,574
Elapsed: 00:11:703.07
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 165412 Card = 1 Bytes = 2)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost = 165412 Card = 54863946 Bytes = 109727892)

Statistics
-------------------------------------------------- --------
0 recursive calls
8823 db block gets
1431070 consistent gets
1419520 physical reads
0 redo size
303 bytes sent via SQL * Net to client
421 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

select count (a.CHANNEL | | B.user_class)
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
EXEC_ORDER PLANLINE
---------- ---------------------------------------- -------------------------------------------------- -----------------
6 SELECT STATEMENT OPT_MODE: CHOOSE (COST = 108968, CARD = 1, BYTES = 21)
5 SORT (AGGREGATE) (COST =, CARD = 1, BYTES = 21)
4 NESTED LOOPS (COST = 108968, CARD = 1213745, BYTES = 25488645)
1 TABLE ACCESS (FULL) OF 'SWORD.SUPER_USER' (COST = 2, CARD = 2794, BYTES = 27940)
3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SWD_BILLDETAIL' (COST = 39, CARD = 54863946, BYTES = 603503406)
2 INDEX (RANGE SCAN) OF 'SWORD.IDX_DETAIL_CN' (NON-UNIQUE) (COST = 3, CARD = 54863946, BYTES =)

This query takes a long time, need more than 1 hour.
After running the following information:
COUNT (A. CHANNEL | | B. USER_CLASS)
------------------------------
1186387

Elapsed: 01:107:6429.87

Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 108968 Card = 1 Bytes = 21)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost = 108968 Card = 1213745 Bytes = 25488645)
3 2 TABLE ACCESS (FULL) OF 'SUPER_USER' (Cost = 2 Card = 2794Bytes = 27940)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SWD_BILLDETAIL' (Cost = 39 Card = 54863946 Bytes = 603503406)
5 4 INDEX (RANGE SCAN) OF 'IDX_DETAIL_CN' (NON-UNIQUE) (Cost = 3 Card = 54863946)

Statistics
-------------------------------------------------- --------
0 recursive calls
4 db block gets
1196954 consistent gets
1165726 physical reads
0 redo size
316 bytes sent via SQL * Net to client
421 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

The statement by adding hints, so the oracle optimizer uses nested loop, and a large table as the driving table to generate a new execution plan:
select / * + ORDERED USE_NL (A) * / count (a.CHANNEL | | B.user_class)
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;

EXEC_ORDER PLANLINE
---------- ---------------------------------------- -------------------------------------------------- -----------
6 SELECT STATEMENT OPT_MODE: CHOOSE (COST = 109893304, CARD = 1, BYTES = 21)
5 SORT (AGGREGATE) (COST =, CARD = 1, BYTES = 21)
4 NESTED LOOPS (COST = 109893304, CARD = 1213745, BYTES = 25488645)
1 TABLE ACCESS (FULL) OF 'SWORD.SWD_BILLDETAIL' (COST = 165412, CARD = 54863946, BYTES = 603503406)
3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SUPER_USER' (COST = 2, CARD = 2794, BYTES = 27940)
2 INDEX (RANGE SCAN) OF 'SWORD.IDX_SUPER_USER_CN' (NON-UNIQUE) (COST = 1, CARD = 2794, BYTES =)

This query takes less time, only 20 minutes, the performance is better.
After running the following information:
COUNT (A. CHANNEL | | B. USER_CLASS)
------------------------------
1186387

Elapsed: 00:20:1208.87

Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 109893304 Card = 1 Bytes = 21)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost = 109893304 Card = 1213745 Bytes = 25488645)
3 2 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost = 165412 Card = 54863946 Bytes = 603503406)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUPER_USER' (Cost = 2Card = 2794 Bytes = 27940)
5 4 INDEX (RANGE SCAN) OF 'IDX_SUPER_USER_CN' (NON-UNIQUE) (Cost = 1 Card = 2794)

Statistics
-------------------------------------------------- --------
0 recursive calls
8823 db block gets
56650250 consistent gets
1413250 physical reads
0 redo size
316 bytes sent via SQL * Net to client
421 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

Note that the above two parts of the execution plan corresponding to the statistics seem to have problems, I was re-test, but the problem in the system, need to test ourselves to finish.

Summary:
Because the two queries are used nested loop cycle, when using the table as a driving table which is very important. In the first sql, small table (SUPER_USER) as the driving table, in line with the recommendations of oracle optimization, but due to SWD_BILLDETAIL cn column values ​​in the table there are many duplicates, so the SUPER_USER in each row there are many lines in SWD_BILLDETAIL use the index query the rowid of the row quickly, but then use these to query SWD_BILLDETAIL rowid user_class column of the table value, relatively slowly. Because these rowid is random, and the table is too big, not cached in memory, so almost every time the query needs to read in accordance with rowid physical disk, which is the slow implementation plan the real reason. From the results can be verified: check out the 1,186,387 lines, need to use the rowid from SWD_BILLDETAIL table read 1,186,387 times, and most of the reading from the hard disk.

Opposite, the use of large tables (SWD_BILLDETAIL) as the driving table, so only need a large table full table scan (and will use multi-block read function, each physical I / O to read a few of oracle data block to a read a lot of lines, speed up the execution efficiency), for each line read out, in line with SUPER_USER match, because SUPER_USER table is small, so you can put all the memory, so operating on the fast match , so the sql execution time and SWD_BILLDETAIL table full-table scan is almost time (SWD_BILLDETAIL full table in 11 minutes, while the query in 20 minutes).

Also: If SWD_BILLDETAIL cn the table a unique column values, the first sql execution plan may be the result of the implementation would be pretty good. If SUPER_USER table is also great, such as 500 million lines, the first two sql execution results of implementation of the plan but they may be poor. In fact, if SUPER_USER table is small, the first two sql statement execution plan if you do not use SUPER_USER table index, the query may be faster, I did not test this.

Therefore, in the performance adjustment, to a concrete analysis of specific issues, there is no uniform standard.

相关文章
  • 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 Implementation Plan Detailed 2011-05-06

    Oracle Implementation Plan Detailed --- Of: TTT BLOG This article addresses: http://blog.chinaunix.net/u3/107265/showart_2192657.html --- Description: Full details of this oracle concepts related to the implementation plan, access to the data access

  • ORACLE implementation plan of some basic concepts 2011-03-22

    ORACLE implementation plan of some basic concepts (1) One. Related Concepts Rowid concept: rowid is a pseudo-column, since it is pseudo column, this column is not user-defined, but the system itself to add the. For each table has a rowid pseudo-colum

  • ORACLE implementation plan of some basic concepts 2 2011-03-22

    ORACLE implementation plan of some basic concepts (2) III. The connection between the table Join an attempt to combine the two predicate table, one can only connect two tables, table join is also called table association. In the following description

  • Oracle Implementation Plan (Explain Plan) Description 2011-07-07

    http://blog.csdn.net/tianlesoftware/article/details/5827245 http://blog.csdn.net/tianlesoftware/article/details/5826546 If you want to analyze the SQL performance on a particular issue, usually we have to look at the SQL execution plan to see each st

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • How to read ORACLE implementation plan 2011-08-11

    First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads blocks of data. The smallest amount of d

  • ORACLE Implementation Plan Analysis 2011-09-26

    ORACLE analysis of an implementation plan, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads bloc

  • How to see Oracle implementation plan 2011-01-14

    oracle explain an implementation plan. Related Concepts 1 · rowid, pseudo-column: is the system to add their own, each table has a pseudo-column is not physically exist. It can not be modified, deleted, and added, rowid in the life cycle of the line

  • Oracle Implementation Plan 2010-03-09

    Summary: SQLPLUS under the automatic display, look at their statements or implementation plan will be implemented. Especially in the implementation of the UPDATE / DELETE statement, please do note, ORACLE is the first implementation of the script als

  • ORACLE SQL Optimization - ORACLE Implementation Plan 2010-03-16

    Last login: Mon Feb 8 14:13:19 2010 from 192.168.1.249 ipnet -Bash-3.00 $ -Bash-3.00 $ ipnet -Bash: ipnet: command not found -Bash-3.00 $ su - oracle Password: eastcsu: incorrect password -Bash-3.00 $ eastcom -Bash: eastcom: command not found -Bash-3

  • Oracle execution plan from the start view 2010-08-29

    Consider the following situation (1) If your data table to manage 40 million video information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly feel its efficiency over it? If users complain about slow resp

  • (Installed) oracle execution plan 2011-07-20

    One. Related Concepts Rowid concept Recursive Sql concept Predicate (verb) DRiving Table (table-driven) Probed Table (by probe table) Combination index (concatenated index) Selectivity (selectivity) II. oracle access to the data access methods 1) Ful

  • Oracle execution plan from the beginning to view 2010-08-29

    Consider the following scenario, (1) If your data tables to manage 30 million line of card user information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly felt it efficient? If users complain about slow r

  • Note ORACLE bind variables 2010-08-10

    The meaning of bind variables: In oracle, in regard to a submission of the sql statement, there are two optional resolution process, called the hard analysis, called soft parsing. Hard parse through analysis, development of the execution path, optimi

  • 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

  • Why Oracle will sometimes use the index to find data? - Force Oracle to use the best "implementation plan" 2011-06-01

    [Abstract] you use SQL, a query to the database when you release, Oracle will be generated with an "implementation plan" that is, what kind of data that the statement will search through the implementation of the program. Search for program sele

  • Why Oracle will sometimes use the index to find data? - To force Oracle to use the best "implementation plan" 2011-06-01

    [Abstract] you use the SQL language, a query to the database release, Oracle will have with an "implementation plan", that is what the statement by the data search program execution. Search program selection is closely related to the Oracle opti

  • Oracle SQL with AutoTRACE analysis of the implementation plan 2010-09-06

    Turn http://hi.baidu.com/bystander1983/blog/item/d6c26a53fe457b040cf3e3cd.html * Environment: windowsXP + Oracle10gR2 * AutoTRACE is to analyze the SQL execution plan, the efficiency of a very simple and convenient tool * / AUTOTRACE is a function of

  • Oracle database implementation plan some basic concepts 2011-08-28

    Oracle database implementation plan some basic concepts One. Related Concepts Rowid concept: rowid is a pseudo-column, since it is pseudo-column, then this column is not user-defined, but the system of their own to add. For each table has a rowid pse