Tutorials to .com

Tutorials to .com » Database » Oracle » Oracle 8i/9i plan to resolve the stability

Oracle 8i/9i plan to resolve the stability

Print View , by: iSee ,Total views: 12 ,Word Count: 1825 ,Date: Sun, 23 Aug 2009 Time: 3:59 PM

Oralce8.1 started from, oracle adds a new characteristic of Stored Outlines, or known as the Plan Stability (plan stability). The characteristics of the three benefits bring. First of all, you can optimize the costs of dealing with a lot of statements. Secondly, if there are some statements Oracle takes a long time to optimize (rather than the implementation), you can save time and reduce the optimization phase of the competition. Finally, it can let you choose to use the new parameters without the need to worry about cursor_sharing So instead of optimizing the implementation of the path.

Need to know how to use the stored outline is the best, we first run a number of extremely inefficient to start SQL stored procedure, it is necessary to note that we can not modify the source code (in theory).

We will look at how to track SQL statements, and view it in the database of the current implementation plan, to find out some tips to improve the performance of SQL statements, and then re-implementation of the SQL statement, let Oracle use our tips.

In this example, we will create a user in the user's model to build a form to create a stored procedure and access to the forms, we will use this stored procedure tool wrap, so that we can not reverse on the way source code. And then we will be the adoption of the SQL stored procedure to debug the implementation.

Example we will assume that money should have been stored in the database is automatically created when installed.


Create a user, his powers are: create session, create table, create procedure, create any outline, and alter session. To the user to connect and run the following script to create a form:
create table so_demo (

n1 number,

n2 number,

v1 varchar2 (10)



insert into so_demo values (1,1, One);

create index sd_i1 on so_demo (n1);

create index sd_i2 on so_demo (n2);

analyze table so_demo compute statistics;

The need for coding and then create a stored procedure to access the form. C_proc.sql create a script called, are as follows:

create or replace procedure get_value (

i_n1 in number,

i_n2 in number,

io_v1 out varchar2




select v1

into io_v1

from so_demo

where n1 = i_n1

and n2 = i_n2




Of course, you can run this script directly to the establishment of the process, However, in order to more effective, to the operating system command line and perform the following command:

wrap iname = c_proc.sql

Response: Processing c_proc.sql to c_proc.plb

This is not c_proc.sql through the implementation of the script to generate the process, but can not see the implementation of c_proc.plb script source, you will find in the view user_source we can not find the SQL statement.

This application What is the role?

Now that we have produced a simulation application, we can run it, open sql_trace, to see what is going to happen. We will find a full implementation of the SQL table to get the search request data.

In this test, the entire table search is perhaps the most effective way, but let us suppose that the use of a single row has an index and-equal options and the best execution path, we can amend it (without the code add tips)?

Summary of the adoption of storage, the answer is simple. To achieve the things I have done the following There are several methods in practice, so do not think this is the only approach. Oracle has always been to improve its characteristics in order to facilitate the use of the technology said here perhaps in the next version will be lost.

The application you want to do?

Oracle to make as we wish, as the operation, there are three stages:

1. To start a new session (connection), and then re-run the process, first of all we have to tell Oracle to be run to track SQL statements and use the SQL path. Here that the "path" is that we store a summary of the first example.

2. For the problem SQL statements to create a better storage outline, and then replace with a good question.

3. To start a new session, and tell Oracle to see the match in the SQL started to use the new storage outline, rather than using the usual approach to the implementation of optimization; and then re-run the process.

We have to stop and start a new session to ensure that the pl / sql cursor in the buffer (cursors) is not to keep open. Store a summary of a cursor only when the analysis was the generation and (or) application, so we need to confirm the existence of similar previous cursor is closed.

Start a session and perform the following command: alter session set create_stored_outlines = demo;

And then run a short anonymous block of code to implement the process, such as:

m_value varchar2 (10);


get_value (1, 1, m_value);



And then to stop the collection of the implementation of the path (unless you implement some of the following SQL will also store a summary on the table, so a bit difficult to deal with the next).

alter session set create_stored_outlines = false;

To see the results of doing so, we can query the following view-ching Oracle point of view we have created and stored for a summary of the details.

select name, category, used, sql_text

from user_outines

where category = DEMO;





select name, stage, hint

from user_outline_hints

where name = SYS_OUTLINE_020503165427311;


SYS_OUTLINE_020503165427311 3 NO_EXPAND

SYS_OUTLINE_020503165427311 3 ORDERED

SYS_OUTLINE_020503165427311 3 NO_FACT (SO_DEMO)

SYS_OUTLINE_020503165427311 3 FULL (SO_DEMO)

SYS_OUTLINE_020503165427311 2 NOREWRITE

SYS_OUTLINE_020503165427311 1 NOREWRITE

We can see the classification in the demo, only a summary of a store to see a summary of sql_text we can see with our PL / SQL code similar, but somewhat different language. This is a very important point, because Oracle only sql_text storage and SQL to be implemented will be very similar to when a summary of the use of storage. In fact, in Oracle8i, the two should be exactly the same SQL statement can also store a summary of a big problem.

You can see from the list of stored outline is used to describe a set of hints on how the implementation of Oracle (or will be the implementation of) the SQL. The plan to use a table-wide search, even if such a full table search operation, Oracle use a lot of hints to ensure that the implementation plan.

It should be noted that storage is usually a summary of the classification; demo classification here, we are through the alter session command to the specified. If the command above, we use true instead of demo, we will be in a default name for the category to find a summary of the store.

Summary of storage has a name, the name of the database must be unique. Summary of no two names are the same, even if they are generated by different users. In fact, the summary is not who is going to have, and they only creator. If you create a store and I will outline the implementation of a matching SQL statement, Oracle will be the application of a list of your hints to my statement, even if they are hints in my model is meaningless. (So that we have completely different storage options to deceive the outline, but this article is another thing). You may also be noted that storage is automatically generated when a summary of Oracle, it contains the name of a close to the time stamp in milliseconds.

We continue to deal with the problem that SQL, we determine if you are using a / * + and_equal (so_demo, sd_i1, sd_i2) * / the hint, then Oracle will use the implementation of the path we want, so we now approach the adoption of the following significant style to create a stored outline:

create or replace outline so_fix

for category demo on

select / * + and_equal (so_demo, sd_i1, sd_i2) * / v1

from so_demo

where n1 = 1

and n2 = 2;

Oracle Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.