Parsing the planned stability of Oracle 8i and 9i
- 2020-05-17 06:49:53
- OfStack
The ORACLE tutorial you are looking at is: parsing the planned stability of Oracle 8i/9i.
Now that we've generated a simulated application, we can run it, open sql_trace, and see what happens. We will find that SQL performs a full table search to get the requested data.
In this test, full table retrieval is probably the most efficient way to do it -- but let's assume that it has been proven that using a single-column index and the and-equal option is the best execution path, how can we modify it (without having to add a hint to the code)?
By storing the profile, the answer is simple. There are actually several ways to get to what I'm going to do, so don't think of it as the only one. Oracle1 improves its features for ease of use, and the techniques described here may disappear in a future release.
What do you think it should be used for?
To make Oracle work the way we want it to, there are three phases:
Start a new session (connection) and rerun the procedure, first telling Oracle that we want to track the SQL statement that will be run and the path that SQL USES. This "path" is our first example of storing a profile.
Create a better storage profile for the problematic SQL statement, then replace the problematic one with a good one.
Start a new session and tell Oracle to start using the new storage profile when they see a matching SQL instead of using the usual optimization method; Then rerun the process.
We must stop and start the new session to ensure that the cursor in the pl/sql buffer (cursors) is not kept open. The storage profile is only generated and/or applied when a cursor is analyzed, so we must make sure that similar cursors that existed before are closed.
Start 1 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 collecting the execution path (otherwise the following SQL you executed will also be placed in the table where the profile is stored, making the following a little difficult).
alter session set create_stored_outlines = false;
To see the results of this, we can query the following views to see the profile details Oracle created and stored for us.
We can see that there is only one storage profile in the demo classification, and if we look at sql_text in the profile we can see statements that are similar to our original PL/SQL code, but a little different. This is an important point because Oracle only USES storage profiles if the stored sql_text is very similar to the SQL that will be executed. In fact, in Oracle8i, two SQL statements need to be exactly the same, which is a big problem with storing profiles.
You can see from the list that there is a set of hints in the storage profile that describes how Oracle is executed (or will be executed) and SQL. This plan USES 1 full table search -- even if it's an operation like 1 full table search, Oracle USES a lot of hints to ensure that the plan is executed.
Note that storage profiles generally fall into one category; Here is the demo classification, which we specify by the alter session command. If we use true instead of demo in the above command, we will find the storage profile in a class named default.
The storage profile has a name that must be unique to the entire database. No two profiles have the same name, even if they are generated by different users. In fact, profiles are not owned by anyone, they are created only. If you create a storage profile that matches an SQL statement that I will execute later, Oracle will apply your hints list to my statement -- even though these hints are meaningless in my schema. (so we have a completely different option to trick the storage profile, but that's for another article). You may also notice that when Oracle automatically generates a storage profile, its name contains a timestamp of nearly a millisecond.
Continuing with our problematic SQL, we determined that if we use hint of 1 /*+ and_equal(so_demo, sd_i1, sd_i2) */, Oracle will use the desired execution path, so we now explicitly create a storage profile by:
This explicitly creates a storage profile named so_fix in our demo class. We can re-query user_outlines and user_outline_hints by using the condition name='SO_FIX' to see what the storage profile looks like.
Note that FULL(SO_DEMO) has been replaced by AND_EQUAL(SO_DEMO SD_I1 SD_I2), which is what we want to see.
Now we must "replace" the two storage profiles. We want Oracle to use the new hint list when it sees the previous statement; To do this, we have to cheat. user_outlines and user_outline_hints views are generated by two tables (ol$and ol$hints, respectively), which are owned by outln mode. We have to modify these tables directly. This means connecting to the database using outln and updating the form with one authorized account.
Fortunately, the outln form 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 checking the names carefully, we can exchange the storage profile tips by exchanging the names on the ol$hints table:
You may feel a little uncomfortable doing this, especially according to the guidelines - but this update is allowed on Metalink. However, you will need to make a second update to ensure that the number of hints associated with each storage profile remains 1. If you ignore this step, you will find that some of your storage profiles are corrupted, or corrupted during the processing of an export/import.
Once the above statement is complete, you can initiate a new connection, tell it to use the storage profile, rerun the procedure, and exit. Again, you can use sql_trace to confirm that Oracle does so. To tell Oracle to use the modified storage profile, you can use the following command:
alter session set use_stored_outline =&nb
If you examine the trace file, 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 will show the and_equal path used, and the second will probably be a full table search, because the storage profile may not be called when tkprof executes explain plan on the SQL tracked.
Now that we have generated a single 1 profile, we need to transfer it to production. Storage profiles have a number of features that can help you do this. For example, we could rename the storage profile, export it from the development environment, then import it into the production system, first verify it in one of the production test categories, and then move 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 1 development system to 1 production system, we can make use of adding an where statement to 1 exported parameter file, so our export parameter file might be:
Enhancement of Oracle 9
There are many other details to consider when using storage profiles, and in Oracle8 there are inconvenient limitations on what they can do and how they work, although many of these problems have been eliminated in Oracle 9.
The biggest disadvantage of using the storage profile in Oracle8 is that it can only be used on the stored text and the text to be executed exactly the same. In Oracle 9, there is a "standardized" process that eliminates this matching restriction; The text will be converted to uppercase and whitespace removed before comparison. This improves the chances that different SQL can use the same storage profile.
There are also problems with complex execution plans that invoke multiple storage blocks. Oracle solved this problem by rolling out an ol $nodes table in outln mode in Oracle 9. This helps Oracle reduce the hints list in ol$hints, and you can cross-apply them correctly in the subsections where SQL is about to be executed. However, the policy of swapping hints between stored profiles has one side effect, because the ol$hints table also requires text lengths and offsets for different details. When upgrading to Oracle9, you will need to choose methods to manage the storage profile, such as a second schema with special data sets or missing indexes, or a storage view with built-in hints to replace the named tables in the text.
Another feature of Oracle9 is more support for managing storage profiles, including the introduction of a package that lets you edit storage profiles directly. More importantly, there is an option that allows you to manage your schedule on your production system more securely. While no one likes to experiment in a production environment, sometimes only the production system has the right data distribution and volume to allow you to determine the optimal execution path for SQL. In Oracle9, you can create a private copy of the outln table and release the "public" profile for a "private" experiment, so you don't run the risk of your private storage profile being seen by the end user's code. I personally think this is a last resort, but I can imagine that sometimes it is necessary. More securely, if you have an full-scale UAT or development system, you can use this feature to test freely.
warning
This article gives you enough information to experiment with storing profiles; But there are a few things you should be aware of when applying this technology to a production system.
First -- in Oracle8i, outln (which is the mode in which the tables with the stored profiles reside) has a default password, and the account has a very dangerous permission. You have to change the password of this account. In Oracle9i, you will find that the account has been locked.
2-- the table that holds the stored profile is created in the system table space. In a production system, when you start creating a storage profile, you will find that you are using a lot of space in the system table space. It is therefore best to move these tables, preferably into their own table Spaces. Unfortunately, one of the tables has an long column, so you may need to use exp/imp to move these tables to a new table space.
3-- while storage profiles can be useful for solving serious performance problems, they also have a cost. If a storage profile is activated, Oracle checks to see if there is an associated storage profile when analyzing each new statement. If a large number of statements do not have a stored profile, then you need to balance this overhead with the performance gain you get from having fewer stored profile statements to see if it is worth the effort. However, this problem will only occur on a system with a more serious performance problem.
conclusion
Storing profiles has huge benefits. Storing profiles is the only way to make third-party applications run more efficiently when you cannot modify source code or index policies.
Further, if you are faced with the problem of switching a system from rules-based to overhead-first, then storing profiles is your most efficient and risk-free option.
If you need to get the most out of storage profiles, Oracle9 has some enhancements that allow it to cover more classes of SQL, reduce overhead, and give you more flexibility in testing, managing, and installing storage profiles.
Starting with Oralce 8.1, Oracle adds a new feature called Stored Outlines, or Plan Stability (plan stability). This feature provides three benefits. First, you can optimize the processing of expensive statements. Second, if you have statements Oracle that take a long time to optimize (rather than execute), you can save time and reduce contention in the optimization phase. Finally, it allows you to choose to use the new cursor_sharing parameter without worrying about the optimal execution path.
To know how to use the storage profile optimally, let's start by running some extremely inefficient SQL stored procedures. Note that we cannot modify the source code (in theory).
We'll look at 1 how to track SQL statements and look at its current execution plan in the database, find some tips to improve the performance of SQL statements, and then ask Oracle to use our tips when reexecuting the SQL statement.
In this example, we will create a user, create a table in the user's schema, and create a stored procedure to access the table. We will use the wrap tool on this stored procedure so that we cannot get the source code in reverse. We will then debug the execution of SQL through this stored procedure.
In the example, we will assume that the storage requirements have been automatically installed when the database is created.
The preparatory work
Create a user with the following permissions: create session, create table, create procedure, create any outline, and alter session. Connect with this user and run the following script to create a table:
Coding is then required to create a stored procedure to access the table. Create a script called c_proc.sql, as follows:
You could, of course, simply execute this script to set up the process -- but for more effect, 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
Instead of executing the c_proc.sql script to generate this procedure, you will execute the c_proc.plb script where you cannot see the source code. You will find our SQL statement missing in the user_source view.
[NextPage]
What does this app do?
Now that we've generated a simulated application, we can run it, open sql_trace, and see what happens. We will find that SQL performs a full table search to get the requested data.
In this test, full table retrieval is probably the most efficient way to do it -- but let's assume that it has been proven that using a single-column index and the and-equal option is the best execution path, how can we modify it (without having to add a hint to the code)?
By storing the profile, the answer is simple. There are actually several ways to get to what I'm going to do, so don't think of it as the only one. Oracle1 improves its features for ease of use, and the techniques described here may disappear in a future release.
What do you think it should be used for?
To make Oracle work the way we want it to, there are three phases:
Start a new session (connection) and rerun the procedure, first telling Oracle that we want to track the SQL statement that will be run and the path that SQL USES. This "path" is our first example of storing a profile.
Create a better storage profile for the problematic SQL statement, then replace the problematic one with a good one.
Start a new session and tell Oracle to start using the new storage profile when they see a matching SQL instead of using the usual optimization method; Then rerun the process.
We must stop and start the new session to ensure that the cursor in the pl/sql buffer (cursors) is not kept open. The storage profile is only generated and/or applied when a cursor is analyzed, so we must make sure that similar cursors that existed before are closed.
Start 1 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 collecting the execution path (otherwise the following SQL you executed will also be placed in the table where the profile is stored, making the following a little difficult).
alter session set create_stored_outlines = false;
To see the results of this, we can query the following views to see the profile details Oracle created and stored for us.
We can see that there is only one storage profile in the demo classification, and if we look at sql_text in the profile we can see statements that are similar to our original PL/SQL code, but a little different. This is an important point because Oracle only USES storage profiles if the stored sql_text is very similar to the SQL that will be executed. In fact, in Oracle8i, two SQL statements need to be exactly the same, which is a big problem with storing profiles.
You can see from the list that there is a set of hints in the storage profile that describes how Oracle is executed (or will be executed) and SQL. This plan USES 1 full table search -- even if it's an operation like 1 full table search, Oracle USES a lot of hints to ensure that the plan is executed.
Note that storage profiles generally fall into one category; Here is the demo classification, which we specify by the alter session command. If we use true instead of demo in the above command, we will find the storage profile in a class named default.
The storage profile has a name that must be unique to the entire database. No two profiles have the same name, even if they are generated by different users. In fact, profiles are not owned by anyone, they are created only. If you create a storage profile that matches an SQL statement that I will execute later, Oracle will apply your hints list to my statement -- even though these hints are meaningless in my schema. (so we have a completely different option to trick the storage profile, but that's for another article). You may also notice that when Oracle automatically generates a storage profile, its name contains a timestamp of nearly a millisecond.
Continuing with our problematic SQL, we determined that if we use hint of 1 /*+ and_equal(so_demo, sd_i1, sd_i2) */, Oracle will use the desired execution path, so we now explicitly create a storage profile by:
This explicitly creates a storage profile named so_fix in our demo class. We can re-query user_outlines and user_outline_hints by using the condition name='SO_FIX' to see what the storage profile looks like.
Note that FULL(SO_DEMO) has been replaced by AND_EQUAL(SO_DEMO SD_I1 SD_I2), which is what we want to see.
Now we must "replace" the two storage profiles. We want Oracle to use the new hint list when it sees the previous statement; To do this, we have to cheat. user_outlines and user_outline_hints views are generated by two tables (ol$and ol$hints, respectively), which are owned by outln mode. We have to modify these tables directly. This means connecting to the database using outln and updating the form with one authorized account.
Fortunately, the outln form 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 checking the names carefully, we can exchange the storage profile tips by exchanging the names on the ol$hints table:
You may feel a little uncomfortable doing this, especially according to the guidelines - but this update is allowed on Metalink. However, you will need to make a second update to ensure that the number of hints associated with each storage profile remains 1. If you ignore this step, you will find that some of your storage profiles are corrupted, or corrupted during the processing of an export/import.
Once the above statement is complete, you can initiate a new connection, tell it to use the storage profile, rerun the procedure, and exit. Again, you can use sql_trace to confirm that Oracle does so. 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: parsing the planned stability of Oracle 8i/9i. sp; demo;If you examine the trace file, 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 will show the and_equal path used, and the second will probably be a full table search, because the storage profile may not be called when tkprof executes explain plan on the SQL tracked.
[NextPage]
From development to generation environment
Now that we have generated a single 1 profile, we need to transfer it to production. Storage profiles have a number of features that can help you do this. For example, we could rename the storage profile, export it from the development environment, then import it into the production system, first verify it in one of the production test categories, and then move 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 1 development system to 1 production system, we can make use of adding an where statement to 1 exported parameter file, so our export parameter file might be:
Enhancement of Oracle 9
There are many other details to consider when using storage profiles, and in Oracle8 there are inconvenient limitations on what they can do and how they work, although many of these problems have been eliminated in Oracle 9.
The biggest disadvantage of using the storage profile in Oracle8 is that it can only be used on the stored text and the text to be executed exactly the same. In Oracle 9, there is a "standardized" process that eliminates this matching restriction; The text will be converted to uppercase and whitespace removed before comparison. This improves the chances that different SQL can use the same storage profile.
There are also problems with complex execution plans that invoke multiple storage blocks. Oracle solved this problem by rolling out an ol $nodes table in outln mode in Oracle 9. This helps Oracle reduce the hints list in ol$hints, and you can cross-apply them correctly in the subsections where SQL is about to be executed. However, the policy of swapping hints between stored profiles has one side effect, because the ol$hints table also requires text lengths and offsets for different details. When upgrading to Oracle9, you will need to choose methods to manage the storage profile, such as a second schema with special data sets or missing indexes, or a storage view with built-in hints to replace the named tables in the text.
Another feature of Oracle9 is more support for managing storage profiles, including the introduction of a package that lets you edit storage profiles directly. More importantly, there is an option that allows you to manage your schedule on your production system more securely. While no one likes to experiment in a production environment, sometimes only the production system has the right data distribution and volume to allow you to determine the optimal execution path for SQL. In Oracle9, you can create a private copy of the outln table and release the "public" profile for a "private" experiment, so you don't run the risk of your private storage profile being seen by the end user's code. I personally think this is a last resort, but I can imagine that sometimes it is necessary. More securely, if you have an full-scale UAT or development system, you can use this feature to test freely.
warning
This article gives you enough information to experiment with storing profiles; But there are a few things you should be aware of when applying this technology to a production system.
First -- in Oracle8i, outln (which is the mode in which the tables with the stored profiles reside) has a default password, and the account has a very dangerous permission. You have to change the password of this account. In Oracle9i, you will find that the account has been locked.
2-- the table that holds the stored profile is created in the system table space. In a production system, when you start creating a storage profile, you will find that you are using a lot of space in the system table space. It is therefore best to move these tables, preferably into their own table Spaces. Unfortunately, one of the tables has an long column, so you may need to use exp/imp to move these tables to a new table space.
3-- while storage profiles can be useful for solving serious performance problems, they also have a cost. If a storage profile is activated, Oracle checks to see if there is an associated storage profile when analyzing each new statement. If a large number of statements do not have a stored profile, then you need to balance this overhead with the performance gain you get from having fewer stored profile statements to see if it is worth the effort. However, this problem will only occur on a system with a more serious performance problem.
conclusion
Storing profiles has huge benefits. Storing profiles is the only way to make third-party applications run more efficiently when you cannot modify source code or index policies.
Further, if you are faced with the problem of switching a system from rules-based to overhead-first, then storing profiles is your most efficient and risk-free option.
If you need to get the most out of storage profiles, Oracle9 has some enhancements that allow it to cover more classes of SQL, reduce overhead, and give you more flexibility in testing, managing, and installing storage profiles.
On 1 page
Previous page [1] [2]