This article describes Oracle's query optimizer, a key component of the database, which gives Oracle users excellent performance. Oracle's query optimization technology is unmatched in functionality, and this article discusses all the important areas of query optimization in detail.
Brief introduction
What is the query optimizer.
Query optimization is critical to the performance of relational databases, especially for performing complex SQL statements. The query optimizer determines the best strategy for executing each query.
For example, the query optimizer chooses whether to use indexes for a specified query, and which join technology to use when joining multiple tables. Such decisions have a significant impact on the execution performance of SQL statements, and query optimization is a key technology for each application, ranging from the operating system to the data Warehouse, from the analysis system to the content management system. The query optimizer is completely transparent to applications and end users.
Because an application can generate very complex SQL statements, the query optimizer must be well constructed and powerful to ensure good execution performance. For example, the query optimizer converts SQL statements so that complex statements can be converted into equivalent but better performing SQL statements. A typical feature of the query optimizer is overhead. In a cost-based optimization strategy, generate multiple execution plans for a given query, and then estimate the cost for each plan. The query optimizer chooses the plan with the lowest estimated cost.
What does Oracle provide in query optimization?
Oracle's optimizer can be described as the industry's most successful optimization program. Based on cost optimization program since 1992 with Oracle7 launched, through 10 years of rich experience in the actual user, and constantly improve and improve. A good query optimization program is not based on purely theoretical hypotheses and predicates developed in the laboratory, but is developed and adapted by the needs of the actual user.
Oracle's query optimizer has more applications than any other query optimizer in database applications, and Oracle's optimizer has been improved by actual application feedback.
Oracle's optimizer contains 4 major components (these sections are discussed in detail in the following sections):
SQL statement conversion: In Query optimization Oracle uses a series of sophisticated techniques to transform SQL statements. The purpose of this step in query optimization is to convert an existing SQL statement into an SQL statement with the same semantics and more efficient processing.
Execution Plan selection: For each SQL statement, the optimizer selects an execution plan (which can be viewed using either Oracle's explain program tool or Oracle's "V$sql_plan" view). The execution plan describes all the steps in executing SQL, such as the order in which the tables are accessed, how they are joined together, and whether the tables are accessed through the index. The optimizer designs many possible execution plans for each SQL statement and selects the best one.
Cost model and statistics: Oracle's optimizer relies on cost estimates for all individual operations that execute SQL statements. If you want the optimizer to choose the best execution plan, you need the best way to estimate the cost. Overhead estimates require detailed knowledge of the I/O, CPU and memory resources required for each query, statistics related to database objects (tables, indexes, and materialized views), and performance information about the hardware server platform. The process of collecting these statistics and performance information should be efficient and highly automated.
Dynamic run time Optimization: Not every aspect of SQL execution can be optimized beforehand. Oracle therefore dynamically adjusts the query processing policy based on the current database load. The goal of this dynamic optimization is to achieve optimized execution performance, even though each query may not be able to obtain the ideal CPU or memory resources. Oracle has another original optimizer, the Rule-based optimizer. The optimizer is backward-compatible only, and the next version of Oracle will no longer be supported. Most Oracle users currently use cost-based optimization programs. All major application vendors, such as Oracle Applications, SAP and PeopleSoft, listed only, and a large number of recently developed client applications use cost-based optimizations for good execution performance, so this article only covers cost-based optimizations.
SQL Statement Conversions
There are several ways to use SQL statements to represent complex queries. The type of SQL statement that is submitted to the database is typically the type of SQL that an end user or application can generate in the simplest way. But these human-written or machine-generated query formulas are not necessarily the most efficient SQL statements for executing queries. For example, an application-generated query usually contains some irrelevant conditions that can be removed. Alternatively, some additional conditions from a query predicate should be added to the SQL statement. The purpose of the SQL conversion statement is to convert the given SQL statement to a SQL statement that has the same idiom meaning (that is, the SQL statement that returns the same result) and has a better performance.
All of these transformations are completely transparent to the application and end users. SQL statement conversions are implemented automatically during query optimization.
Oracle implements a variety of SQL statement conversions. These conversions can be divided into two categories:
Heuristic Query Transformation: This conversion occurs when possible for incoming SQL statements. This conversion can provide the same or better query performance, so Oracle knows that implementing this transformation does not degrade performance. Cost-based search
Query conversion: Oracle uses a cost-based approach for several types of queries conversion. With this approach, the converted query is compared to the original query, and Oracle's optimizer picks the best execution strategy from it.
Several examples of Oracle conversion technologies are discussed in the following sections. These examples are not authoritative and are used only to help readers understand the key conversion technologies and their benefits.
Probing query conversions
Simple View Merge
Perhaps the simplest query transformation is a view merge. For queries that contain views, you can usually remove the view from the query by merging the view definition with the query. For example, take a look at the very simple view and query below.
CREATE VIEW Test_view as SELECT ename, Dname, SAL from EMP E, DEPT D WHERE e.deptno = D.deptno;
SELECT ename, dname from Test_view WHERE SAL > 10000;
Without any conversion, the only way to process the query is to join all of the EMP's rows to all rows in the Dept table, and then filter those rows with the appropriate SAL values.
If you use view merging, the above query can be converted to:
SELECT ename, dname from EMP E, DEPT D WHERE e.deptno = D.deptno and e.sal > 10000;
The predicate ' sal>10000 ' can be used before joining the EMP and Dept tables when processing the converted query. This transformation greatly improves the execution performance of a query by reducing the amount of data being joined. Even in such a very simple example, the benefits and importance of query conversions are obvious.
Complex view Merge
Many view merge operations are straightforward, as in the example above. However, a more complex view, such as a view that contains a group by or distinct operator, is not as easy to combine. Oracle provides some advanced technologies for merging such complex views.
Consider the following view with a GROUP by statement. In this example, the view calculates the average wage for each department.
CREATE VIEW Avg_sal_view as SELECT DEPTNO, AVG (SAL) avg_sal_dept from EMP GROUP by DEPTNO
The purpose of the query is to find out the average wage for each department in Oakland:
SELECT Dept.name, avg_sal_dept from DEPT, Avg_sal_view WHERE DEPT. DEPTNO = Avg_sal_view. DEPTNO and DEPT. LOC = ' Oakland '
can be converted to:
SELECT Dept.name, AVG (SAL) from DEPT, EMP WHERE DEPT. DEPTNO = EMP. DEPTNO and DEPT. LOC = ' Oakland ' GROUP by DEPT. ROWID, Dept.name
The performance benefits of this special transformation are immediately apparent: the conversion joins and filters the EMP data before grouping aggregations, rather than aggregating all the data of the EMP table before the join.
Subquery "Flattening"
Oracle has some transformations that transform different types of subqueries into joins, Semi joins, or back joins. As a technical example in this field, let's look at the following query to find out which departments have employees with a salary of over 10000:
Select D.dname from DEPT D where D.deptno to (select E.deptno from EMP E where e.sal > 10000)
There are a series of execution plans that can optimize this query. Oracle will consider these possible different transformations and choose the best plan based on overhead.
If no conversion is made, the execution plan for this query is as follows:
OPERATION object_name OPTIONS
SELECT STATEMENT
FILTER
TABLE ACCESS DEPT Full
TABLE ACCESS EMP Full
According to the execution plan, each row of the Dept table is scanned for an EMP record that satisfies the subquery criteria. Typically, this is not an efficient execution strategy. However, query conversions can achieve more efficient planning.
One of the possible plans for the query is to execute the query as a "semi-join." A "semi-join" is a special type of join that eliminates redundant values from the table in the join (which is actually the original semantics of the subquery). In this example, the optimizer selects a hash half join, although Oracle also supports sorting-merging and nesting-loop semi-joins:
OPERATION object_name OPTIONS
SELECT STATEMENT
HASH JOIN SEMI
TABLE ACCESS DEPT Full
TABLE ACCESS EMP Full
Because SQL does not have a direct syntax for a semi join, this converted query cannot be represented using standard SQL. However, the converted pseudo SQL will be:
SELECT dname from EMP E, DEPT D WHERE d.deptno e.deptno and e.sal > 10000;
Another possible plan is for the optimizer to decide to use the Dept table as the inner table of joins. In this case, the query executes as a normal join, but the EMP table is sorted specifically to eliminate the redundant department numbers:
OPERATION object_name OPTIONS
SELECT STATEMENT
HASH JOIN
SORT UNIQUE
TABLE ACCESS EMP Full
TABLE ACCESS DEPT Full
The converted SQL statement is:
Select D.dname from (select DISTINCT DEPTNO from EMP) E, DEPT D WHERE e.deptno = D.deptno and e.sal > 10000;
As with view merging, child query flattening is also the basic optimization method to get good query execution performance.
Transitive predicate generation
In some queries, a predicate in one table can be converted to a predicate in another table because of the join relationship between tables. Oracle interprets new predicates in this way, which are called transitive predicates. For example, look at a query to find out all the goods shipped on the day of the order:
SELECT COUNT (DISTINCT O_orderkey) from order, LINEITEM WHERE O_orderkey = l_orderkey and o_orderdate = L_shipdate and O_or Derdate BETWEEN ' 1-jan-2002 ' and ' 31-jan-2002 '
With transitivity, predicates in this order table can also be used in LineItem tables:
SELECT COUNT (DISTINCT O_orderkey) from order, LINEITEM WHERE
From: http://blog.csai.cn/user1/16350/archives/2006/8241.html
