Optimizing Sql_mssql by analyzing execution plans for SQL statements

How to intervene in the execution plan--Using hints hints

The cost-based optimizer is smart, and in most cases it chooses the right optimizer to ease the burden on the DBA. But sometimes it is clever to be mistaken, choose a bad execution plan, make the execution of a statement become extremely slow. At this point, the DBA is required to make an artificial intervention, telling the optimizer to use our specified access path or connection type to generate the execution plan so that the statement runs efficiently. For example, if we think that a full table scan is more efficient than performing an index scan for a particular statement, then we can instruct the optimizer to use a full table scan. In Oracle, the purpose of the Intervention Optimizer optimization is to add hints (hint) to the statement.

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

1. Types of optimizer used

2. Cost-based optimizer optimization objective is all_rows or first_rows.

3. Table access path, whether full table scan, or index scan, or direct use of ROWID.

4. Type of connection between tables

5. Sequence of joins between tables

6. The degree of parallelism of the statement

In addition to the rule hint, once the other prompts are used, the statement automatically changes to the CBO optimizer, and the default statistics are used if there is no statistical data in your data dictionary. So if you use CBO or hints hints, it's a good idea to analyze your tables and indexes regularly.

How to use hints:

Hints are only applied to their SQL statement blocks (statement block, identified by the Select, Update, and delete keywords) and have no effect on other SQL statements or other parts of the statement. For example, if you have hints on only one SQL statement for 2 SQL statements that use the Union operation, the hints does not affect another SQL statement.

We can use annotations (comment) to add hints for a statement, one statement block can have only one comment, and annotations can only be placed after the Select, UPDATE, or delete keyword

Syntax for using hints:

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

Annotations:

1.DELETE, INSERT, select, and update are keywords that identify the beginning of a statement block, and comments that contain hints can appear only after those keywords, otherwise the prompts are invalid.

2. The "+" number means that the annotation is a hints, the plus must immediately follow the "/*", with no spaces in the middle.

3.hint is one of the specific tips described below, and if you include multiple prompts, you need to separate each hint with one or more spaces.

4.text is an annotated version of the other description hint

If you do not specify the correct hints,oracle the hints is ignored and no error is given.