Oracle Optimizer: Migrate to cost-based optimizer ----- Series 1.3
3.2.3 Available CBO mode
CBO has two operating modes: all_rows and first_rows.
The target of First_ROWS is to return as quickly as possible. This mode tells the optimizer response time being the most important. It is preferred for nested connection. First_ROWS is also handled in consideration of cost as a main rule. Usually rules are used as an indexed plan as a plan for a full table scan as an access path, and the Order By clause leads to index access, and so on.
From the 9i version, the number of lines in the First prompt can be set in the parameter, and FRIST_ROWS_N (N can be 1, 10, 100 or 1000), setting with different applications.
All_Rows processes all rows to a given query before returning the output results. It forces the optimizer to consider the minimum resource occupation and the best throughput. All_ROWS prefer to sort merge operation
For an OLTP system, First_ROWS is an ideal option for fast response time.
Applications for batching directions means using all_rows. Note that the plan to return to Nth line with the fastest response time may not be an ideal plan, if the system requires a complete result. Therefore, according to the needs of the application, it should be determined according to the needs of the application .
When the database size changes, the CBO dynamically adjusts its execution path, so if you work well for the same application, you run well in a database setting, you don't have to pay attention to it. If you are in two databases The settings and statistics are different, this will happen .. To prevent this situation, it is considered to use optimizer persistence. Later sections will be mentioned.
3.2.3 CBO basic terminology
When the CBO analysis statement, the following terms will be used frequently.
COST (cost)
In CBO, the calculation of costs involves each operating unit. How much cost is logically actually does not have documentation or to the outside world, and even change with the versions.
Cardinality (base)
The number of single rows in the index and the number of in the table. The query base is the number of hopes to return.
SELECTIVITY
The number of single values is considered selective. For example, if a table has a 10,000 line, an index that has been created is 4000 single value in a column, then the selectivity of the index is 4000 / 10000 * 100 = 40%, the alternative index in the column is not 100%.
TRANSIVITY
For queries, CBO generates processing of additional predicates. It enables optimizers to consider additional execution paths. For example, if a predicate of A = B and B = C is provided in the query, an optimizer may add additional predicates to imply A = C.
STATISTICS (statistics)
For a variety of different data collected many must-have to support objects .. For CBO execution plans, it is critical.
Join Methods (connection method)
Oracle is connected with a hash, merge sorting and nested connections. A more fast phase compared to a query is more than other methods. The connection method should be evaluated for a single query.
FTS (full table scan)
Full table scan involves queries, scanning from the first block to the last distributed block. It is extremely expensive for large tables and should be avoided.
Index Scan (Index Scan)
Recommended random access on a table by using one or more indexes on the table.
