Oracle Learning Performance Optimization (eight) optimizer

We know that in the process of parsing SQL statements, there is a process called optimization. Oracle has a component called optimizer specifically designed to handle SQL optimizations. After considering many of the factors related to query conditions and object references, the optimizer can determine the most efficient way to execute an SQL statement. For any SQL statement, the optimizer optimization results can greatly affect the execution time.

There are two ways to optimize an Oracle optimizer:

• CBO cost-Based optimization law

• RBO rule-Based Optimization law
  

Initialization parameter Optimizer_mode controls optimizer optimization behavior

Sql> Show parameter Optimizer_modename TYPE VALUE----------------------------------------------------------- ----------------------------------------Optimizer_mode String all_rows

Optimizer_mode has the following five values

• CHOOSE use CBO or RBO, based on the existence of statistical information, if there is a statistical system using CBO, otherwise use RBO.

• All_rows is based on the CBO and uses an optimal execution plan that returns all results as soon as possible.

• First_rows_n based on the CBO, returns the first n rows of data as soon as possible, and the value of n is 1,10,100,1000

• First_rows a method based on the combination of CBO and heuristics to find the quickest way to return to a few previous rows; This parameter is primarily used for backwards compatibility.

• Rule uses a CBO-based optimization rule.

The Oracle 11g version is only valid for the intermediate three parameters and is not recommended for use with first_rows.

> Let's look at the effect of the optimizer on the query

sql> alter system set optimizer_mode=all_rows; System altered. sql> conn scott/tigerconnected.sql> set autot traceonly expsql>  select * from emp,dept where emp.deptno=dept.deptno; Execution plan----------------------------------------------------------plan hash value:  844388907----------------------------------------------------------------------------------------| id   | Operation     | Name    | Rows   | Bytes | Cost  (%CPU) | time     |--------------------- -------------------------------------------------------------------|   0 | select  STATEMENT     |       |     14 |   812 |     6 (17)| 00:00:01 | |    1 |  MERGE JOIN     |        |    14 |   812 |      6 (| 00:00:01 |) |    2 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     4 |    80 |      2  (0) | 00:00:01 | |    3 |    index full scan     |  PK_DEPT |     4 |       |      1  (0) | 00:00:01 | | *  4 |   sort join     |        |    14 |   532 |     4 (+) | 00:00:01 | |    5 |    table access full     |  EMP     |    14 |   532 |      3  (0) | 00:00:01 |------------------------------------------------------ ----------------------------------predicate information  (identified by operation id): ---------------------------------------------------   4 - access ("EMP"). DEPTNO "=" DEPT "." DEPTNO ")        filter (" EMP "). DEPTNO "=" DEPT "." DEPTNO ")

After modifying the optimizer mode

sql> alter session set optimizer_mode=first_rows_1; Session altered. sql> select * from emp,dept where emp.deptno=dept.deptno; Execution plan----------------------------------------------------------plan hash value:  3625962092----------------------------------------------------------------------------------------| id   | Operation     | Name    | Rows   | Bytes | Cost  (%CPU) | time     |--------------------- -------------------------------------------------------------------|   0 | select  STATEMENT     |       |      1 |    58 |     3  (0) | 00:00:01  | |    1 |  NESTED loops     |       |        |       |    |        | |    2 |   NESTED LOOPS     |        |     1 |    58 |      3  (0) | 00:00:01 | |    3 |    table access full     |  EMP     |     1 |    38  |     2  (0) | 00:00:01 | | *  4 |    index unique scan     |  pk_dept |     1 |       |     0  (0) | 00:00:01 | |    5 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |      1  (0) | 00:00:01 |------------------------------------------------------------------ ----------------------predicate information  (identified by operation id):------------ ---------------------------------------   4 - access ("EMP"). DEPTNO "=" DEPT "." DEPTNO ")

The CBO consists of the following three components

• Query Transformer

• Estimator

• Plan Generator

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/71/A1/wKiom1XVPb3ytxaUAADszr2bUaU175.jpg "title=" CBO. PNG "alt=" Wkiom1xvpb3ytxauaadszr2buau175.jpg "/>

First, Query Transformer

The input section is SQL that has been parsed by the parser.

The query transformation includes the following techniques:

View merging (views merge)

Each view involved in the query is parser expanded and separated into a single query block. The query block essentially represents the definition of the view. Merge it with the rest of the query into a total execution plan, and the converted statement basically does not include the view. Let's illustrate

Suppose there is such a view

CREATE VIEW EMPLOYEES_50_VW as SELECT employee_id, last_name, job_id, salary, commission_pct, department_id from Emplo Yees WHERE department_id = 50;

Make the following query

SELECT employee_idfrom employees_50_vw WHERE employee_id > 150;

After the optimizer transforms, the query becomes

SELECT employee_idfrom employeeswhere department_id = employee_id > 150;

predicate pushing (predicate propulsion)

The predicate is pushed from the inner query block into a non-consolidated query block, which allows the predicate condition to be selected earlier, to filter out unnecessary rows of data earlier, to improve efficiency, and also to allow the use of certain indexes in this way.

Suppose you have the following view

CREATE VIEW all_employees_vw as (SELECT employee_id, last_name, job_id, commission_pct, department_id from employee s) UNION (SELECT employee_id, last_name, job_id, commission_pct, department_id from Contract_workers);

We issue such a query

SELECT last_namefrom All_employees_vwwhere department_id = 50;

After conversion

Select Last_namefrom (select employee_id, last_name, job_id, commission_pct, department_id from Employees         WHERE department_id=50 UNION SELECT employee_id, last_name, job_id, commission_pct, department_id From Contract_workers WHERE department_id=50);

Subquery unnesting (sub-query solution nesting)

The most typical is that a subquery is transformed into a table connection, and the main difference between it and the view merge is that its subquery is in the WHERE clause, and is detected by the converter for its nesting.

Suppose there's a query like this

SELECT * from Saleswhere cust_id in (select cust_id from Customers);

After the query is converted

SELECT sales.* from sales, customerswhere sales.cust_id = customers.cust_id;

Query Rewrite with materialized views

Suppose a materialized view is established

CREATE materialized VIEW cal_month_sales_mv ENABLE QUERY REWRITE as SELECT T.calendar_month_desc, SUM (S.amount_sold) as Dollars from sales S, the Times t WHERE s.time_id = t.time_id GROUP by T.calendar_month_desc;

Execute the following query

SELECT T.calendar_month_desc, SUM (S.amount_sold) from Sales s, times twhere s.time_id = T.time_idgroup by T.calendar_mon Th_desc;

After the query is converted

SELECT Calendar_month, Dollarsfrom cal_month_sales_mv;

Second, estimator

The estimator determines the total cost of a given execution plan. Estimates generate three different types of measures to achieve this goal:

• Selectivity

The first measure here, selectivity, represents the ratio of the number of rows in SQL hit to the row set. The so-called rowset can be a table, a view, or an intermediate result of a join or group by operation. Selectivity is related to predicates in the query, such as Last_name= ' Smith ', or a UNION predicate last_name= ' Smith ' and job_type= ' clerk '. A predicate acts as a filter, filters a certain amount of rows in a rowset, and the selectivity of a predicate is a ratio that represents the proportion of rows remaining in a rowset after a predicate has been filtered. Its value is between 0.0 and 1.0, and 0.0 indicates that no rows are selected in the rowset, and 1.0 indicates that all rows in the rowset are selected. If no statistics are available, the evaluator assigns an internal default value to the selectivity, which differs from the predicate. For example: The internal default value of the equality predicate (Last_name= ' Smith ') falls below the range predicate (last_name> ' Smith '), and the evaluator assumes that the number of rows returned by the equality predicate is less than the range predicate. When statistical information is available, the evaluator uses statistics to estimate selectivity. For example: for an equality predicate (last_name= ' Smith '), the selectivity value is the reciprocal of distinct last_name: (1/count (distinct last_name)). However, if there is a histogram (histogram) on the Last_Name field, the selectivity value is: Coun (last_name) where last_name= ' Smith '/count (last_name) where last_name is not NULL. It can be seen that applying histograms to data-skewed fields helps the CBO perform an accurate selectivity assessment

• Cardinality

The cardinality is the number of rows in the row set. The cardinality is divided into:

Base Base cardinality: is the number of rows in the base table. The base cardinality is obtained during table analysis. If the table does not have statistics available, the evaluator uses the number of table Central (extents) to estimate the base cardinality.

Valid cardinality (Effective cardinality): is the number of rows selected from the base table. The valid cardinality is related to the specific predicate and field. The valid cardinality is based on the selectivity of the base cardinality and all predicates acting on the table, and if no predicate acts on the table, the valid cardinality is equal to the base cardinality.

Connection cardinality (Join cardinality): is the number of rows produced by the two rowset after a connection. A connection is a Cartesian product produced by two rowsets, filtered by the join predicate. Therefore, the connection cardinality is the product of the two rowset cardinality and the JOIN predicate selectivity.

Distinct cardinality (Distinct cardinality): Is the number of rows after the field Distinct of a rowset. One

The distinct cardinality of the rowset is based on the data in the field. For example: a rowset with 100 rows, if there are 20 rows left after a field distinct, the distinct cardinality is 20.

Group Base (group cardinality): is the number of rows that a rowset produces after applying Group by. The group cardinality relies on the distinct cardinality of the fields in each group and the number of rows in the rowset.

• Cost

The cost is used to describe the work unit or Resource usage. The CBO uses disk I/O, CPU, and memory as a unit of work, so the cost of the CBO can be described as the amount of disk I/O used during the execution of an operation and the total CPU and memory usage.

Iii. Plan Generator

The main function is to generate a variety of possible plans for a given query and pick the one with the lowest cost.

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1686407

Oracle Learning Performance Optimization (eight) optimizer