Optimize the SQL statement by analyzing the SQL statement execution plan

How to intervene in the execution plan--Tips for using hints

The cost-based optimizer is very intelligent. In most cases, it selects the correct optimizer, reducing the DBA burden. However, sometimes it is too clever to be confused. It chooses a poor execution plan, making the execution of a statement extremely slow. In this case, the DBA needs to perform manual intervention to tell the optimizer to use the specified access path or connection type to generate an execution plan, so that the statement can run efficiently. For example, if we think that full table scan is more effective for a specific statement than index scan, we can instruct the optimizer to use full table scan. In Oracle, hints (prompt) is added to the statement to optimize the intervention optimizer.

Hints is a mechanism provided by oracle to tell the optimizer to generate an execution plan as we tell it. We can use hints to implement:

1. Type of optimizer used

2. The optimization goal of the cost-based optimizer is all_rows or first_rows.

3. The access path of a table is full table scan, index scan, or rowid.

4. connection types between tables

5. Connection sequence between tables

6. Parallel statement degree

In addition to the "RULE" prompt, once other prompts are used, the statement will be automatically changed to the CBO optimizer. If your data dictionary does not contain statistics, the default statistical data is used. Therefore, it is recommended that you perform regular analysis on tables and indexes if you use the CBO or HINTS prompt.

How to Use hints:

Hints only applies to the SQL statement block (statement block, identified by the select, update, and delete keywords) where they are located. Hints does not affect other SQL statements or statements. For example, if two SQL statements that use the union operation have hints on only one SQL statement, the hints will not affect the other SQL statement.

We can use comment to add hints to a statement. A statement block can only have one comment, and the comment can only be placed behind the SELECT, UPDATE, or DELETE keyword.

Use hints Syntax:

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

Note:

1. DELETE, INSERT, SELECT, and UPDATE are keywords that identify the beginning of a statement block. Comments containing prompts can only appear behind these keywords; otherwise, the prompt is invalid.

2. "+" indicates that the comment is a hints, And the plus sign must be immediately followed by "/*" without spaces in the middle.

3. hint is one of the specific prompts described below. If there are multiple prompts, one or more spaces are required to separate each prompt.

4. text is other explanatory text about hint.

If you do not specify hints correctly, Oracle will ignore this hints and will not give any errors.