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

The cost-based optimizer is smart, and in most cases it will choose the right optimizer, reducing the burden on DBA. But sometimes it is clever and misguided and chooses a poor execution plan, which makes the execution of a statement extremely slow. At this point, DBA is required to intervene artificially to tell the optimizer to generate an execution plan using the access path or connection type we specify to make the statement run efficiently. For example, if we think that performing a full table scan is more efficient than performing an index scan for a particular statement, we can instruct the optimizer to use a full table scan. In Oracle, the purpose of interfering with optimizer optimization is achieved by adding Hints (hints) to the statement.

Using hint in your code is not recommended, and using hint in your code makes it impossible for CBO to choose the correct execution plan based on the actual state of the data. After all, the data is constantly changing, and the CBO after 10g is getting better and better, and in most cases we should leave it to Oracle to decide what execution plan to adopt. Oracle Hints is a mechanism for telling the optimizer to generate an execution plan the way we told it. We can use Oracle Hints to do this:

Hints for Optimization Approaches and Goals

Hints for Access Paths

Hints for Query Transformations

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

The syntax to implement the prompt:

{DELETE | INSERT | SELECT | UPDATE} / * + hint [text] [hint [text]]. * / or {DELETE | INSERT | SELECT | UPDATE}-- + hint [text] [hint [text]].

Hints for Optimization Approaches and Goals

The / * + ALL_ROWS*/ statement block chooses a cost-based optimization method and obtains the best throughput to minimize resource consumption.

/ * + FIRST_ROWS (n) * / statement block chooses a cost-based optimization method and obtains the best response time to minimize resource consumption.

/ * + CHOOSE*/ block relies on statistics to decide whether to choose CBO or RBO

/ * + RULE*/ statement block chooses a rule-based optimization method.

Example:

SQL > select / * + ALL_ROWS*/ * 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") SQL > select / * + FIRST_ROWS (1) * / * from emp Dept where emp.deptno=dept.deptno 14 rows selected.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")

Hints for Access Paths

/ * + FULL (TABLE) * / full table scan

SQL > select empno from emp 14 rows selected.Execution Plan---Plan hash value: 179099197Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 1 | 4 | 1 (0) | 00:00:01 |- -

After adding hint

SQL > select / * + FULL (emp) * / ename from emp 14 rows selected.Execution Plan---Plan hash value: 3956160932 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | EMP | 1 | 6 | 2 (0) | 00:00:01 |- -

/ * + ROWID (TABLE) * /

SQL > SELECT ROWID,EMPNO FROM EMP ROWID EMPNO--AAASZHAAEAAAACXAAA 7369AAASZHAAEAAAACXAAB 7499AAASZHAAEAAAACXAAC 7521AAASZHAAEAAAACXAAD 7566AAASZHAAEAAAACXAAE 7654AAASZHAAEAAAACXAAF 7698AAASZHAAEAAAACXAAG 7782AAASZHAAEAAAACXAAH 7788AAASZHAAEAAAACXAAI 7839AAASZHAAEAAAACXAAJ 7844AAASZHAAEAAAACXAAK 7876ROWID EMPNO-- -AAASZHAAEAAAACXAAL 7900AAASZHAAEAAAACXAAM 7902AAASZHAAEAAAACXAAN 793414 rows selected.

Select a rowid from the above result set without adding hint

SQL > SELECT * FROM EMP WHERE ROWID > = 'AAASZHAAEAAAACXAAA' AND EMPNO IN (7521, 7654) Execution Plan---Plan hash value: 2355 049923 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00:00:01 | | 1 | INLIST ITERATOR | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 | (0) | 00:00:01 | | * 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |- -

Use hint

SQL > SELECT / * + rowid (EMP) * / * FROM EMP WHERE ROWID > = 'AAASZHAAEAAAACXAAA' AND EMPNO IN (7521 Magne7654) Execution Plan---Plan hash value: 2267975152 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0) | 00:00:01 | | * 1 | TABLE ACCESS BY ROWID RANGE | EMP | 1 | 38 | 3 (0) | 00:00:01 |-

/ * + INDEX (TABLE INDEX_NAME) * / scan method for selecting an index for a pair of tables. INDEX_NAME must be capitalized

SQL > select / * + INDEX (emp PK_EMP) * / * from emp 14 rows selected.Execution Plan---Plan hash value: 4170700152 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0) | 00:00:01 | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0) | 00:00:01 |-

/ * + INDEX_ASC (TABLE INDEX_NAME) * / indicates the scanning method for selecting the ascending order of the index on the table. If desc is not specified when indexing, the INDEX_ASC and INDEX hints indicate the same meaning.

SQL > select / * + INDEX_ASC (emp PK_EMP) * / * from emp 14 rows selected.Execution Plan---Plan hash value: 4170700152 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0) | 00:00:01 | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0) | 00:00:01 |-

/ * + INDEX_DESC (TABLE INDEX_NAME) * / indicates the scanning method for selecting the descending index of the table.

SQL > select / * + INDEX_DESC (emp PK_EMP) * / * from emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7566 JONES MANAGER 7839 1981/04 / 02 00:00:00 2975 20 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 2014 rows selected.Execution Plan -Plan hash value: 3088625055-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00: 00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0) | 00:00:01 | | 2 | INDEX FULL SCAN DESCENDING | PK_EMP | 14 | | 1 (0) | 00:00:01 |- -

The above query results are sorted in descending order of empno.

/ * + INDEX_COMBINE (TABLE INDEX1 INDEX2...) * /

SQL > create bitmap index bidx_emp_sal on emp (sal); Index created.SQL > create bitmap index bidx_emp_hiredate on emp (hiredate); Index created.SQL > SELECT * FROM EMP WHERE SAL select * from emp where empno=7840 or ename='SCOTT' Execution Plan---Plan hash value: 2037299637 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0) | 00:00:01 | | 1 | CONCATENATION | | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0) | 00:00:01 | | * 3 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0) | 00:00:01 | | * 4 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 00:00: 01 | | * 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |- -

After adding hint

SQL > select / * + NO_EXPAND*/ * from emp where empno=7840 or ename='SCOTT' Execution Plan---Plan hash value: 3956160932 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | | 2 | 76 | 3 (0) | 00:00:01 | | * 1 | TABLE ACCESS FULL | EMP | 2 | 76 | 3 (0) | 00:00:01 |-- -

/ * + REWRITE (mview) * / rewrite sql with materialized views

/ * + NO_REWRITE*/ rewrites sql without materialized views

/ * + MERGE*/ merges view queries.

Look at the following example:

SQL > SELECT e1.ename, e1.sal, v.avg_sal FROM emp E1, (SELECT deptno, avg (sal) avg_sal FROM emp e2 GROUP BY deptno) v WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal Execution Plan---Plan hash value: 2698 84559 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 29 | 8 (25) | 00:00:01 | | * 1 | HASH JOIN | | 1 | 29 | 8 (25) | 00:00:01 | | 2 | VIEW | | 3 | 48 | 4 (25) | 00:00:01 | 3 | HASH GROUP BY | | 3 | 21 | 4 (25) | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0) | 00:00:01 | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |--

First calculate the results of v, and then do the join operation with E1.

What if you use hint.

SQL > SELECT / * + merge (v) * / e1.ename, e1.sal, v.avg_sal FROM emp E1, (SELECT deptno, avg (sal) avg_sal FROM emp e2 GROUP BY deptno) v WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal Execution Plan---Plan hash value: 2435 006919 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 4 | 128 | 8 (25) | 00:00:01 | | * 1 | FILTER | | 2 | HASH GROUP BY | | 4 | 128 | 8 (25) | 00:00:01 | | * 3 | HASH JOIN | | 65 | 2080 | 7 (15) | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 350 | 3 (0) | 00:00:01 | 5 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0) | 00:00:01 |--

Associate the two tables before group by

/ * NO_MERGE (VIEW) * / is the opposite of the MERGE operation.

Hints for Join Orders

/ * + ORDERED*/ joins tables in turn according to the order of the tables in the FROM clause.

SQL > select * from emp dept d where e.deptno=d.deptno 14 rows selected.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 |-

Although the emp table is written earlier, the optimizer does not process the emp table first.

After adding hint

SQL > select / * + ORDERED*/ * from emp eForce dept d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 1123238657 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 14 | 812 | 7 (15) | 00:00:01 | | * 1 | HASH JOIN | 14 | 812 | 7 (15) | 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 () 0) | 00:00:01 |

Hints for Join Operations

/ * + USE_NL (TABLE1 TABLE2) * / joins using loop nesting and uses the first table specified as the driver table.

SQL > select / * + USE_NL (de) * / * from emp e _ dept d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 4192419542Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | 0 | SELECT STATEMENT | | 14 | 812 | 10 (0) | 00:00:01 | | 1 | NESTED LOOPS | 14 | 812 | 10 (0) | 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | EMP | 4 | 152 | 2 (0) | ) | 00:00:01 |-

/ * + USE_MERGE (table1 table2) * /

SQL > alter session set optimizer_mode=first_rows_1;Session altered.SQL > select a.enamejime b.ename from emp a memorialemp b where a.mgr=b.empno 13 rows selected.Execution Plan---Plan hash value: 3355052392 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0) | 00:00:01 | | 1 | NESTED LOOPS | | 2 | NESTED LOOPS | | 1 | 20 | 3 (0) | 00:00:01 | | * 3 | TABLE ACCESS FULL | EMP | 7 | 70 | 2 (0) | 00:00:01 | | * 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | 0 | 0 | 00:00:01 | 5 | TABLE ACCESS BY INDEX ROWID | EMP | | | 1 | 10 | 1 (0) | 00:00:01 |-SQL > select / * + USE_MERGE (a b) * / a.ename B.ename from emp a,emp b where a.mgr=b.empno 13 rows selected.Execution Plan---Plan hash value: 992080948 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 30 | 6 (17) | 00:00:01 | | 1 | MERGE JOIN | | 1 | 30 | 6 (17) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | | 2 (0) | 00:00:01 | | 3 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0) | 00:00:01 | * 4 | SORT JOIN | 13 | 130 | 4 (25) | 00:00:01 | * 5 | TABLE ACCESS FULL | EMP | 13 | 130 | 3 (0) | 00:00:01 |-

/ * + USE_HASH (table1 table2) * / joins the specified table with other tables through a hash join.

SQL > select / * + USE_HASH (a b) * / a.ename from emp b.ename from emp arecedence emp b where a.mgr=b.empno 13 rows selected.Execution Plan---Plan hash value: 3638257876 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 20 | 7 (15) | 00:00:01 | | * 1 | HASH JOIN | | 1 | 20 | 7 (15) | 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | EMP | 7 | 70 | 3 (0) | 00:00:01 |

/ * + DRIVING_SITE (TABLE) * / this hint is useful when using dblink. Let's look at the following example

SQL > conn / as sysdbaConnected.SQL > grant create database link to scott;Grant succeeded.SQL > conn scott/tigerConnected.SQL > create shared database link "db1" authenticated by SCOTTidentified by "tiger" using '192.168.199.216GDG1'

Make the following query

SQL > select * from emp@db1 dept d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 2705760024 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |- -| 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 | REMOTE | EMP | 14 | 532 | 3 (0) | 00:00:01 | DB1 | R-> S |--

Oracle is to transfer the data of emp on db1 to the local, and then sort and merge. If the amount of data in emp is very large, this is undoubtedly very time-consuming. If we can pass the dept to the remote end, execute it at the remote end, and return the result locally, then the execution speed will be faster.

SQL > select / * + DRIVING_SITE (e) * / * from emp@db1 e _ dept d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 2412741621 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |- | 0 | SELECT STATEMENT REMOTE | | 14 | 812 | 7 (15) | 00:00:01 | | * 1 | HASH JOIN | | 14 | 812 | 7 (15) | 00:00 | : 01 | | 2 | REMOTE | DEPT | 4 | 80 | 3 (0) | 00:00:01 |! | R-> S | 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0) | 00:00:01 | DGTST | |-- -

/ * + LEADING (TABLE) * / uses the specified table as the first table in the join order.

SQL > select / * + LEADING (e) * / * from emp e _ dept d where e.deptno=d.deptno 14 rows selected.Execution Plan---Plan hash value: 1123238657 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 14 | 812 | 7 (15) | 00:00:01 | | * 1 | HASH JOIN | 14 | 812 | 7 (15) | 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 () 0) | 00:00:01 |

/ * + HASH_AJ*/, / * + MERGE_AJ*/, and / * + NL_AJ*/ rewrites not in to anti-connection. AJ = anti-join

SQL > select * from emp where empno not in (select / * + NL_AJ*/ mgr from emp where mgr is not null) 8 rows selected.Execution Plan---Plan hash value: 3509159946Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 8 | 336 | 24 (0) | 00:00:01 | | 1 | NESTED LOOPS ANTI | 8 | 336 | 24 (0) | 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | EMP | 6 | 24 | 2 (0) | 00: 00:01 |

/ * + HASH_SJ*/, / * + MERGE_SJ*/, and / * + NL_SJ*/ rewrites the exists clause to semi-join SJ = semi-join

(one-to-many, as long as there is one record, join is successful)

SQL > select * from dept where exists (select * from emp where deptno=dept.deptno and sal select * from dept where exists (select / * + HASH_SJ*/* from emp where deptno=dept.deptno and sal 1234)

/ * + cache (table_name) * /

Place table on the most active side of the LRU list when performing a full table scan, similar to the cache property of table

Select / * + full (employees) cache (employees) * / last_name from employees

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.