Detailed description of Oracle Database Implementation Plan

The knowledge about Oracle database implementation plans is what we will introduce in this article. We first introduce the concept of implementation plans, and then give examples of two implementation plans, finally, we introduced the form of the Oracle optimizer and the purpose of the plan. Let's take a look at this part.
What is a strong plan?
The so-called implementation of the plan, we hope that, even for a query task, make a detailed plan on how to end the task. For example, if I want to go to the UK from Zhuhai, I can choose to go to Hong Kong and then improve, or Beijing or Guangzhou. However, how to make a good deal in the UK, even if I spend at least, is a cost-effective thing. Similarly, for queries, the SQL statements we submit only describe our target location in the UK, but as for how to proceed, there is no reminder message in our SQL statements, it is determined by the database.
First, we can easily look at the comparison of a strong plan: SQL> set autotrace traceonly
Plan 1:
The following is a code snippet:
SQL> select count (*) from t; COUNT (*)
----------
24815 Execution Plan 0 select statement Optimizer = CHOOSE 10 SORT (AGGREGATE)
21 TABLE Access (FULL) OF 'T' Plan 2:
The following is a code snippet:
SQL> select count (*) from t; COUNT (*)
24815 Execution Plan 0 select statement Optimizer = CHOOSE (Cost = 26 Card = 1)
10 SORT (AGGREGATE)
21 INDEX (full scan) OF 't_ Index' (NON-UNIQUE) (Cost = 26 Card = 28180)
In these two strong plans, the first one indicates that the sum is done through a full table scan, and the full table data is read into the memory to accumulate one by one; the second one indicates that the full table data is indexed according to the table, read the full index into the memory to accumulate data one by one without having to read the data in the table. But which of the two measures is faster? It may be faster than the other, but it is not absolute. This is an easy example to demonstrate the difference in implementing a plan. For mixed SQL statements (table join, nested subqueries, etc.), there may be dozens or even hundreds of plans, but what is better? We did not know before, and the database itself did not know. However, the database will choose a strict plan based on the legal possibility of the Census message (statistics), so we can easily choose the best choice, but there is also a time to make a decision, even if the value of this talk lies.
Oracle optimizer format
There are two types of Oracle optimizer: statutory and cost-based. In SQLPLUS, we can view the default optimizer format defined in the init file.
The following is a code snippet:
SQL> show parameters optimizer_mode name typevalue optimizer_mode string choose SQL>
This is Oracle8.1.7 Enterprise Edition. We can see that after the default installation, the database optimizer format is CHOOSE, and we can also set it to RULE, FIRST_ROWS, and ALL_ROWS. you can set all sessions of the full instance in the init file, or you can set a session independently:
The following is a code snippet:
SQL> ALTER SESSION SET optimizer_mode = RULE; the SESSION has been changed.
SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS; the SESSION has been changed.
SQL> ALTER SESSION SET optimizer_mode = ALL_ROWS; the SESSION has been changed.
Based on statutory queries, databases define messages based on tables, indexes, and so on, and implement strict plans based on specific laws. Cost-based queries, the database collects statistics based on the collected table and index data (the data may be collected using the dbms_stats package through analyze) sum up to determine the best practice plan for getting a database (no need to decide the best ). RULE is based on laws. CHOOSE indicates that if the queried table contains the collected census message, it is based on the price (Oracle approves FIRST_ROWS in the form of CHOOSE); otherwise, it is based on laws. Among the two cost-based measures, FIRST_ROWS indicates that it plans to approve at least the return of resources to the client as soon as possible. It is particularly useful for sorting pages to reveal such queries, ALL_ROWS means to return the result to the client after taking the minimum amount of resources for the general administration.
Based on the legal form, the database's strict plan is relatively peaceful. However, in a price-based manner, we have a larger opportunity to choose the best plan. Because of Oracle's many query characteristics, it was revealed in a price-based manner, therefore, we have no choice about RULE (and Oracle claims that it will no longer support RULE from the Oracle 10i database ). Since it is a price-based model, even if we say that the choice of a strong plan is determined based on definitions such as tables and indexes and Census messages of data, this census message is collected on a regular basis based on analyze's call for the dbms_stats package. There is a possibility of taking the lead, even if the collection of messages is a resource-consuming and time-consuming action, especially when the table data volume is large, because the collection of messages is a re-comprehensive census of the full table data, this is the question of our determination. We can only regularly collect messages when the server is comfortable. This explains that during a period of time, the census message may not match the data of the database itself. In addition, even the Oracle census data itself may be inaccurate (see the Oracle DOCUMENT for details ), A more important issue is that even if the data in a timely manner is relatively accurate, the Oracle optimizer's choice is not always the best plan. This is also a reference to Oracle's statutory computing laws for the cost of different plans (we cannot know the detailed computing laws at our leisure ). It seems that we have decided on how to calculate the actual prices from Hong Kong or from Beijing to the UK, tickets, air tickets, etc. We do not know, maybe we are currently inquiring about the price news, when we took the bus, our earnest price and our accounting had changed. All factors will affect our full expenditures.
What does the Anning function bring to us?
Oracle has the possibility of failing to make a strong plan. This is also some of the illusion we often encounter. For example, some people say that my process runs well in the test database, but even in the product database, even the latter has better hardware conditions than the former. Why? Hardware resources, Census messages, and parameter settings may affect the implementation plan. Due to too many factors, we always have an inexplicable fear of the future. Is my product database running after going online? Therefore, Oracle provides a kind of strength to carry out a plan, even if OUTLINES that run well in the test environment are transplanted to the product database, so that the plan will not change with other factors.
So what is OUTLINES? First, we should recommend a piece of content. Oracle provides the strength to use HINTS in SQL to lead the optimizer to implement the implementation plan we want. This is particularly useful in Multi-table join and hybrid queries. There are many HINTS types. They can set the optimizer targets (RULE, CHOOSE, FIRST_ROWS, and ALL_ROWS), specify the order of table connections, and specify the index of the table to be used, it can hold a lot of fine-grained control over SQL. Through this kind of measure we want to implement these HINTS, Oracle can store these HINTS, which we call OUTLINES. through the store outlines can make us have the same strength to carry out the plan, even if we have the peace of mind to carry out the strength of the plan.
Here I would like to give a supplementary explanation. Even if, in reality, we use tools to modify SQL, such as the SQL statement after SQL EXPERT is modified, these are not only SQL statements with HINTS added, but also those with modified texts. They can also store OUTLINES and be exploited. However, this is not a definite effect. Let's test whether it works. However, even with OUTLINES, when the database is doing its best, the database will not return because it has neglected to generate a new plan, so we dare to make the best use of it. Of course, this is not specified in the Oracle document. The document only explains that if HINTS are added to SQL when OUTLINES exist, HINTS will be neglected by OUTLINES. this function will be used in the products that LECCO will announce, so that it can combine the strength of SQL expert's SQL modification and the strength of Anning and strict planning, so we have a considerable strength in SQL optimization for the use of the source code that cannot be changed.
Maybe we have doubts. If we have a strong plan, what else should we collect the census news? This is because of several factors that have taken the lead. The current plan may not be light-weight for data that has changed in the future. There is a plan that does not like the efficiency of future data changes, the implementation plan of the new census message is not fully scientific. At that time, we were able to approve the new census news, but we were able to approve the poor strong plan under the new census news to approve the strong plan of Oracle supply, the peace of mind, the strength of the Anning plan, in this way, we can build a comfortable and efficient database runtime environment.
We also need to care about one thing, dbms_stats package provided by Oracle unless it has the strength to collect census messages, but also has the strength to query messages (statistics) export/import in the database, it also has the strength to collect only the census message, which makes the census message unfavorable for the database (collect the census message to a specific table instead of taking effect immediately ), on this basis, we can import the census message export to a testing environment, and then run our usage, in the test environment, we will examine which plans will change due to the latest census News (the Plan Version Tracer of db expert is a tool that imitates different environments and actively observes the changes in plans in different environments), is it better or worse. We are able to leverage hints in the testing environment to minimize this variation (SQL expert is currently the most powerful tool in rewriting the SQL territory) A well-planned SQL statement can be used to generate OUTLINES, and then the OUTLINES will be transplanted to the product database while taking advantage of the latest Census messages.