The Oracle tutorial you are looking at is to resolve the planned stability of Oracle 8i/9i.
Starting with Oralce8.1, Oracle added a new feature is stored outlines, or plan stability (planning stability). This feature brings three benefits. First, you can optimize the processing of very expensive statements. Second, if some statements Oracle takes a long time to optimize (rather than execute), 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 having to worry about it and not take the optimized execution path.
To know how to use the storage profile is optimal, we start by running some extremely inefficient SQL stored procedures, and note that we cannot modify the source code (theoretically).
We'll look at how to track the SQL statement, look at its current execution plan in the database, find out some hints to improve the performance of the SQL statement, and then let Oracle use our hints when we execute the SQL statement again.
In this example, we will create a user, build a table in the user's schema, and create a stored procedure to access the table, and we will use the Wrap tool on the stored procedure so that we cannot get the source code in reverse mode. Then we will debug the execution of SQL through this stored procedure.
In the example we will assume that the storage is already installed automatically when the database is created.
Preparatory work
Create a user, his permissions are: Create session, CREATE table, create procedure, create any outline, and alter session. Connect with the user and run the following script to create a table:
The encoding is then required to create a stored procedure to access the table. Create a script called C_proc.sql, as follows:
Of course, you can also execute this script directly to establish the process--but, to be more effective, go to the operating system's command line and execute the following command:
Wrap Iname=c_proc.sql
The response is:
Processing C_proc.sql to C_PROC.PLB
This is not done by executing the C_proc.sql script, but by executing C_PROC.PLB scripts that do not see the source code, and you will find that our SQL statements are not found in the User_source view.
[NextPage]
What is the role of this application?
Now that we have a simulated application, we can run it, open the sql_trace, and see what's going on. We will find that this SQL performs a full table search to get the requested data.
In this test, full table retrieval may be the most efficient way-but let's assume we've proven that using a Single-column index and and-equal option is the best execution path, how can we modify it (without adding a hint to the code)?
By storing the outline, the answer is simple. There are actually several ways to achieve what I've done below, so don't think it's the only way. Oracle has been improving its features for ease of use, and the technology described here may disappear in a future release.
What do you want to do with the application?
To make Oracle work as we think, there are three phases:
. Start a new session (join), and then rerun the process, first telling Oracle we want to track the SQL statement that will run and the path that the SQL uses. The "path" here is our first example of storing a profile.
. Create a better storage profile for the problematic SQL statements, and then replace the problematic with a good one.
. Starts a new session and tells Oracle to start using the new storage profile when it sees the matching SQL, rather than using the usual optimization method, and then rerun the process.
We must stop and start a new session to make sure that the cursor (cursors) in the Pl/sql buffer is not kept open. The storage profile is generated and/or applied only when a cursor is parsed, so we have to confirm that a similar cursor existed before is closed.
Start a session and execute the following command:
Alter session Set Create_stored_outlines = demo;
Then run a small block of anonymous code to perform the procedure, for example:
Then stop collecting the execution path (otherwise, some of the SQL you execute will be placed in the table that stores the profile, making the next process a bit difficult).
Alter session Set Create_stored_outlines = FALSE;
To see the results of this, we can query the following view to see the summary details that Oracle created and stored for us.
We can see that there is only one storage profile in the demo category, see the Sql_text in the summary and we can see statements that are similar to our original Pl/sql code, but somewhat different. This is important because Oracle uses the storage profile only when the stored sql_text is very similar to the SQL that will be executed. In fact, in oracle8i, two SQL statements are exactly the same, which is a big problem with storage profiles.
You can see from the list that the storage profile is a set of hints used to describe how Oracle executes (or will execute) the SQL. The plan uses a full table search-even if it's a full table search, Oracle uses a lot of hints to ensure that the plan is executed.
Note that the storage profile is usually categorized as a category; Here is the demo class, which we specify by the alter session command. If in the above command we use True instead of demo, we will find the storage profile in a category named default.
Storage profiles have a name that must be unique across the database. The names of no two profiles are the same, even if they are generated by different users. In fact, the profiles are not owned by anyone, they only have founders. If you create a storage profile that matches a SQL statement I execute later, Oracle will apply your hints list to my statement-even though these hints are meaningless in my schema. (This gives us a completely different option to cheat the storage profile, but this is another article). You may also notice that when Oracle automatically generates a storage profile, its name contains a timestamp that is close to the millisecond.
To continue with our problematic SQL, we determine that if you use a/*+ and_equal (So_demo, SD_I1, SD_I2)/hint, Oracle will use the execution path we want, So we now explicitly create a storage profile by using the following methods:
This explicitly creates a storage profile named So_fix in our demo category. We can requery User_outlines and user_outline_hints by Name= ' So_fix ' to see what the storage overview is.
Note that the full (So_demo) line has been replaced by and_equal (So_demo sd_i1 sd_i2), which is what we want to see.
Now we have to "replace" the two storage profiles. We want Oracle to use the new hint list when they see the previous statements; To do this, we must do some deception. The User_outlines and user_outline_hints views are generated by two tables (respectively, ol$ and ol$hints), they are owned by the OUTLN model, and we have to modify them directly; This means using the outln to connect to the database. and use a permission account to update the table.
Fortunately, the Outln table does not have any referential integrity restrictions. Conveniently, the relationship between the ol$ (outlines) and ol$hints (hints) tables is defined by the name of the profile (stored in the Ol_name column). So, by examining the names carefully, we can exchange the hints for storing the profiles by exchanging names on the ol$hints table:
You may feel a bit unaccustomed to doing so, especially on the advice of the guide-but this update is allowed on Metalink: This is a technical support site for Oracle. However, you will also need to make a second update to ensure that the number of hints associated with each storage profile is consistent. If you ignore this step, you will find that some of your storage profiles are damaged or corrupted in an export/import process.
Once you have completed the above statement, you can initiate a new connection, tell it to use the storage profile, rerun the process, and then exit; Similarly, you can use Sql_trace to confirm that Oracle did. To tell Oracle to use the modified storage profile, you can use the following command:
Alter session set Use_stored_outline =&NB
[1] [2] Next page
The Oracle tutorial you are looking at is to resolve the planned stability of Oracle 8i/9i. Sp;demo;
Check the trace file and you will find that the SQL is now using the and_equal path (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 show the and_equal path used, and the second one will probably be a full table search because the storage profile may not be invoked when Tkprof executes explain plan on the tracked SQL.
[NextPage]
From development to build environment
Now that we have a single summary, we need to deliver it to the production environment. The storage profile has a number of features to help us do this. For example, we could rename the storage profile, export it from the development environment, and then import it into a production system, first test it in a test category in the production environment, and then transfer it to the production category. Useful commands are:
Alter outline sys_outline_020503165427311 rename to And_equal_sample;
Alter outline and_equal_sample change category to Prod_cat;
To export the profile from one development system to a production system, we can take advantage of adding a where statement to an exported parameter file, so our export parameter file might be:
Oracle 9 's strength
There are a number of other details to consider when using the storage profile, and there are some inconvenient limitations to what they can do and how to work in Oracle8, but many of these problems have been eliminated in Oracle 9.
The biggest disadvantage of a storage profile in Oracle8 is that it can only be used exactly as the text is stored and the text that will be executed. In Oracle 9, there is a "standardized" process that eliminates this match, and the text is converted to uppercase and the spaces are removed before being compared. This increases the chance that different SQL can use the same storage profile.
There are still some problems with complex execution plans that call multiple storage blocks. Oracle is resolved by introducing a ol$nodes table in OUTLN mode in Oracle 9. This helps Oracle reduce the list of hints in ol$hints, and it can be applied correctly across the subgroups of SQL that will be executed. However, the strategy for swapping hints between storage profiles has a side effect because the Ol$hints table also requires different details of text length and offset. When you upgrade to ORACLE9, you will need to choose a few ways to manage your storage profile, such as a second pattern with a special data collection or a missing index, or a stored view with a built-in hints to replace the named table in the text.
Another feature of ORACLE9 is the increased support for managing storage profiles, including the initial launch of a package to allow you to edit the storage profile directly. More importantly, there is also an option for you to more securely manage the planning on your production system. Although no one likes to do experiments in a production environment, in some cases only the production system has the right data distribution and volume to allow you to determine the optimal execution path for an SQL. In Oracle9, you can create a private copy of a outln table and release the "public" profile for a "private" experiment so you don't have to risk your personal storage profile being seen by the end-user code. I personally think this is a last resort, but I can imagine sometimes it is necessary. More safely, if you have a full-scale uat or development system, you can use this feature to test freely.
Warned
This article gives you enough information to store a summary of the experiment, but there are a few areas you must be aware of when applying the technology to a production system.
First-in Oracle8i, Outln (which is the schema of the tables that have the storage profile) has a default password that has a very dangerous privilege. You must modify the password for this account. In Oracle9i, you will find that this account has been locked.
Second-the table that maintains the storage profile is created in the system tablespace. In a production system, when you start creating a storage profile, you will find that you will use much of the space in the system table space. So it's best to move these tables, preferably in their own tablespaces. Unfortunately, one of the tables contains a long column, so you'll probably need to use Exp/imp to move the tables to a new tablespace.
Third-although the storage profile is useful for solving serious performance problems, it has a cost. If the storage profile is activated, Oracle examines each new statement to see if there is a related storage profile. If a large number of statements do not store a summary, then you need to balance this overhead with the performance improvements you get with very little storage profile statements to see if this is worth it. However, this problem only occurs on a system with a more serious performance problem.
Conclusion
The storage profile has great benefits. When you cannot modify the source code or indexing strategy, the storage profile is the only way to make third-party applications run more efficiently.
Further, if you still need to face the problem of switching a system from rule-based to overhead, then the storage profile will be your most efficient and risk-free option.
If you need to maximize the benefits of a storage profile, there are some Oracle9 that will allow it to overwrite more classes of SQL, reduce overhead, and give you more flexibility in testing, managing, and installing storage profiles.
Previous page
prev [1] [2]
