Shulou(Shulou.com)05/31 Report--

This article gives you an overview of oracle hint analysis, the content is very detailed, interested friends can use for reference, hope to be helpful to you.

Overview of oracle hint

1. Why Hint was introduced

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.

Hint is a SQL syntax provided by Oracle, which allows users to insert relevant syntax into SQL statements, thus affecting the way SQL is executed.

Because of the special role of Hint, Hint is more like a tool that Oracle provides to DBA to analyze problems for developers who should not use it in their code. Using Hint in SQL code can lead to very serious consequences, because the data in the database is changing, and using this execution plan at one time is optimal, but at another time, it may be very bad, which is one of the reasons why CBO replaces RBO. The rules are dead, and the data is changing all the time. In order to get the most correct execution plan, only know the actual situation of the data in the table. By calculating the cost of various implementation plans, it is the best and the most scientific, which is the working mechanism of CBO. It is dangerous to add Hint, especially performance-related Hint, to your SQL code.

Hint is a SQL syntax provided by Oracle, which allows users to insert relevant syntax into SQL statements, thus affecting the way SQL is executed.

One thing to note when using Hint is that Hint does not work all the time, because if CBO thinks that using Hint will lead to wrong results, Hint will be ignored.

two。 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.

The disadvantages of 3.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.

Relationship between 4.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 characteristics

Application range of 6.HINT

Dml statement

Query statement

7. Grammar

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

Or

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

If the syntax is wrong, ORACLE will automatically ignore the written HINT and do not report an error.

7.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 that explains hint

7.2) errors in prompts

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).

7.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.

8.Hint classification

8.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 make us specify a system parameter value in a statement.

ALL_ROWS: optimize the query statement as a whole and guide the optimizer 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 get 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: use a rule-based optimizer to achieve optimal execution, that is, guide the optimizer to make an execution plan based on the priority rules to determine the order of execution 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).

8.2 related to the access path

FULL: tells 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 clustered index.

HASH: guides the optimizer to read data from the table as a hash scan.

INDEX: tells the optimizer to access the data indexed 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 bitmap indexes. 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: tells the optimizer to access 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: forces 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.

8.3 related to query transformation

USE_CONCAT: decompose the query statement connected by multiple OR or IN operators into multiple single query statements, and select the optimal query path for each single query statement, and then combine these optimal query paths together to achieve the optimization of the overall 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 operational 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 a view or nested view in an optimal way, the base table data used by the view is read directly by transforming the query statement, a process 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: prompts the optimizer to convert subqueries to joins. That is to guide the optimizer to merge the subquery and the main query and convert them to the join type.

NO_UNNEST: the boot optimizer allows the subquery to 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.

8.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, this Hint specifies which table is the driving table, that is, tells 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.

8.5 related to table join operations

USE_NL: 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 as a sort merge join. 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.

8.6 parallelism-related

PARALLEL: 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.

8.7 other related

APPEND: let the database load the data directly (direct load) into the library. 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 through 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 block, and you can use the name of the query block directly in other query 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 cardinality for the whole or part of a query statement, and makes an execution plan for the query statement by referring to that cardinality 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.

= supplement =

1. / * + ALL_ROWS*/

It shows that the optimization method based on cost is selected for the statement block, and the best throughput is obtained to minimize the resource consumption.

For example:

SELECT / * + ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'

2. / * + FIRST_ROWS*/

It shows that the optimization method based on cost is selected for the statement block, and the best response time is obtained to minimize the resource consumption.

For example:

SELECT / * + FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'

3. / * + CHOOSE*/

It shows that if there is statistical information about the access table in the data dictionary, the optimization method based on cost will be used to obtain the best throughput.

It indicates that if there is no statistical information about the access table in the data dictionary, the optimization method based on rule overhead will be used.

For example:

SELECT / * + CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'

4. / * + RULE*/

It shows 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 the method of selecting a global scan for the table.

For example:

SELECT / * + FULL (A) * / EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT'

6. / * + ROWID (TABLE) * /

The prompt clearly 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 clearly indicates that the access method for selecting cluster scanning for the specified table is valid only 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 scanning method for selecting an index on the table.

For example:

SELECT / * + INDEX (BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS * / FROM BSEMPMS WHERE SEX='M'

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

Indicates the scanning method for selecting the ascending order of the index on the table.

For example:

SELECT / * + INDEX_ASC (BSEMPMS PK_BSEMPMS) * / FROM BSEMPMS WHERE DPT_NO='SCOTT'

10. / * + INDEX_COMBINE*/

Select the bitmap access path for the specified table. If the index as a parameter is not provided in INDEX_COMBINE, the Boolean combination of the bitmap index will be selected.

For example:

SELECT / * + INDEX_COMBINE (BSEMPMS SAL_BMI HIREDATE_BMI) * / * FROM BSEMPMS

WHERE SALV.AVG_SAL

21. / * + ORDERED*/

Depending on the order in which the tables appear in FROM, ORDERED causes ORACLE to join them in this order.

For example:

SELECT / * + ORDERED*/ A.Col1 WHERE A.COL1=B.COL1 AND B.COL1=C.COL1 B.Col2 FROM TABLE1 C.Col3 Col2 A Magi TABLE2 B Magna TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1

twenty-two。 / * + USE_NL (TABLE) * /

Joins the specified table with the row source of the nested join, and uses the specified table as the 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

23. / * + USE_MERGE (TABLE) * /

Joins the specified table with other row sources by merging sort connections.

For example:

SELECT / * + USE_MERGE (BSEMPMS,BSDPTMS) * / * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO

24. / * + USE_HASH (TABLE) * /

Joins the specified table with other row sources through a hash join.

For example:

SELECT / * + USE_HASH (BSEMPMS,BSDPTMS) * / * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO

25. / * + DRIVING_SITE (TABLE) * /

Force query execution on tables that are different from the location selected by ORACLE.

For example:

SELECT / * + DRIVING_SITE (DEPT) * / * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO

twenty-six。 / * + LEADING (TABLE) * /

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

twenty-seven。 / * + CACHE (TABLE) * /

When performing a full table scan, CACHE prompts that the retrieval block of the table can be placed in the buffer cache on the most recent user side of the least recent list LRU

For example:

SELECT / * + FULL (BSEMPMS) CAHE (BSEMPMS) * / EMP_NAM FROM BSEMPMS

twenty-eight。 / * + NOCACHE (TABLE) * /

When performing a full table scan, CACHE prompts that the retrieval block of the table can be placed in the buffer cache on the most recent user side of the least recent list LRU

For example:

SELECT / * + FULL (BSEMPMS) NOCAHE (BSEMPMS) * / EMP_NAM FROM BSEMPMS

twenty-nine。 / * + APPEND*/

Insert directly to the end of the table to improve speed.

Insert / * + append*/ into test1 select * from test4

thirty。 / * + NOAPPEND*/

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

Insert / * + noappend*/ into test1 select * from test4

thirty-one。 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

thirty-two。 Parallel

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

Also: there can be only one / * + * / after each SELECT/INSERT/UPDATE/DELETE command, but there can be multiple prompts, separated by commas and spaces.

For example, / * + ordered index () use_nl () * /

-

A sentence similar to the following: insert into xxxx select / * + parallel (a) * / * from xxx a; has about 75 gigabytes of data. The brother, who had not finished running from morning to afternoon, came to me and asked me what was going on. He said that what 2hrs could usually finish running for hours was still messing around. Check the system performance is also relatively normal, cpu,io are not busy, the average READ speed is about 80M/s (barely), but the average write speed is less than 10m. There are a large number of''PX Deq Credit: send blkd','in the waiting event. You can see that there is something wrong with the parallelism here, and you finally know that there is a problem with the parallel usage, and the operation is completed 20 minutes after the modification. The right thing to do should be:

Alter session enable dml parallel

Insert / * + parallel (xxxx,4) * / into xxxx select / * + parallel (a) * / * from xxx a

Because oracle does not open PDML by default, you must enable the DML statement manually. In addition, we have to say that parallelism is not an extensible feature, and it is beneficial to make full use of resources only in data warehouse or as a tool for a small number of people, such as DBA, while parallelism needs to be used very carefully in OLTP environment. In fact, PDML still has many limitations, such as not supporting triggers, reference constraints, advanced replication, distributed transactions and other features, but also brings additional space footprint, the same is true of PDDL. You can refer to the official documentation for Parallel excution, which is also brilliantly described in Thomas Kyte's new book "Expert Oracle Database architecture".

-

Select count (*)

From wid_serv_prod_mon_1100 a

Where a.acct_month = 201010

And a.partition_id = 10

And serv_state not in ('2HB', '2HL', '2HJ', '2HP', '2HF')

And online_flag in (0)

And incr_product_id in (2000020)

And product_id in (2020966, 2020972, 2100297, 2021116)

And billing_mode_id = 1

And exp_date > to_date ('201010 October,' yyyymm')

And not exists (select / * + no_index (b IDX_W_CDR_MON_SERV_ID_1100) * /

one

From wid_cdr_mon_1100 b

Where b.acct_month = 201010

And b.ANA_EVENT_TYPE_4 in

('102010102019,' 102020102018, '10203010201,' 10203010202, '10203030201,' 10203030201, '10204010201,' 10204010202, '10204030201')

And a.serv_id = b.serv_id)

= Common hint supplement =

Hint related to the optimizer pattern

1. 1 / * + all_rows * / Let the optimizer start CBO

1.2 / * + first_rows (n) * / the optimizer starts CBO and selects an execution plan that will quickly return the first n rows of data. Unlike first_rows_n, first_rows_n n can only be 1, 10, 10, 100, 100, 000.

1. 3 / * + rule * / start RBO. When used with other hint, other hint will generally become invalid.

Hint related to table and index access

2.1 full (xxx) full table scan

2.2 index (target table target index 1 target index 2)

2.3 no_index (target table target index 1 target index 2)

2.4 index_desc (target table target index 1, target index 2) lets the optimizer perform a descending scan on the target index. If the target index is in descending order, hint scans the target index in ascending order.

2.5 index_ffs (target table target index 1 target index 2) index fast full scan

Hint related to table join order

Ordered lets the optimizer join multiple tables in the order in which their where conditions appear in sql. Query transformation may invalidate this hint.

3.2 leading (Table 1, Table 2) asks the optimizer to take the join result of our execution of multiple tables as the result set during the join of the target sql table, and the first table from left to right in hint as the table join driver.

Hint related to the table join method

Use_merge (Table 1, Table 2) allows the optimizer to sort and join multiple tables we specify as driven tables with other tables or result sets.

4.2 no_use_merge (Table 1, Table 2)

4.3 use_nl (Table 1, Table 2) lets the optimizer join multiple tables we specify as driven tables with other tables or result sets in a nested loop. Often used with leading.

/ * + use_nl (sQuery X) leading X * /

4.4 no_use_merge (Table 1, Table 2)

4. 5 use_hash (Table 1, Table 2) lets the optimizer hash multiple tables we specify as driven tables with other tables or result sets. Often used with leading.

4.6 no_use_merge (Table 1, Table 2)

4. 7 merge_aj targets the hint of the subquery and lets the optimizer sort and disjoin the target table.

Nl_aj directs the hint of the subquery to allow the optimizer to perform nested loop disjoins on the target table.

4.9 hash_aj directs the hint of the subquery to let the optimizer perform hash disjoins on the target table.

4.10 merge_sj targets the hint of the subquery and lets the optimizer perform a sort merge semi-join on the target table.

4.11 nl_sj targets the hint of the subquery and lets the optimizer perform a nested loop semi-join on the target table.

4.12 hash_sj targets the hint of the subquery and lets the optimizer perform a hash semi-join on the target table

Hint related to query transformation

Use_concat is the hint for the target sql, allowing the optimizer to use in_list or or_list extensions for the target sql.

5.2 no_expand is the use_concat antisense hint, which prevents the optimizer from using in_list or or_list extensions for the target sql.

5.3 merge is a hint for a single target view, allowing the optimizer to perform view merging (view merging) on the target view

No_merge is an antisense hint for merge, which prevents the optimizer from performing view merging (view merging) on the target view

5.5 unnest is the hint for subqueries, which allows the optimizer to subquery unnesting the subqueries in the target sql

5.6 no_unnest is an antisense hint for unnest, which prevents the optimizer from expanding (subquery unnesting) subqueries in the target sql.

5.7 expand_table (table) allows the optimizer to extend the table without considering the cost

5.8The antisense hint of no_expand_table (table) expand_table (table) does not extend the table.

Hint related to parallelism

6.1 parallel parallelism

6.2 no_parallel hint for the entire target sql

6.3 parallel_index (Table index-1 index-2. Index-n n n n)

6.4 no_parallel_index (Table index-1 index-2. Index-n)

Other common hint

Driving_site lets the optimizer execute the target sql on the node where we specified the target table. Applies only to distributed query statements with dblink.

Append lets the optimizer bypass the buffer cache and use direct path insertion when executing the insert of the tape query.

7.3.When append_values lets the optimizer execute insert with values, bypass buffer cache and use direct path insertion. (11R2)

7.4 push_pred lets the optimizer perform join predicate push on the target view for the hint of the target view.

No_push_pred prevents the optimizer from performing join predicate push on the target view for the hint of the target view.

7.6 push_subq aims at the hint of the subquery to let the optimizer execute the subquery in the target sql that cannot be expanded by the subquery as soon as possible.

7.7 no_push_subq targets the hint of the subquery so that the optimizer finally executes the subquery that cannot be expanded by the subquery in the target sql.

Opt_param for the hint of the target sql is used to modify changes to the finer particles of the target sql than at the system level and session level.

7.9 optimizer_features_enable ('optimizer version number') to change the optimizer version

7.10 qb_name specifies a custom name for a query block.

7.11 cardinality is used to set the value of cardinality after scanning the target table for the hint of a single target table. (invalid for unique index scan)

7.12 swap_join_inputs allows the optimizer to exchange the order of the driver table and driven table of the original hash connection for the hint of the hash connection.

This is the end of the summary and analysis of oracle hint. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.