What is an implementation plan?
The so-called execution plan, as the name suggests, is a detailed plan for a query task and make a detailed scheme for completing the task. For examples in life, I will go to the UK from Zhuhai, I can choose to go to Hong Kong first, can also go to Beijing to turn, or go to Guangzhou. But how to go to the UK is cost-effective, that is, my cost is the least, this is a thing worth studying. Also for queries, the SQL we submit is merely describing our destination is a UK, but as for how to go, usually there is no prompt information in our SQL, which is determined by the database.
Let's take a simple look at the comparison of the implementation plan:
SQL> Set Autotrace Traceonly
Executive plan one:
SQL> SELECT Count (*) from T;
Count (*)
------------
24815
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (aggregate)
2 1 Table Access (Full) of 'T'
Executive Plan 2:
SQL> SELECT Count (*) from T;
Count (*)
------------
24815
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 26 Card = 1)
1 0 sort (aggregate)
2 1 Index (Full Scan) of 'T_index' (cost = 26 card = 28180)
In these two executive plans, the first representation is made by performing full-table scanning, reading the data in the entire table into the memory, and the second representation can read the entire index into memory according to the index in the table. To accumulate, do not read the data in the table. But which two ways are so fast? Usually, it may be two more fast, but it is not absolute. This is a very simple example to demonstrate the difference in implementation plan. For complex SQL (table connection, nesting, etc.), executing plan may dozens of or even hundreds of kinds, but that is the best? We don't know beforehand, the database itself does not know, but the database will choose an executive plan according to certain rules or statistics (Statistics), usually, the choice is relatively excellent, but there is also a mistake, this is The value of this discussion is.
Oracle optimizer mode
Oracle Optimizer has two major categories, rules-based and cost-based, in SQLPLUS we can view the default optimizer mode defined in the init file.
SQL> Show Parameters Optimizer_Mode
Name Type Value
----------------------------------------------------------------------
Optimizer_Mode String Choose
SQL>
This is Oracle8.1.7 Enterprise Edition, we can see that the database optimizer mode is Choose by default, we can also set to rule, first_rows, all_rows. You can set all session settings for the entire instance in the init file, you can also set a session setting: SQL> ALTER Session Set Optimizer_Mode = Rule;
The session has changed.
SQL> ALTER Session Set Optimizer_Mode = first_ROWS;
The session has changed.
SQL> ALTER Session Set Optimizer_Mode = All_ROWS;
The session has changed.
Based on the rules, the database is defined according to the information such as table and index, and the execution plan is generated according to certain rules; the price based on the query, the database is collected according to the statistics of the collected table and index (through the Analyze command or use dbms_stats bag to collect Summary to decide to select a database to think the optimal execution plan (actually not optimal). Rule is based on rules, and Choose indicates that if the query table has a collection of gathering, it is performed based on the cost (in the Choose mode is first_ROWS), otherwise it is performed based on rules. In two ways based on the price, First_ROWS refers to the execution plan to use the least resource return part of the return part to the client, and for the sorting page, this query is particularly applicable. All_Rows refers to the minimum of the country to return the result to the client. .
Based on rule-based mode, the execution plan of the database is usually relatively stable. But in the price-based mode, we have a greater opportunity to choose the best implementation plan. Due to the characteristics of many query in Oracle must be reflected in the price-based mode, we usually do not choose Rule (and Oracle declare that the rule will not be supported from the Oracle 10i version database. Since it is based on the price-based model, it is to say that the choice of the implementation plan is determined based on the statistics of the table, index, etc., which is collected regularly based on the analysis command or dbms_stats package. There is one possibility, that is, because the collection information is a very consumable resource and time action, especially when the table data is large, because the collection information is completely statistics for the entire table data, this is what we must Careful consideration. We can only regular information collection when the server is idle. This shows that during a period of time, the statistics may not be consistent with the data of the database itself; it is also an incorrect part of Oracle's statistics itself (detailed reference Oracle Document), more important issues are timely statistics. It is already more accurate, but the choice of Oracle's optimizer is not always the optimal solution. This also relies on the calculation rules for Oracle's cost of different implementation programs (we usually unknown to specific computational rules). This is better than us to decide from Hong Kong or from Beijing to the British, ticket, ticket, etc., we don't know, or we now understand the price information, when we travel to, the real price is followed us. The budget has changed. All factors will affect our entire overhead.
What is the implementation plan stability?
Oracle has the possibility of performing a plan to choose a mistake. This is also some phenomena we often meet. For example, some people say that my program runs very well in the test database, but in the product database is very poor, even the hardware conditions are better than the former, why is this? ? Hardware resources, statistics, parameter settings may have an impact on the implementation plan. Because there are too many factors, we always have a inexplicable fear in the future. My product database is not good after going online. So Oracle provides a stable implementation of the program, that is, the Outlines generated by the Outlines generated in the test environment in the test environment to the product database, so that the implementation plan does not change with other factors. So what is OUTLINES? First, you need to introduce one content, Oracle provides the ability to boot the optimizer to generate the power we want in SQL to boot the ability to produce the execution plan we want. This is particularly effective in multi-table connections and complex queries. Hints has a lot of types, you can set an optimizer target (Rule, Choose, First_ROWS, All_ROWS), you can specify the order of the table connection, which index can be specified which index of which table is used, etc., you can make a lot of fine control of SQL. In this way, these Hints we want will produce these Hints, Oracle can store these hints, we call Outlines. Through Store Outlines, we have the ability to have the same implementation plan in the future, that is, the ability to have stabilized the implementation plan.
Here, I want to give an additional explanation. In fact, we rewrite SQL through tools, such as SQL after SQL EXPERT rewritten, these more than just add HINTS and have changed SQL, or store Outlines, And can be applied to the application. But this is not necessarily effective, we must test whether it takes effect. However, since it is called the wrong OUTLINES, the database is only ignored by the past regeneration execution plan without returning an error, so we dare to use it so much. Of course, in the Oracle documentation, it is not specified, in the document, it is only explained. If Outlines is existed, Hints is added in SQL, Outlines is used to ignore Hints. This feature uses this feature in Lecco, which can combine SQL EXPERT's ability to overwrite SQL and stabilize the capabilities of the implementation plan, then we have quite powerful SQL for applications that cannot change source code. Optimization.
Maybe we will have questions, if you have stabilized the implementation plan, is it gathered in statistics? This is due to several reasons, first, the current execution plan is not necessarily suitable for data that has changed in the future, there is a current implementation plan does not meet the efficiency of the future data, and the situation of new statistics The implementation plan to be produced is not all reasonable. At this time, we can use the new collected statistics, but we use Oracle's implementation plan stability under the implementation plan stability under the new statistics, which combines us to establish a satisfactory efficient database operation. surroundings.
We also need a concern, and the DBMS_STATS package provided by Oracle has the ability to collect statistics, but also has the ability to statistics Export / Import in the database, but also has only statistics to make statistics. The ability of the database (collecting statistics in a specific table instead of take effect immediately), on this basis, we can put the statistics export to IMPORT to a test environment, run our app, in the test environment We observe the latest statistics cause which implementation plan changes (DB Expert Plan Version Tracer is a tool that simulates different environments and automatically checks the implementation plan changes in different environments), it is better to become. We can use Hints in the test environment in the test environment (SQL EXPERT is the most powerful tool to rewrite the SQL this area) produces a good execution plan, using these SQLs can generate Outlines, Then transplant the latest statistics in the product database to these Outlines. Finally, we have to use the implementation of planning stability capabilities. We assume that Oracle's optimizer is accurate, but the basis for optimizer selection is our SQL, which fundamentally determines the efficiency of operation, which is more important to optimize the link. SQL is the foundation (of course, the design of the database is based on the foundation), a SQL written is good, it is equivalent to us to want to go to the UK, but my starting point is in Zhuhai, your starting point is in Tibet's the most edge of Tibet One place, no matter what the best route you do, you are not as good as the price spent in Zhuhai to the UK. For this reason, if it is usually our own design program, we can try to modify the SQL code, but if the application is a third-party development, or we are secondary development based on others, such as our ERP system Is SAP, even if we find SQL in the database, we have no power to modify the application. However, we can capture these SQL in the database, then generate a good execution plan for these SQLs, which is bound to Outlines that produces SQL and generated Outlines with execution plan stability. This allows you to improve the running efficiency of the program on the basis of not modifying the source code. This is the only way.
How to use the implementation plan stability
Let's first demonstrate how to perform plan stability with a simplest example.
First we have to create a category, put the execution plan we want to stably in this category, this is a classification of the implementation plan, we can create a lot of category, but each of our session can only select one of the category Use the customized execution plan.
Usually we use a simple way to perform this process:
First, in order to generate an execution and observation execution plan, we create a table that saves the execution plan.
SQL> @e: / oracle / ora81 / rdbms / admin / utlxplan;
The table has been created.
This script UTLXPLAN.SQL is in $ oracle_home / rdbms / admin directory
Then create a experimental table.
SQL> CREATE TABLE T AS SELECT * ALL_OBJECTS;
The table has been created.
SQL> CREATE INDEX T_INDEX ON T (Object_ID);
Index has been created. (Note that we create indexed fields are non-empty fields)
Here be started to open the execution plan tracking.
SQL> Set autotrace on
SQL> SELECT Count (*) from T;
COUNT (*) ----------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (aggregate)
2 1 Table Access (Full) of 'T'
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
422 Consistent Gets
418 Physical READS
0 redo size
370 Bytes Sent Via Sql * Net to Client
425 Bytes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
1 rows proped
Here we can see the generated execution plan, the query has been fully scanned, which is actually statistics with a series of queries, but because this is not within our discussion, we will ignore this information.
Then we collect the statistics of this table, followed by executing the query discovery that the implementation plan has changed, no longer a full table scan but scan according to the index.
SQL> Analyze Table T Compute Statistics;
The table has been analyzed.
SQL> SELECT Count (*) from T;
Count (*)
------------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 8 Card = 1)
1 0 sort (aggregate)
2 1 Index (Fast Full Scan) of 'T_index' (COST = 8card = 30658)
From here, we will try to create a category.
All until the end of the session or SET CREATE_STORED_OUTLINES = false, we will generate and retain an execution plan for these queries, as follows, these execution plans are saved in the My_DEMO.
SQL> ALTER session set create_stored_outlines = my_demo;
The session has changed.
SQL> SELECT Count (*) from T;
Count (*)
------------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 8 Card = 1)
1 0 sort (aggregate)
2 1 Index (Fast Full Scan) of 'T_index' (COST = 8card = 30658)
SQL> ALTER session set create_stored_outlines = false;
The session has changed. Here we remove the statistics of the table, then perform the query to see.
SQL> Analyze Table T Delete Statistics;
The table has been analyzed.
SQL> SELECT Count (*) from T;
Count (*)
------------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (aggregate)
2 1 Table Access (Full) of 'T'
We found that the execution plan has been restored into a full mete-aforemention.
So we tried to make session using the Category we generated in the execution query.
SQL> ALTER session set using_stored_outlines = my_demo;
The session has changed.
SQL> SELECT Count (*) from T;
Count (*)
------------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 4 CARD = 1)
1 0 sort (aggregate)
2 1 Index (Fast Full Scan) of 'T_index' (Non-Unique) (COST = 4 Card = 35450)
At this time we found that we have successfully used the execution plan saved in this category in MY_DEMO.
Then we have to verify one thing here, that is, in the new Oracle9.2.0 version, about SQL, even if space, case, case, and wrap are different, we can still use the original implementation plan (that is, database It can be determined to be the same SQL), which cannot be done in the previous version of 9i.
SQL> SELECT
2 count (*) from t;
Count (*)
------------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 4 CARD = 1)
1 0 sort (aggregate)
2 1 Index (Fast Full Scan) of 'T_index' (Non-Unique) (COST = 4 Card = 35450)
In SQLPLUS, we have a wrap with space, and put from T → From T, but the database can still think that our SQL is the same, this is newly added in 9i.
Then we let session terminate the use of implementation plan stability and look at the query.
SQL> ALTER session set use_stored_outlines = false;
The session has changed.
SQL> SELECT Count (*) from T;
Count (*)
------------
30658
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (aggregate)
2 1 Table Access (Full) of 'T'
It can be seen that the query restores the full table scanning method rather than scans according to the index.
Let's take a look at some information saved for the implementation plan.
SQL> SELECT SQL_TEXT, NAME, CATEGORY from user_outline where category = 'my_demo';
SQL_Text Name Category
------------------------------------------------- ---------
SELECT Count (*) from T sys_outline_030401154756109 my_demo
The system automatic naming name is SYS_OUTLINE_030401154756109.
The category is MY_DEMO.
SQL text is SELECT Count (*) from T.
Here we are inquiry is View, in essence, all related information is stored below these tables.
SQL> SELECT Object_name from dba_objects where owner = 'OUTLN' and Object_type = 'Table';
Object_name
-------------------------------------------------- -
Ol $
OL $ hints
OL $ NODES
That is, we are actually, you can only implement our goals only with the three tables EXPs and IMP enter the new database and make the session using a Category to achieve our purpose. It is recommended to migrate these three tables out of the SYSTEM table space placed in other specific table spaces. But how can we use a Category that existing applications?
We can implement the Logon system-level trigger provided by Oracle8i.
Create a system-level trigger through the SYS user.
Create Or Replace Trigger Biti_Rainy_logon
After Logon Database
Begin
IF (user = 'rainy') THEN
Execute immediate 'alter session set us_stored_outline = my_demo';
END IF;
END;
Through this trigger, you can use my_demo this category when the user Rainy will log in to the database; of course, we can easily think of a problem with this example, that is, for SAP's ERP, we can pass Setting in the Logon trigger.
Execute immediate 'alter session set create_stored_outline = my_deemo';
This way we can collect SQL and execute planning information when the application is used.
Exchange two SQL Outlines
Now let's take 817 as an example to show how to exchange 2 different SQL Outlines.
SQL> CREATE TABLE T AS SELECT * ALL_OBJECTS;
The table has been created.
SQL> CREATE TABLE T_SMALL AS SELECT * FROM All_Objects Where Rownum <11;
The table has been created.
SQL> CREATE INDEX T_INDEX ON T (Object_ID);
Index has been created.
SQL> CREATE INDEX T_SMALL_INDEX ON T_SMALL (Object_ID); index has been created.
SQL> SELECT Count (*) from T, T_Small where t.object_id = t_small.Object_id;
Count (*)
------------
10
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (aggregate)
2 1 nested loops
3 2 Table Access (Full) of 'T_Small'
4 2 Index (Range Scan) of 'T_index' (Non-Unique)
SQL> Select Count (*) from t_small, t where t.object_id = t_small.Object_id;
Count (*)
------------
10
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (aggregate)
2 1 nested loops
3 2 Table Access (Full) of 'T'
4 2 Index (Range Scan) of 'T_Small_index' (Non-Unique)
We pay attention to these two query statements and execution plans, and then we create Outlines.
SQL> CREATE OR Replace Outline Ul1 for category my_demo on select count (*) from T, t_small where t.object_id = t_small.object_id;
The summary information has been created.
SQL> Create or Replace Outline Ul2 for category my_deemo on select count (*) from t_small, t where t.object_id = t_small.object_id;
The summary information has been created.
From here, we try to exchange Outlines, we will do through the manual Update data dictionary table (because this is an 817 version, only 2 tables can be updated, 9i more OL $ NODES table), here we have to pay attention to just now When we create Outlines, we use your own specific name. If you are automatically created Outlines, the system will give an automatic creation of the only name, which requires a closer to check which is the name you need.
SQL> Update Outln.ol $ HINTS
2 set ol_name =
3 DECODE
4 OL_NAME,
5 'ul1', 'ul2',
6 'ul2', 'ul1'
7)
8 WHERE OL_NAME IN ('UL1', 'UL2')
9 ;
20 lines have been updated.
SQL> Update Outln.ol $ OL1
2 SET HINTCOUNT =
3 SELECT HINTCOUNT4 from Outln.ol $ OL2
5 Where ol2.ol_name in ('UL1', 'UL2')
6 and il2.ol_name! = OL1.OL_NAME
7)
8 WHERE
9 OL1.OL_NAME IN ('UL1', 'UL2')
10;
2 lines have been updated.
SQL> commit;
Submitted.
After updating the OL $ and OL $ HINTS table here, let's observe the effect of our query.
SQL> ALTER session set using_stored_outlines = my_demo;
The session has changed.
SQL> SELECT Count (*) from T, T_Small where t.object_id = t_small.Object_id;
Count (*)
------------
10
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 28478 Card = 1 Bytes = 2
6)
1 0 sort (aggregate)
2 1 nested loops (cost = 28478 card = 23309 BYtes = 606034)
3 2 Table Access (Full) of 'T' (COST = 53 Card = 28425 BYTES = 3
69525)
4 2 Index (Range Scan) of 't_small_index' (Non-Unique) (CO
ST = 1 card = 82 bytes = 1066)
We observed the above red slide section and found that our inquiry program has been changed to another SQL execution plan.
Utilize tool maintenance implementation plan stability
As can be seen from the example, if the hand is doing quite troublesome things. Maybe there are still many SQLs that need to use Stored Outlines. If you are batch, I am afraid it is trouble. More importantly, a SQL actually has a lot of implementation plans, how to produce a good implementation plan for this SQL and test this is also a quite trouble.
Fortunately, there are many tools to optimize and rewrite before such tools. Lecco's SQL EXPERT is the most leading expert system in the field of SQL optimization. Of course, other companies also follow SQL EXPERT to make similar products. With these products, we can easily generate a large number of different execution plans for a SQL, and include the SQL source code itself has been rewritten after the execution plan. All of these execution plans have their own Outlines. If we can bind these Outlines with the SQL statement, then this is a great job.
The point you need to point out is that in the Oracle's official document, you have no support to modify OUTLINES. Later, you can confirm that the Oracle Service Site Metalink is confirmed. After the 9i version, you can get different SQLs. Outlines is bound to each other, and Oracle can identify whether the binding is valid then determines whether the binding Outlines is used. This gives us a convenient door, making us freely bind Outlines, which lays the foundation for our use of development tools. Thus we can use the SQL optimization tool to generate a large number of Outlines and try to change these OUTLINES to SQL binding, retest the change of the program's change and efficiency, then select Outlines we expect, and finalize these Outlines to the production environment. Lecco currently has this ability, but not officially released, still being further tested.
In the test, we were surprised to find that when different SQL OUTLINs were binded to each other, there may be a new execution plan, which is produced by the Oracle optimizer, and we don't have to be afraid. Even for us, this is a good thing, we only need to care about what implementation plans after Outlines bindings instead of this Outlines itself means what kind of implementation plan. Outlines is still obscured for us. Under such conditions, the tools show their power relative to the person. Therefore, we expect this formal launch of this tool to bring us a bigger surprise.
Stored Outlines Summary
Stored Outlines is the function launched to maintain SQL execution plan stability. It is mainly suitable for the migration of the test environment to the product database environment. When the statistics are collected in sampling, the collection statistics may cause harm to certain SQL, cannot To modify the source code, etc. In order to ensure the good operation of the product database, we need to stabilize the plan. Artificially adjusts some specific SQL, we can use SQLPLUS to be cautious to determine the Outlines required for SQL. In order to make it more convenient to make this job, we can use tools to change from SQL to the implementation plan to make these complex trivial work for us. Such a function is a surprise function.
About the author: Feng Chunpei, graduated from Beijing Information Engineering College. He has developed a telecommunications billing process, engaged in developing DBA work and doing database optimization product design work, currently providing Oracle training and services in an independent external. I love Oracle, at www.itpub.net, the database management section moderator (Biti_Rainy), personal interest is mainly in Oracle Internal, Performance Tuning. Database management, backup and recovery, database application development, SQL optimization have extensive understanding. I hope everyone will discuss Oracle and related technologies together.
