Shulou(Shulou.com)06/01 Report--

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

There are two ways to optimize the Oracle optimizer:

Cost-based Optimization Rule of CBO

Rule-based optimization rules for RBO

The initialization parameter optimizer_mode controls the behavior of optimizer optimization

SQL > show parameter optimizer_modeNAME TYPE VALUE -optimizer_mode string ALL_ROWS

Optimizer_mode has the following five values

Whether CHOOSE uses CBO or RBO, based on the existence of statistics, CBO is used if there is a statistical system, otherwise RBO is used.

ALL_ROWS is based on CBO and adopts an optimal execution plan that returns all results as soon as possible.

Based on CBO, FIRST_ROWS_n returns the first n rows of data as soon as possible, and the value of n is 1mem10, 100pence1000.

FIRST_ROWS is based on a combination of CBO and heuristics to find a way to return the first few rows as quickly as possible; this parameter is mainly used for backward compatibility.

RULE adopts the optimization rule based on CBO.

The version of Oracle 11g is valid only for the middle three parameters, and FIRST_ROWS.

Let's look at the impact of the optimizer on queries.

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 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17) | 00:00:01 | | 1 | MERGE JOIN | 14 | 812 | 6 (17) | 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 (25) | 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 Murray- -| 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")

CBO consists of the following three components

Query Transformer

Estimator

Plan Generator

1. Query Transformer

The input part is the sql that has been parsed by the parser.

Query transformation includes the following techniques:

View Merging (View merge)

Each view involved in the query is expanded by parser and separated into query blocks. The query block essentially represents the definition of the view. Merge it with the rest of the query into an overall execution plan, and the converted statement basically does not contain views. Let's give an example.

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 employees WHERE department_id = 50

Make the following query

SELECT employee_idFROM employees_50_vw WHERE employee_id > 150

After the optimizer is converted, the query becomes

SELECT employee_idFROM employeesWHERE department_id = 50 AND employee_id > 150

Predicate Pushing (predicate progression)

Pushing the predicate from the internal query block to an unmerged query block can make the predicate condition selected earlier, filter out the unwanted data rows earlier, and improve the efficiency. This way can also be used to allow the use of some indexes.

Suppose you have the following view

CREATE VIEW all_employees_vw AS (SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees) UNION (SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers)

We send out this 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 (subquery unnesting)

The most typical is that a subquery is transformed into a table join. The main difference between it and view merging is that its subquery is located in the where clause and is unnesting detected by the converter.

Suppose there is such a query

SELECT * FROM salesWHERE cust_id IN (SELECT cust_id FROM customers)

After query conversion

SELECT sales.* FROM sales, customersWHERE sales.cust_id = customers.cust_id

Query Rewrite with Materialized Views

Suppose you create a materialized view

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, 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_month_desc

After query conversion

SELECT calendar_month, dollarsFROM cal_month_sales_mv

II. Estimator

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

Selectivity

The first measure here, selectivity, represents the ratio of the number of rows hit by sql to the rowset. 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, filtering a certain amount of rows in the rowset, and the selectivity of the predicate is a ratio, which indicates the proportion of the remaining rows to the original rowset after being filtered by the predicate. The value is between 0.0 and 1.0, where 0.0 indicates that no rows are selected in the rowset; 1.0 means that all rows in the rowset are selected. If no statistics are available, the evaluator selectively assigns an internal default, which varies from predicate to predicate. For example, if the internal default value of an equality predicate (last_name='Smith') is lower than that of a range predicate (last_name > 'Smith'), the evaluator assumes that the number of rows returned by the equality predicate is less than the range predicate. When statistics are available, the evaluator uses statistics to estimate selectivity. For example, for an equality predicate (last_name='Smith'), the selective 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 optional value is: coun (last_name) where last_name='Smith' / count (last_name) where last_name is not null. It can be seen that the application of histogram on the fields with skewed data can help CBO to make accurate selective evaluation.

Cardinality

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

Base cardinality: the number of rows in the base table. The basic cardinality is obtained during the table analysis. If no statistics are available for the table, the evaluator uses the number of extents in the table to estimate the base.

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

Join cardinality (Join cardinality): the number of rows generated by two rowsets after they are joined. A join is a Cartesian product generated by two rowsets, and the result is filtered by the join predicate. Therefore, the join cardinality is the product of the cardinality of two rowsets and the selectivity of the join predicate.

Distinct cardinality (Distinct cardinality): 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, for a rowset with 100 rows, if there are 20 rows left after a field distinct, the distinct cardinality is 20.

Group cardinality (Group cardinality): the number of rows generated by a rowset after GROUP BY is applied. The Group cardinality depends on the distinct cardinality of the fields in each group and the number of rows in the rowset.

Cost

Cost is used to describe the use of a unit of work or resource. CBO uses disk ICBO O, CPU, and memory usage as a unit of work, so the cost of CBO usage can be described as the number of disk ICBO O used during the execution of an operation, as well as the total amount of disk and memory used.

III. Plan Generator

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.