Plsql_ Performance Optimization series 05_oracle hint Tips

2014-06-20 Baoxinjian

I. Summary

Manually specify the execution plan for the SQL statement

Although the Oracle Optimizer is smart, sometimes you want to choose your own execution plan, which can be achieved through hint. In a development test environment, you can test the performance of different execution plans through hint.

The disadvantage of hint is that it increases the additional burden of managing code, which can cause performance degradation when the database or environment changes without modifying hint. For example, the code uses hint to specify the index, but the index name changes when the index is rebuilt.

Therefore, Oracle recommends using hint to test performance with other tools to manage execution plans, such as SQL Tuning Advisor or SQL Plan baseline after Oracle 10g.

But hint is still a very common optimization means, especially some dynamic SQL, the table name is not fixed, you can not use the SQL plan and other tools, you need to hint to do the work.

1. Hints is a mechanism provided by Oracle that tells the optimizer to generate an execution plan in the way we tell it. We can use hints to achieve:

• 1) type of optimizer used
• 2) The optimization goal of the cost-based optimizer is all_rows or first_rows.
• 3) The Access path to the table, whether it is a full table scan, an index scan, or a direct use of ROWID.
• 4) Type of connection between tables
• 5) Order of connections between tables
• 6) The degree of parallelism of the statement

2, hint can be based on the following rules to produce a role

Table join order, table join method, Access path, degree of parallelism

3. Hint Application range

DML statements

Query statements

4. Grammar

{delete| Insert| Select| UPDATE}/*+ hint [text] [Hint[text]] ... */

Or

{delete| Insert| Select| UPDATE}--+ hint [text] [Hint[text]] ...

If the language (sentence) is not correct, Oracle will automatically ignore the written hint, without error

5. Categories

Second, the specific analysis

1./*+all_rows*/

Indicates that the cost-based optimization method is selected for the statement block, and the optimal throughput is obtained, minimizing the resource consumption.

For example:

SELECT/*+all_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

2./*+first_rows*/

This indicates that the cost-based optimization method is selected for the statement block, and the optimal response time is obtained, minimizing the resource consumption.

For example:

SELECT/*+first_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

3./*+choose*/

Indicates that if the data dictionary has access to the table statistics, the cost-based optimization method is used, and the optimal throughput is obtained;

Indicates that if the data dictionary does not have access to the table statistics, the optimization method based on the rule cost is used;

For example:

SELECT/*+choose*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

4./*+rule*/

Indicates that the rule-based optimization method is selected for the statement block.

For example:

SELECT/*+ RULE */emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

5./*+full (TABLE) */

Indicates how to select a global scan for a table.

For example:

SELECT/*+full (a) */Emp_no,emp_nam from Bsempms A WHERE emp_no= ' SCOTT ';

6./*+rowid (TABLE) */

The prompt explicitly indicates that the specified table is accessed according to ROWID.

For example:

SELECT/*+rowid (BSEMPMS) */* from Bsempms WHERE rowid>= ' aaaaaaaaaaaaaa '

and emp_no= ' SCOTT ';

7./*+cluster (TABLE) */

The prompt explicitly indicates the access method to select a cluster scan for the specified table, which is only valid for cluster objects.

For example:

SELECT/*+cluster */Bsempms. Emp_no,dpt_no from Bsempms,bsdptms

　　WHERE dpt_no= ' TEC304 ' and Bsempms. Dpt_no=bsdptms. Dpt_no;

8./*+index (TABLE index_name) */

Indicates the scan method for the table selection index.

For example:

SELECT/*+index (Bsempms sex_index) use Sex_index BECAUSE there is fewmale Bsempms * * from Bsempms WHERE sex= ' M ';

9./*+INDEX_ASC (TABLE index_name) */

Indicates the scan method for the table selection index ascending.

For example:

SELECT/*+index_asc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';

Ten./*+index_combine*/

Selects a bitmap access path for the specified table, and if no index is provided as a parameter in Index_combine, the Boolean combination of bitmap indexes is selected.

For example:

SELECT/*+index_combine (Bsempms sal_bmi hiredate_bmi) */* from BSEMPMS

WHERE sal<5000000 and HireDate

/*+index_join (TABLE index_name) */

Prompts the explicit command optimizer to use the index as the access path.

For example:

SELECT/*+index_join (Bsempms sal_hmi hiredate_bmi) */sal,hiredate

From Bsempms WHERE sal<60000;

/*+index_desc (TABLE index_name) */

Indicates the scan method for descending the table selection index.

For example:

SELECT/*+index_desc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';

/*+index_ffs (TABLE index_name) */

Performs a fast full-index scan of the specified table instead of a full-table scan.

For example:

SELECT/*+index_ffs (Bsempms in_empnam) */* from Bsempms WHERE dpt_no= ' TEC305 ';

/*+add_equal TABLE index_nam1,index_nam2,... */

Prompts for a clear selection of execution plans, combining several single-column index scans.

For example:

SELECT/*+index_ffs (Bsempms in_dptno,in_empno,in_sex) */* from Bsempms WHERE emp_no= ' SCOTT ' and dpt_no= ' TDC306 ';

/*+use_concat*/.

A combined query that converts the or condition after the where in the query to union ALL.

For example:

SELECT/*+use_concat*/* from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';

/*+no_expand*/.

For query statements that are followed by or or in-list, No_expand prevents them from being extended based on the optimizer.

For example:

SELECT/*+no_expand*/* from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';

/*+nowrite*/.

Suppresses query rewrite operations on query blocks.

/*+rewrite*/.

You can use a view as an argument.

/*+merge (TABLE) */

Be able to merge the individual queries of the view accordingly.

For example:

SELECT/*+merge (V) */a.emp_no,a.emp_nam,b.dpt_no from Bsempms A (Selet dpt_no

, AVG (SAL) as avg_sal from Bsempms B GROUP by Dpt_no) V WHERE a.dpt_no=v.dpt_no

and a.sal>v.avg_sal;

/*+no_merge (TABLE) */

Views that have a consolidated view are no longer merged.

For example:

Select/*+no_merge (V) */a.emp_no,a.emp_nam,b.dpt_no from Bsempms A (SELECT dpt_no,avg (SAL) as avg_sal from Bsempms B Grou P by Dpt_no) V WHERE a.dpt_no=v.dpt_no and a.sal>v.avg_sal;

/*+ordered*/.

Based on the order in which the table appears in from, ordered enables Oracle to connect to it in this order.

For example:

SELECT/*+ordered*/a.col1,b.col2,c.col3 from TABLE1 a,table2 b,table3 C WHERE a.col1=b.col1 and b.col1=c.col1;

/*+USE_NL (TABLE) */

Joins the specified table with the row source of the nested connection and takes the specified table as an internal table.

For example:

SELECT/*+ordered use_nl (BSEMPMS) */BSDPTMS. Dpt_no,bsempms. Emp_no,bsempms. Emp_nam from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+use_merge (TABLE) */

Joins the specified table with other row sources through a merge sort connection.

For example:

SELECT/*+use_merge (BSEMPMS,BSDPTMS) */* from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+use_hash (TABLE) */

Connects the specified table to another row source by hashing the connection.

For example:

SELECT/*+use_hash (BSEMPMS,BSDPTMS) */* from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+driving_site (TABLE) */

Enforces query execution on tables that are different from the location selected by Oracle.

For example:

SELECT/*+driving_site (DEPT) */* from Bsempms,[email protected] WHERE Bsempms. Dpt_no=dept. Dpt_no;

/*+leading (TABLE) */

Takes the specified table as the first table in the join order.

/*+cache (TABLE) */

When a full table scan is performed, the cache hint is able to place the retrieved block of the table in the buffer cache most recently used in the least recent list of LRU

For example:

SELECT/*+full (BSEMPMS) Cahe (BSEMPMS) */Emp_nam from Bsempms;

/*+nocache (TABLE) */

When a full table scan is performed, the cache hint is able to place the retrieved block of the table in the buffer cache most recently used in the least recent list of LRU

For example:

SELECT/*+full (BSEMPMS) Nocahe (BSEMPMS) */Emp_nam from Bsempms;

/*+append*/.

You can increase the speed by inserting directly to the end of the table.

Insert/*+append*/to test1 select * from Test4;

/*+noappend*/.

Start a regular insert by stopping the parallel mode during the INSERT statement lifetime.

Insert/*+noappend*/to test1 select * from Test4;

no_index: Specify which indexes are not used

/*+ no_index (table [index [index] ...]) */

Select/*+ no_index (EMP ind_emp_sal ind_emp_deptno) */* from EMP where deptno=200 and sal>300;

Parallel.

Select/*+ Parallel (emp,4) */* from EMP where deptno=200 and sal>300;

Another: Each select/insert/update/delete command can only have a/*+ * *, but the hint content can have multiple, can be separated by commas, spaces can also.

Example:/*+ ordered index () use_nl () */

Abalone New ********************

Plsql_ Performance Optimization series 05_oracle hint tips