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

1. Overview of Hint. 1. Why introduce Hint?

Hint is a unique function in Oracle database, and it is a means often used in many DBA optimization. So why would Oracle consider introducing an optimizer? The cost-based optimizer is smart, and in most cases it will choose the right optimizer to reduce 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 specified access path or connection type, so that the statement runs efficiently. Hint is a mechanism provided by Oracle to tell the optimizer to generate an execution plan the way it is told.

2. Don't rely too much on Hint

When the SQL execution plan is not good, priority should be given to issues such as statistics, rather than directly adding Hint. If the statistics are correct, you should consider whether the physical structure is reasonable, that is, there is no appropriate index. Consider SQL only if it still fails to execute according to the optimized execution plan in the end.

After all, using Hint requires the application system to modify the code, Hint can only solve the problem of one SQL, and due to changes in data distribution or other reasons (such as index renaming), SQL performance problems will occur again.

3. The disadvantages of Hint.

Hint is a more "violent" solution, not very elegant. Developers are required to modify the code manually.

Hint will not adapt to new changes. For example, significant changes have taken place in data structure and data size, but statements that use Hint are aware of change and produce a better execution plan.

Hint may vary or even become obsolete depending on the version of the database. At this point, the statement itself is imperceptible and must be tested and corrected manually. 4. The relationship between Hint and annotations

Hints are an extension provided by Oracle to avoid breaking compatibility with other database engines for SQL statements. Oracle decided to add the prompt as a special comment. Its particularity is that the prompt must be followed by the DELETE, INSERT, UPDATE, or MERGE keywords.

In other words, prompts cannot be added everywhere in a SQL statement like normal comments. And the first character after the comment delimiter must be a plus sign. It will be explained in detail in the later usage section.

5. Hint function

Hint provides a wealth of features that allow you to adjust the execution of statements flexibly. With Hint, we can adjust:

Optimizer type

Optimization objective of optimizer

Data reading method (access path)

Query conversion type

Order of associations between tables

Types of associations between tables

Parallel characteristic

Other features II, Hint usage 1, syntax

1) keyword description

DELETE, INSERT, SELECT, and UPDATE are keywords that identify the beginning of a block of statements, and comments containing prompts can only appear after these keywords, otherwise the prompt is invalid.

The "+" sign indicates that the comment is a hint, and the plus sign must be immediately followed by "/ *" with no spaces in the middle.

Hint is one of the specific tips described below, and if you include multiple prompts, each prompt needs to be separated by one or more spaces.

Text is other annotative text describing hint 2) error in prompt

The syntax error in the prompt will not report an error, and if the parser cannot parse it, it will be treated as a normal comment. This is also easy to cause confusion, whether the use of Hint works in the end? There are some measures that can be used to check the validity of the prompt. It is important to note that prompts that are grammatically correct but reference object errors are not reported.

Explain plan + dbms_xplan

Use the note option in the dbms_xplan output.

10132 event

In 10g, there is a special prompt at the end of the output document generated by this event. Through it, you can check two aspects: one is that each prompt used will be listed. If you miss which one, the prompt is not recognized; second, check to see if there is some information indicating that there is a prompt error (if something goes wrong, the ER value will be greater than 0).

3) objects in the prompt

SELECT / + INDEX (table_name index_name) /...

Table_name is required, and if a table alias is used in the query, the table alias is also used in hint instead of the table name.

Index_name may not have to be written, and Oracle will select an index based on the statistical value.

If the index or table name is misspelled, the hint will be ignored.

If the specified object is a view, you need to specify it this way. / * + hint view.table... * /, where table is the table in view.

A common mistake when using prompts is that the most common mistake is related to the alias of the table. The correct rule is that when using a table in a prompt, you should use an alias instead of a table name as long as the table has an alias.

2. The scope of the prompt query block

Initialization parameter hints work on the entire SQL statement, while other hints work only on query blocks. Hints that work only for a single query block must be specified within the query block it controls.

Exception-Global prompt

You can use periods to refer to objects contained in other query blocks (assuming they are named). The syntax of global prompts can support more than two layers of references, and objects must be separated by periods.

Named query block

Since subqueries in the where clause are unnamed, their objects cannot be referenced by global prompts. To solve this problem, another approach is used in 10g-naming query blocks. The query optimizer can generate a query block name for each query, and you can also manually name each query block using the prompt qb_name. Most prompts can be specified by parameters to be valid in that query block.

* use @ to reference a query block in the prompt.

3. Prompt data dictionary

Oracle provides a data dictionary, V$SQL_HINT, in the 11g version. From this data dictionary, you can see the occurrence version of the prompt, the summary data version, the SQL feature, and vice versa.

INVERSE

The hint of the opposite operation of this hint.

VERSION

It represents the version that was officially announced and introduced by hint.

Third, Hint classification 1, related to the optimizer

When you are not satisfied with the basic execution plan made by the optimizer for a statement, the best way is to convert the pattern of the optimizer through prompts, and observe the results after conversion to see if it has reached the desired level. If you can get a very good execution plan simply by converting the pattern of the optimizer, there is no need to use more complex hints.

OPT_PARAM

The purpose of this prompt is to enable us to specify a system parameter value in a statement.

ALL_ROWS

In order to optimize the query statement as a whole, the optimizer is guided to make an execution plan with the lowest cost. This prompt causes the optimizer to choose the path that retrieves all query rows as quickly as possible, at the expense of being slow to retrieve a row of data.

FIRST_ROWS

Guide the optimizer to develop an execution plan with the lowest cost in order to obtain the best response time. This prompt causes the optimizer to choose the path that can retrieve the first row (or specified row) of the query as quickly as possible, at the expense of slow retrieval of many rows. The number of rows optimized with FIRST_ROWS, the default value is 1, which is between 10 and 1000, and this new method using FIRST_ROWS (n) is entirely cost-based. It is sensitive to n, and if n is small, CBO generates a plan that includes nested loops and index lookups; if n is large, CBO generates a plan consisting of hash joins and full table scans (similar to ALL_ROWS).

CHOOSE

Decide whether to use RBO or CBO based on the presence or absence of statistics for the tables used in SQL. In CHOOSE mode, if you can refer to the statistics of the table, it will be executed in ALL_ROWS mode. Unless all tables in the query are not parsed, the choose hint uses cost-based optimization for the entire query. If one table in a multi-table join is analyzed, the entire query is optimized based on cost.

RULE

The rule-based optimizer is used to achieve optimal execution, that is, the optimizer is guided to make an execution plan according to the priority rules to determine the execution order of the indexes or operators used in the query conditions. This prompt forces oracle to give priority to a predefined set of rules rather than statistics; it also prevents the statement from using other hints except DRIVING_SITE and ORDERED (both of which can be used regardless of rule-based optimization).

2. FULL related to the access path

Tell the optimizer to access the data through a full table scan. This prompt only performs a full table scan of the specified table, not all tables in the query. FULL hints can improve performance. This is mainly because it changes the driven table in the query, not because of a full table scan. When using some other prompts, you must also use FULL prompts. The table can be cached using the CACHE prompt only if the entire table is accessed. Some prompts in parallel groups must also use full table scans.

CLUSTER

The boot optimizer reads data from the index table by scanning the clustering index.

HASH

Guides the optimizer to read data from the table in the way of hash scanning.

INDEX

Tells the optimizer to access data through an index on the specified table. The optimizer ignores this Hint when accessing the data results in an incomplete result set.

NO_INDEX

Tells the optimizer that indexes are not allowed on the specified table. This prompt forbids the optimizer from using the specified index. Indexes can be disabled in many queries before unnecessary indexes are deleted. If NO_INDEX is used, but no indexes are specified, a full table scan is performed. If both NO_INDEX and meeting conflicting prompts (such as INDEX) are used on an index, both prompts will be ignored.

INDEX_ASC

When reading data from a table using an index, guides the optimizer to scan the index column values of the index specified in the prompt in ascending order.

INDEX_COMBINE

Tells the optimizer to force the selection of a bitmap index. This prompt causes the optimizer to merge multiple bitmap indexes on the table instead of selecting the best one (this is the purpose of the INDEX hint). You can also use index_combine to specify a single index (this prompt takes precedence over the INDEX prompt for pointing to the positioning map index). For B-tree indexes, you can use the AND_EQUAL hint instead of this one.

INDEX_JOIN

Index association, when there is an index on all the columns referenced in the predicate, the data can be accessed by index association. This prompt allows you to merge different indexes of the same table so that you only need to access those indexes, saving time to query back to the table. However, this hint can only be used in a cost-based optimizer. Not only does this prompt allow you to access only the indexes on the table so that fewer blocks of code can be scanned, but it is five times faster than using the index and scanning the entire table through rowid.

INDEX_DESC

When reading data from a table using an index, guides the optimizer to scan the index column values of the index specified in the prompt in descending order.

INDEX_FFS

Tell the optimizer to access the data in an INDEX FFS (index fast full scan) way. The INDEX_FFS prompt performs a quick global scan of the index. This prompt accesses only the index, not the corresponding table. This prompt is used only if the information that the query needs to retrieve is on the index. Using this prompt can greatly improve performance, especially when the table has many columns.

INDEX_SS

Force access to the index using index skip scan. When in a federated index, some predicate conditions are not in the first column of the federated index (or when the predicate is not in the first column of the federated index), the index can be accessed through index skip scan to obtain data. When the unique value of the first column of the federated index is very small, this method is more efficient than the full table scan.

3. USE_CONCAT related to query transformation

The query statement connected by multiple OR or IN operators is decomposed into multiple single query statements, and the optimal query path is selected for each single query statement, and then these optimized query paths are combined to achieve the optimization of the whole query statement. This prompt can be used only if the OR is included in the driven query condition.

NO_EXPAND

Guide the optimizer not to make a combined execution plan for conditions that use OR operation symbols (or IN operators). Just the opposite of USE_CONCAT.

REWRITE

When the object connected by the table is a table with a large amount of data or needs to obtain the results processed by statistical functions, the materialized view can be created in advance in order to improve the execution speed. When a user requests to query a query statement, the optimizer chooses a more efficient way to read data from a table or from a materialized view. This execution method is called query rewriting. Use the REWRITE prompt to guide the optimizer to do so.

MERGE

In order to read data from views or nested views in an optimal way, the base table data used by views is read directly by transforming query statements, which is called view merging. The specific types of use are also different in different situations. This prompt is mainly used when the view is not merged. Using this hint can sometimes work very well, especially for more complex views or nested views (such as views that use GROUP BY or DISTINC).

UNNEST

Prompt the optimizer to convert the subquery to a join. That is to guide the optimizer to merge the subquery and the main query and convert them to the join type.

NO_UNNEST

Boot the optimizer so that the subquery can be executed independently and then FILTER with the peripheral query.

PUSH_PRED

Use this prompt to push query conditions other than views or nested views into the view.

NO_PUSH_PRED

Use this prompt to ensure that query conditions other than views or nested views are not pushed into the view.

PUSH_SUBQ

Use this prompt to guide the optimizer to develop an execution plan for subqueries that cannot be merged. After a subquery that cannot be merged is executed first, the execution result of the subquery will play the role of a provider that reduces the scope of the main query data query. Usually when the subquery merge cannot be performed, the subquery plays the role of inspector, so the subquery is generally executed at the end. In cases where a subquery that cannot be merged has fewer result rows, or where the subquery can reduce the scope of the main query, you can use this hint to guide the optimizer to maximize the execution of the subquery in front of it to improve execution speed. However, this prompt will have no effect if the subquery executes a remote table or sorts part of the join results of the merge join.

NO_PUSH_SUBQ

Use this prompt to guide the optimizer to execute subqueries that cannot be merged at the end. In the case that the subquery can not reduce the query scope of the main query, or the execution of the subquery is expensive, putting such a subquery at the end of the execution can improve the overall execution efficiency to some extent. That is, the subquery is executed after minimizing the scope of the query using as many other query conditions as possible.

4. Related to the table join order

These tips can adjust the order of table joins. You can not only use these hints to adjust the order of table joins, but you can also use prompts to guide the optimizer to use indexes created by driving query conditions in a nested loop join. However, this method is effective only if the index and table join order used are adjusted at the same time. In general, these hints are mainly used when performing multi-table joins and the order of joins between tables is chaotic, and when sorting merge joins or hash joins are used to guide the optimizer to give priority to the execution of tables with less data.

LEADING

In a multi-table associated query, the Hint specifies which table is the driving table, telling the optimizer to access the data on that table first. Guides the optimizer to use the table specified by LEADING as the first table in the table join order. This prompt is independent of the order of the tables described in FROM, and is different from the ORDERED prompt, which adjusts the join order of tables, and there is no need to adjust the order of tables described in FROM when using this prompt. The prompt is ignored when used in conjunction with the ORDERED prompt.

This prompt is similar to the ORDERED hint, which allows you to specify the table that drives the query, and then it is up to the optimizer to determine which table to access next. If you use this prompt to specify multiple tables, you can ignore this prompt.

ORDERED

Guides the optimizer to perform joins in the order of the tables described in FROM. If used with LEADING prompts, LEADING prompts are ignored. Because ORDERED can only adjust the order of table joins and cannot change the way tables are joined, USE_NL and USE_MERGE hints are often used together with ORDERED hints in order to change the way tables are joined.

5. USE_NL related to table join operation

Use this prompt to guide the optimizer to perform a table join in a nested loop join. It simply indicates how the table is joined, and has no effect on the order of the table join.

USE_MERGE

Guides the optimizer to perform the connection according to the sort merge connection. If necessary, it is recommended that you use this prompt with the ORDERED prompt. Hints are often used to get the best throughput for a query. Assuming that two tables are joined together, the rowset returned from each table will be sorted and then merged (that is, merged sorting) to form the final result set. Because each row is sorted before it is merged, it is fastest to retrieve all rows in a given query. If you need to return the first row as quickly as possible, you should use the USE_NL prompt.

USE_HASH

This prompt guides the optimizer to perform the connection as a hash connection. When performing a hash join, a very good execution speed can be achieved if the hash join can be implemented in memory because the table on one side is relatively small. Since in most cases the optimizer determines Build Input and Prove Input by analyzing statistics, it is recommended that you do not use ORDERED hints to change the join order of the tables at will. However, this prompt can be used when the optimizer fails to make a correct judgment, or when statistics are not available as the result set obtained from the nested view.

6. PARALLEL related to parallelism

Specifies the degree of parallelism that SQL executes, which will override the parallelism set by the table itself. Use the system parameter if this value is default,CBO. Use this prompt to specify parallel operations for SQL when reading large amounts of data from a table and performing DML operations.

In general, you need to specify the number of parallel threads to be used in this prompt. If the number of parallelism is not specified in the prompt, the optimizer automatically calculates using the value specified by the PARALLEL_THREADS_PER_CPU parameter. If PARALLEL is specified when defining the table, the optimizer selects parallel operations at the specified level of parallelism, even if the prompt is not used, if parallel operations can be used.

However, if you want to use parallel operations in DML operations such as DELETE, INSERT, UPDATE, MERGE, and so on, you must set ALTER SESSION ENABLE PARALLEL DML in the session. The level of parallelism set in a session can also be referenced in an internal GROUP BY or sort operation. If a constraint appears in a parallel operation, the prompt is ignored.

NOPARALLEL/NO_PARALLEL

Parallelism is prohibited in SQL statements. In some versions, NO_PARALLEL hints are used instead of NOPARALLEL hints.

PQ_DISTRIBUTE

To improve the execution speed of parallel connections, use this prompt to define how to allocate the data rows of each join table between master and slave processes, such as producer and consumer processes.

PARALLEL_INDEX

Use this prompt to scan the index range of a partitioned index in a parallel manner, and you can specify the number of processes.

7. Other related APPEND

Let the database load the data into the library in a direct load way (direct load). This prompt does not check whether there is currently the required block space for insertion, but instead adds the data directly to the new block. This wastes space, but improves the performance of the insert. Note that the data will be stored in the location on the HWM.

APPEND_VALUES

In 11.2, Oracle added APPEND_VALUES hints so that INSERT INTO VALUES statements can also be inserted using direct paths.

CACHE

After a full table scan, the data blocks remain on the most active side of the LRU list. If you set the CACHE property of a table, it works the same as HINT. This prompt caches all table scans in memory. If the table is large, it takes up a lot of memory. Therefore, it is suitable for smaller tables that users often access.

NOCACHE

The bootstrap optimizer caches the blocks obtained by a full table scan at the end of the LRU list, so that these blocks in the database instance cache are cleared first. This is the default way for the optimizer to manage blocks in Buffer Cache (for full table scans only).

QB_NAME

Use this prompt to name the query statement block, and the name of the query statement block can be used directly in other query statement blocks.

DRIVING_SITE

This tip is useful in distributed database operations. Specifies that the table is where the connection is processed. You can limit the amount of information processed over the network. In addition, you can establish a local view of the remote table to restrict the rows retrieved from the remote site. The local view should have a where clause so that the view can restrict the rows returned from the remote database before sending them back to the local database.

DYNAMIC_SAMPLING

Prompts the level of dynamic sampling when SQL executes. This level is 0: 10, which overrides the system's default dynamic sampling level. The higher the level, the higher the accuracy of the statistical information obtained. The function of this prompt is to ensure that the dynamic sampling principle is applied to a single SQL.

AND_EQUAL

This prompt causes the optimizer to merge multiple indexes on the table instead of selecting the best of them (this is the purpose of the INDEX hint). This prompt differs from the previous INDEX_JOIN prompt, where the specified merged index then needs to access the table, while the INDEX_JOIN prompt only needs to access the index. If you find that you need to use this prompt frequently, you may need to delete these individual indexes and use a combined index instead. You need to query all the index columns in the query condition, and then get the rowid list from each index. Then merge join these objects, filter out the same rowid, and then get the data from the table or directly from the index. In 10g, and_equal is obsolete and can only take effect through hint.

CARDINALITY

Provides the optimizer with a predicted base value for the whole or part of a query statement, and makes an execution plan for the query statement by referring to that base value. If the name of the table is not specified in the prompt, the base value is considered as the final number of resulting rows obtained from the query statement.

IV. Examples of using Hint

Here is an example to illustrate the use of hints and the circumstances in which they will be ignored.

1. Build the table

2. Use the INDEX prompt

* in some cases, if CBO believes that Hint will cause incorrect results, then Hint will ignore it. In this example, because the ID field may be empty and the index holds null values, count (*) using the index will lead to incorrect results, so a full table scan is used and Hint is ignored.

3. Use INDEX prompt (non-empty field)

* the ID field cannot be empty, so COUNT can be processed by index scanning, and Hint takes effect.

Author: Han Feng

Source: trust Institute of Technology (http://college.creditease.cn/)

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.