Explain the plan stability of Oracle 8i/9i

The ORACLE tutorial is: parsing the planned Stability of Oracle 8i/9i.

Starting from Oralce8.1, Oracle has added a new feature called Stored Outlines, or Plan Stability ). This feature brings three benefits. First, you can optimize the processing of statements with high overhead. Second, if some statements require Oracle to be optimized (rather than executed) for a long time, you can save time and reduce competition in the optimization phase. Finally, it allows you to choose to use the new cursor_sharing parameter without the need to worry about using the optimized execution path.

To know how to use the storage overview is optimal, we should first run some extremely inefficient SQL stored procedures, note that we cannot modify the source code (theoretically ).
　
We will take a look at how to track SQL statements, view their current execution plans in the database, find some tips to improve the performance of SQL statements, and then re-execute the SQL statement, let Oracle use our prompt.

In this example, we will create a user, create a table in the user's mode, and create a stored procedure to access the table. We will use the wrap tool in this stored procedure, in this way, we cannot obtain the source code in reverse mode. Then we will debug SQL Execution through this stored procedure.

In this example, we assume that the storage volume must be automatically installed when the database is created.

Preparations

Create a user with the following permissions: create session, create table, create procedure, create any outline, and alter session. Create a table by connecting the user and running the following script:

Then you need to code to create a stored procedure to access the table. Create a script called c_proc. SQL, as follows:

Of course, you can also directly execute this script to establish the process-however, to be more effective, go to the command line of the operating system and execute the following command:

Wrap iname = c_proc. SQL

Response:

Processing c_proc. SQL to c_proc.plb

This process is not generated by executing the c_proc. SQL script, but by executing the c_proc.plb script that does not see the source code. You will find that our SQL statement cannot be found in the user_source view.

[NextPage]

What is the role of this application?

Now we have a simulated application. We can run it, open SQL _trace, and see what happened. We will find that this SQL statement executes a full table search to obtain the requested data.

In this test, full table search may be the most effective method-but let's assume that the use of a single column index and the and-equal option is the best execution path, how can we modify it (without adding a prompt to the Code )?

By storing the summary, the answer is simple. There are actually several ways to achieve what I do below, so don't think this is the only way. Oracle has been improving its features for ease of use. The technology mentioned here may disappear in a future version.

What do you want this application to do?

To make Oracle operate as we think, there are three phases:

. Start a new session (connection), and then re-run the process. First, tell Oracle that we want to track the SQL statement to be run and the path used by the SQL statement. The "path" here is the first example of the storage overview.

. Create a better storage summary for problematic SQL statements, and then use it to replace problematic SQL statements.

. Start a new session and tell Oracle to start using the new storage summary when it sees the matching SQL statement, instead of using the usual Optimization Method for execution. Then re-run the process.

We must stop and start a new session to ensure that cursors In the pl/SQL Buffer are not kept open. The storage summary is generated and/or applied only when a cursor is analyzed. Therefore, we must ensure that the previous similar cursor is closed.

Start a session and execute the following command:

Alter session set create_stored_outlines = demo;

Then run a small anonymous code block to execute the process. For example:

Then stop the path of the collection execution (otherwise some of the SQL statements you run will be placed in the table that stores the summary, which makes the subsequent processing a little difficult ).

Alter session set create_stored_outlines = false;

To see the results, we can query the following view to see the summary of Oracle's creation and storage for us ..

We can see that there is only one storage summary in the demo classification. To view SQL _text in the summary, we can see statements similar to the original PL/SQL code, but somewhat different. This is important because Oracle uses the storage summary only when the stored SQL _text is very similar to the SQL to be executed. In fact, in Oracle8i, two SQL statements must be exactly the same, which is also a big problem in storing summary.

You can see from the list that the storage summary contains a set of hints used to describe how Oracle executes (or is about to execute) the SQL statement. This plan uses a full table search-even a full table search operation, Oracle uses a large number of hints to ensure the execution of the plan.

Note that the storage summary usually belongs to a category. Here, the demo category is specified by the alter session command. If we use true in the preceding command to replace the demo, we will find the storage summary in a category named default.

The storage summary has a name that must be unique throughout the database. No two summary names are the same, even if they are generated by different users. In fact, the summary is not owned by anyone. They only have creators. If the storage summary you created matches a SQL statement that I will execute later, oracle will apply your hints list to my statements-even if these hints are meaningless in my mode. (In this way, we have completely different options to fool the storage overview, but this is another article ). You may also notice that when Oracle automatically generates a storage summary, its name contains a timestamp close to milliseconds.

Continue to process our problematic SQL statement. We determine that if you use a hint of/* + and_equal (so_demo, sd_i1, sd_i2, oracle will use the desired execution path. Therefore, we can create a storage overview using the following methods:

In this way, a storage summary named so_fix is created in our demo category explicitly. We can query user_outlines and user_outline_hints again using the name = 'so _ fix' condition to check the storage overview.

Note that the line FULL (SO_DEMO) has been replaced by AND_EQUAL (SO_DEMO SD_I1 SD_I2), which we want to see.

Now we must replace the two storage profiles. We want Oracle to use the new hint list when we see previous statements; to do this, we must do some deception. The user_outlines and user_outline_hints views are generated by two tables (ol $ and ol $ hints). They are owned by the outln mode and must be modified directly; this means that you need to use outln to connect to the database and use an account with permissions to update the table.

Fortunately, the outln table does not have any reference integrity restrictions. Conveniently, the relationships between ol $ (outlines) and ol $ hints (hints) tables are defined by the summary name (stored in the ol_name column ). Therefore, by carefully checking the name, we can exchange the storage summary information on the ol $ hints table:

You may feel a bit unaccustomed to doing so, especially as recommended in the Guide-but this update is allowed on Metalink (this is a technical support site for Oracle. However, you still need to perform the second update to ensure that the number of hints associated with each storage summary is consistent. If you ignore this step, you will find that some of your storage profiles are corrupted or damaged during the processing of an export/import.

Once the preceding statement is completed, you can initiate a new connection, tell it to use the storage summary, re-run the process, and then exit. Similarly, you can use SQL _trace to confirm that Oracle does. To tell Oracle to use the modified storage overview, you can use the following command:

Alter session set use_stored_outline = & nb

[1] [2] Next page

The ORACLE tutorial is: parsing the planned Stability of Oracle 8i/9i. Sp; demo;

Check the trace file. You will find that the SQL statement uses the path of and_equal. (If you use tkprof to process and interpret the trace file, you will find that the output shows two conflicting paths. The first one will display the and_equal path used, and the second one will probably be a full table search, because when tkprof executes the explain plan on the SQL statement tracked, the storage may not be called ).

[NextPage]

From development to production environment

Now we have produced a single summary, and we need to transfer it to the production environment. Storage has many features that can help us do this. For example, you can rename the storage overview, export it from the development environment, and import it to the production system. First, check it in a test category in the production environment, then transfer it to the production category. Useful commands are:

Alter outline sys_outline_020503695427311 rename to and_0000_sample;
Alter outline and_assist_sample change category to PROD_CAT;

To export a summary from a development system to a production system, we can add a where statement to an exported parameter file. Therefore, our exported parameter file may be:

Oracle 9 enhancements

There are many other details to consider when using the storage overview. In Oracle8, there are some inconveniences for what they can do and how they work, however, many of these problems have been eliminated in Oracle 9.

The biggest disadvantage of using the storage Summary In Oracle8 is that it can be used only when the stored text is identical with the text to be executed. In Oracle 9, there is a "standardized" process that can eliminate this matching restriction; before comparison, the text will be converted to uppercase and spaces will be removed. This improves the chance that different SQL statements can use the same storage summary.

There are still some problems in calling complex execution plans of multiple storage blocks. Oracle released an ol $ nodes table in outln mode in Oracle 9. In this way, the hints list in ol $ hints can be reduced in Oracle, and they can be correctly cross-applied in the subarea of the SQL to be executed. However, the policy of exchanging hints between storage profiles has a side effect, because ol $ hints tables also require text lengths and offsets of different details. When upgrading to Oracle9, you need to use some methods to manage the storage summary, such as the second mode with a special data set or missing index, or a storage view with built-in hints is used to replace the table named in the text.

Another feature of Oracle9 is its more support for managing the storage overview, including the first release of a package for you to directly edit the storage overview. More importantly, there is an option that allows you to manage your plans on the production system more securely. Although no one prefers to do experiments in the production environment, in some cases, only the production system has the correct data distribution and volume, so that you can determine the optimal execution path of a SQL statement. In Oracle9, you can create a private copy of The outln table and release the "public" summary for "private" experiment, in this way, you do not have to risk your private storage summary being viewed by the end user's code. I personally think this is a final means, but I can imagine that sometimes it is necessary. More securely, if you have a full-scale UAT or development system, you can use this feature for free testing.

Warning

This article provides you with enough information to store summary experiments. However, when applying this technology to a production system, you must be aware of other things.

First -- in Oracle8i, outln (which is the mode in which the tables with the summary are stored) has a default password, and this account has a very dangerous permission. You must change the password of this account. In Oracle9i, you will find that this account has been locked.

Second, keep the table that stores the summary created in the system tablespace. In a production system, when you create a storage summary, you will find that a lot of space in the system tablespace is used. Therefore, it is best to remove these tables, preferably in their own tablespace. Unfortunately, one of the tables contains long columns, so you may need to use exp/imp to move these tables to a new tablespace.

Third-although the storage overview is useful for solving serious performance problems, it also has an overhead. If a storage summary is activated, Oracle checks whether a storage summary exists when analyzing each new statement. If a large number of statements do not store the summary, You need to balance the overhead with the performance improvement you have on few summary statements to see if it is worth doing so. However, this problem only occurs on a system with more serious performance problems.

Conclusion

The storage overview has huge benefits. When you cannot modify source code or index policies, the storage overview is the only way to make third-party applications run more efficiently.

Furthermore, if you still need to switch a system from rule-based to overhead first, the storage summary will be your most efficient and risk-free choice.

If you need to maximize the benefits of the storage overview, Oracle9 has some enhancements that allow it to cover more types of SQL statements and reduce overhead, it also allows you to test, manage, and install the storage overview more flexibly.

Previous Page

Previous Page [1] [2]