Oracle Outlines – aka Plan Stability
Roughly 10 years ago, Oracle introduced a way to lock down execution plans. This was a response to the inherent unpredictability of the early cost based optimizer. The Oracle marketing machine even gave the feature the name “Plan Stability”. It appeared to be a good approach, basically allowing a DBA to lock an existing plan for a given statement. The mechanism employed to accomplish this was to create hints that didn’t allow the optimizer much (if any) flexibility to come up with an alternative plan. These hints are stored in the database in a structure called an OUTLINE or sometimes a STORED OUTLINE. The optimizer could then apply these hints behind the scenes whenever a SQL statement that matched was executed. By the way, “matching” basically means that the text of the statement matches. Originally outlines had to match character for character just like the normal rules for sharing SQL statements, but for some reason, Oracle later decided that the matching algorithm should be somewhat relaxed as compared to Oracle’s standard. What that means is that in 10gR2 by default whitespace is collapsed and differences in case are ignored. So (at least as far as outlines are concerned) “select * from dual” is the same as “SELECT * FROM DuAl”. You’ll still get two different statements in the shared pool but they will use the same outline, if one exists.
With 9i, Oracle started to enhance this feature by adding the ability to edit the outlines themselves, but they never really completed the job. They pretty much quit doing anything with the feature after 9i. In fact, the script that creates the DBMS_OUTLN package ($ORACLE_HOME/rdbms/admin/dbmsol.sql) has not been updated since early in 2004 (with the exception of a tweak to keep it working in 11g). Anyway, I think it is a great feature with two primary uses.
- First, it can be used to freeze a plan for a statement. This is especially helpful in situations where bind variable peeking is causing Oracle to alternate between a couple of plans.
- Second, it can be very helpful when dealing with an application where the code can not be modified. Outlines provide a means of altering the execution plan for a statement without changing the code or making changes to the basic database configuration.
Lot’s of people have written about outlines, so I don’t want to just repeat information that is already available. But there doesn’t seem to be a single spot that pulls together all (at least what I consider to be all) the important stuff. Also, most of the stuff I have seen about outlines was written for 8i or 9i. As this is being written, 11gR1 has been out for over a year (although it has still not been widely adopted), and 10gR2 is far and away the most prevalent version in production. So, here we go.
Outlines can be created two ways.
- You can use the CREATE OUTLINE statement – which allows you to give your outline a name, but requires you to include the SQL statement as part of your CREATE OUTLINE statement. Therefore you can’t see what the execution plan is before creating the outline. Not very useful in my opinion.
- You can use the CREATE_OUTLINE procedure in the DBMS_OUTLN package – which doesn’t allow you to give your outline a name, but does let you specify a specific child cursor of a specific SQL statement in the shared pool. This means you can check the execution plan before creating the outline and that you can be sure the statement exactly matches what is being sent from the application.
Here’s an example:
CREATE OUTLINE myoutline FOR CATEGORY temp ON select * from dual;
EXEC DBMS_OUTLN.CREATE_OUTLINE('82734682234',0,'DEFAULT');
There are a few issues:
- First, outlines aren’t used unless you set the USE_STORED_OUTLINES pseudo parameter. This can be set at the session or the system level. Setting this at the session level only makes sense to me for testing purposes. The value can be TRUE, FALSE, or a category name. (more about categories in a minute). The default value for 9i and 10g and 11g is FALSE. This means that even if an outline is created, it won’t be used. The really irritating thing about USE_STORED_OUTLINES is that it is not a real boy (er, parameter). So you can’t see what it’s set to by selecting from the v$parameter view or it’s underlying X$ views (where the hidden parameters are exposed). Fairlie Rego has a post on his blog about using oradebug to see whether it has been set or not here. More importantly, this quirk means that the USE_STORED_OUTLINES setting is not persisted across instance bounces. This prompted an official bug and enhancement request (see Metalink Note:560331.1). The official response was to suggest a database trigger to set the value when an instance is started. (see outline_startup_trigger.sql below for the recommended trigger)
- Second, the DBMS_OUTLN.CREATE_OUTLINE procedure uses the old HASH_VALUE identifier as opposed to the newer SQL_ID that was introduced in 10g. While most of the internal structures were updated to use SQL_ID, outlines never were. This is just a slight irritation as it means you have to find the hash value to use the DBMS_OUTLN.CREATE_OUTLINE procedure. (see create_outline.sql script below for a way to get around this)
- Third, the DBMS_OUTLN.CREATE_OUTLINE procedure is a bit buggy. It often results in an error (1330 which disconnects your session from Oracle – nice!). There is a metalink note describing this issue (Note:463288.1) which references a bug (Bug 5454975) that is supposed to be fixed in 10.2.0.4. Anyway, the bottom line is that you should execute the command to enable stored outlines at the session level (i.e. ALTER SESSION SET USE_STORED_OUTLINES=TRUE) before attempting to create an outline with the DBMS_OUTLN.CREATE_OUTLINE procedure (again see the create_outline.sql script below).
- Fourth, the DBMS_OUTLN.CREATE_OUTLINE procedure does not allow a name to be specified for an outline. Instead it creates a system generated name. This is another minor irritation as outlines can be renamed easily enough with the ALTER OUTLINE command (see the create_outline.sql script below yet again for a way to do this when creating the outline)
Just a quick note on categories: A category may be specified when an outline is created. The default category is DEFAULT. If USE_STORED_OUTLINES is set to TRUE, outlines in the DEFAULT category will be used. If USE_STORED_OUTLINES is set to some other text string, only outlines in the specified category will be used. Categories seem to me to be fairly useless, although the DBMS_OUTLN package does include a procedure to drop all outlines in a specific category and it also provides the ability to move outlines from one category to another. So you may find it useful to use a category other than DEFAULT, but I haven’t.
I have built myself a few scripts:
- check_use_outlines.sql – This scripts uses oradebug to see if USE_STORED_OUTLINES has been set.
- outlines.sql – This script lists all outlines and shows whether they are enabled and if they have been used.
- outlines2.sql – The same as above but it also joins to the v$sqlarea view to get the sql_id for the statement (if it is currently in the shared pool). Note that this is will only return the exact match on sql_text and that outlines have a “relaxed” sql_text matching so there may be additional statements that use the outline. Note that this script uses a function called outline_signature that you’ll need to create as well.
- find_sql_using_outline.sql – This script lists all statements in the shared pool that are using an outline.
- create_outline.sql – This script uses the DBMS_OUTLINE.CREATE_OUTLINE procedure to create an outline for an existing cursor. It prompts for a sql_id, child_no, and name. Since the create_outline procedure uses hash_value instead of sql_id, this script has to get the hash_value first. Also, since the create_outline procedure doesn’t allow a name to be specified for the outline, this script renames the outline after it is created. Note that this script will not work in 9i because sql_id was not introduced until 10g.
- outline_hints.sql – This script lists the hints associated with a given outline.
- outline_startup_trigger.sql – This scripts creates an after startup database trigger that enables outline usage as per Metalink Note:560331.1.
Here’s the example – it’s rather long but has quite a bit in it.
> !sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 17 16:09:15 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @check_use_outlines
Statement processed.
qolprm sgauso_ [2000F98C, 2000F9B0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
SQL>
SQL> alter system set use_stored_outlines=true;
System altered.
SQL> @check_use_outlines
Statement processed.
qolprm sgauso_ [2000F98C, 2000F9B0) = 00000001 45440007 4C554146 00000054 00000000 00000000 00000000 00000000 00000000
SQL>
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO INVALIDATIONS SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------- ------------------------------------------------------------
37j3r0s8gzkuf 0 1528838287 1 .03 443 0 select count(*) from kso.skew where pk_col < 13
7cbu7dgt0vh6y 0 1528838287 1 .00 226 0 select count(*) from kso.skew where pk_col < 10
SQL> @create_outline
Session altered.
Enter value for sql_id: 7cbu7dgt0vh6y
Enter value for child_number: 0
Enter value for outline_name: skew_10
Outline SKEW_10 created.
PL/SQL procedure successfully completed.
SQL> @outlines
Enter value for category:
Enter value for name:
CATEGORY NAME USED ENABLED HINTS SQL_TEXT
--------------- ------------------------------ ------ -------- ------ ----------------------------------------------------------------------
DEFAULT SKEW_10 UNUSED ENABLED 5 select count(*) from kso.skew where pk_col < 10
SQL> select count(*) from kso.skew where pk_col < 10;
COUNT(*)
----------
9
SQL> @outlines
Enter value for category:
Enter value for name:
CATEGORY NAME USED ENABLED HINTS SQL_TEXT
--------------- ------------------------------ ------ -------- ------ ----------------------------------------------------------------------
DEFAULT SKEW_10 USED DISABLED 5 select count(*) from kso.skew where pk_col < 10
SQL> @outlines2
Enter value for category:
Enter value for name:
CATEGORY NAME USED ENABLED HINTS SQL_ID SQL_TEXT
--------------- ------------------------------ ------ -------- ------ --------------- ------------------------------------------------------------
DEFAULT SKEW_10 USED DISABLED 5 7cbu7dgt0vh6y select count(*) from kso.skew where pk_col < 10
SQL> @find_sql_using_outline
Enter value for sql_text:
Enter value for sql_id:
SQL_ID CHILD EXECS AVG_ETIME OUTLINE_CATEGORY OUTLINE_NAME PLAN_HASH_VALUE SQL_TEXT
------------- ------ ---------- ------------- ---------------- ------------ --------------- -------------------------------------------------------
7cbu7dgt0vh6y 0 1 .03 DEFAULT SKEW_10 1528838287 select count(*) from kso.skew where pk_col < 10
SQL> @outline_hints
Enter value for name: SKEW_10
NAME HINT
------------------------------ ----------------------------------------------------------------------
SKEW_10 INDEX(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."PK_COL"))
SKEW_10 OUTLINE_LEAF(@"SEL$1")
SKEW_10 ALL_ROWS
SKEW_10 OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
SKEW_10 IGNORE_OPTIM_EMBEDDED_HINTS
6 rows selected.
SQL> alter outline skew_10 disable;
Outline altered.
SQL> @outlines
Enter value for category:
Enter value for name:
CATEGORY NAME USED ENABLED HINTS SQL_TEXT
--------------- ------------------------------ ------ -------- ------ ----------------------------------------------------------------------
DEFAULT SKEW_10 USED DISABLED 5 select count(*) from kso.skew where pk_col < 10
SQL> @find_sql
Enter value for sql_text: %skew where pk_col < 10%
Enter value for sql_id:
no rows selected
SQL> drop outline skew_10;
Outline dropped.
SQL> @outlines
Enter value for category:
Enter value for name:
no rows selected
SQL> !cat outline_startup_trigger.sql
-- As recommended in Metalink Note:560331.1
--
-- This is necessary since the setting for use_stored_outlines
-- does not persist across instance bounces.
create or replace trigger enable_outlines
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;
/
show errors
SQL> @outline_startup_trigger.sql
Trigger created.
SQL> show errors
No errors.
And a few last bits:
I think outlines are a very powerful tool and although it is clear the Oracle does not intend to continue enhancing this feature, it still can be pretty handy. I for one am glad to see that, at least for the time being, it will continue to work.
Keep in mind that outlines are not absolutely guaranteed to freeze a plan. Since they are implemented with hints, things can happen that make a hint invalid (dropping an index for example). But they are as close as one can come without changing the optimizer_mode to RULE.
Outlines have not been superseded by SQL Profiles. SQL Profiles are similar to outlines in that they implement hints behind the scenes. SQL Profiles are created by the SQL Tuning Advisor and can be a great help in figuring out why a plan has gone wrong. However, SQL Profiles that are create dby the SQL Tuning Advisor often provide less stability than outlines and they do tend to sour over time. By that I mean that many of the hints are aimed at improving the CBO’s estimates, rather than at freezing plans. They often accomplish this by applying scaling factors at various stages via the OPT_ESTIMATE hint. If for example the SQL Tuning Advisor determines that CBO is underestimating the number of rows that pass a certain filter by an order of magnitude, it will simply multiply that estimate by 10. If the stats later improve and the CBO does a better job in its estimating, the SQL Profile still multiplies that stage’s estimate by 10. By the way, there is nothing wrong with using a SQL Profile to get the plan you want and then creating an outline to lock it in. Keep in mind though that Outlines are not absolutely guaranteed to keep a plan from changing.
Outlines still appear to work in 11gR1. The fact that the dbmsol.sql file, which creates the DBMS_OUTLN package, has been updated for 11gR1 indicates that this is intentional. Adaptive Cursor Sharing and SQL Plan Management will probably make this feature less useful (i.e. some of the reasons we need to use them in 10g should go away), but it’s good to know that they still work.
Note: I do intend to do another post on tricks that can be used to get the plan you want, so that you can lock it down with an outline.
And here’s a few additional references:
Metalink:
Note 445126.1 – How to manually store an outline using V$SQL and DBMS_OUTLN.CREATE_OUTLINE
Note 728647.1 – How to Transfer Stored Outlines from One Database to Another (9i and above)
Note 726802.1 – Editing Stored Outlines in Oracle10g and Oracle11g
Note:730062.1 – How to Edit a Stored Outline to Use the Plan from Another Stored Outline
Here’s a good early article on outlines by Jonathan Lewis.
Tom Kyte’s Book “Expert One-on-One Oracle” has a great chapter on Outlines, although it is quite dated now, going only through 8.1.7. However, since outlines haven’t really been updated significantly since then (with the exception of dbms_outln.create_outline procedure) it is still a very good source of information. Also Randolf Geist has a very good post here on using SQL Profiles to lock plans here.
As always, your comments are welcome.
I have played with outlines to fix execution plan for few problematic SQL statements. Your scripts gives useful information but they contain outline_signature function so I get “SQL Error: ORA-00904: “OUTLINE_SIGNATURE”: invalid identifier”.
Tomas,
Good catch! OUTLINE_SIGNATURE is a function I created and then used in the outlines2.sql script. I had uploaded the file to create the function but failed to reference it in the post. I have edited the post to add a link to the file which contains the create statement for it. Thanks for pointing that out.
Kerry
“By the way, there is nothing wrong with using a profile to get the plan you want and then creating an outline to lock it in” don’work!
When you create outline the plan (fixed by SQL profile)changes.
Roberto
Roberto,
First off, thanks for you comment. Outlines (and profiles too for that matter) are definitely not foolproof. They don’t actually store the plan, just a set of hints that they hope will force the optimizer to choose a certain plan. Seems like it would have been easier just to save the plan itself, rather than to try to come up with a set of hints. But that’s a whole other discussion.
A common technique with outlines is to try to coerce a statement to use the plan you want (obviously without changing it’s text). This can be done in many ways, changing optimizer parameters at session level, changing stats, creating views with embedded hints, etc… Creating a profile is just another way to coerce a plan you want. I must admit that I have not used this trick extensively (only on one production system). But the technic appears valid. By that I mean, that you can create an outline on a statement that has a profile already created on it. And that the optimizer appears to then use the outline. It probably would be wise to disable the profile if you do this by the way. My main point was that outlines (while certainly not perfect) are much more likely to remain constant than profiles, because profiles tend to adjust optimizer calculations by applying scaling factors. So they tend to “sour” – that is, as the statistics change, the scaling factors no longer have the desired affect.
I will say that I have seen outlines that were much less specific than I would have expected. For example, the index hints in 10.2 commonly use the form that specifies the columns instead of the actual index name, like so:
INDEX(@”SEL$1″ “SKEW”@”SEL$1” (“SKEW”.”PK_COL”))
This hint just says use an index on the skew.pk_col column if one exists. It doesn’t specify which index to use. So even with outlines, Oracle sometimes leaves itself some wiggly room. And the more complicated the statement, the more chance there is that this type of issue might crop up.
One final thought, I really like outlines because they allow us to manipulate the system (trick it if you will) when we can’t touch the code. I also like them because they are relatively quick to turn on and off. But, and this is a big but, they should really be considered just a temporary fix until you can get the optimizer doing what it should by having the correct statistics, using literals where necessary, etc…
There are certainly cases where outlines go wrong. I’d be interested in seeing an example of one where you had a profile and created an outline that changed the plan.
Kerry
It is not a reproducible case test, however it is not a fake.
I omitted sql profile and outline creations, and the statement, plans, etc., for customer privacy, they are quite complexes.
select plan_hash_value, sql_id, hash_value, child_number, OUTLINE_CATEGORY, SQL_PROFILE
from v$sql
where sql_id = ‘2g985t2xpm0gf’;
PLAN_HASH_VALUE SQL_ID HASH_VALUE CHILD_NUMBER OUTLINE_CATEGORY SQL_PROFILE
—————- ————— ———– ————- —————- ———–
2269489710 2g985t2xpm0gf 3143205358 0
begin
dbms_sqltune.alter_sql_profile(
name => ‘to_fix_with_outline’,
attribute_name => ‘status’,
value => ‘enabled’
);
end;
/
select plan_hash_value, sql_id, hash_value, child_number, OUTLINE_CATEGORY, SQL_PROFILE
from v$sql
where sql_id = ‘2g985t2xpm0gf’;
PLAN_HASH_VALUE SQL_ID HASH_VALUE CHILD_NUMBER OUTLINE_CATEGORY SQL_PROFILE
—————- ————— ———– ————- —————- ——————–
482806775 2g985t2xpm0gf 3143205358 0 to_fix_with_outline
alter session set use_stored_outlines = true;
select plan_hash_value, sql_id, hash_value, child_number, OUTLINE_CATEGORY, SQL_PROFILE
from v$sql
where sql_id = ‘2g985t2xpm0gf’;
PLAN_HASH_VALUE SQL_ID HASH_VALUE CHILD_NUMBER OUTLINE_CATEGORY SQL_PROFILE
—————- ————— ———– ————- —————- ——————–
482806775 2g985t2xpm0gf 3143205358 0 to_fix_with_outline
2269489710 2g985t2xpm0gf 3143205358 1 DEFAULT
Since I verified some times the method don’t work, I believed it was a wrong method generally.
But I tried with a artificial case test and it works actually, hence is a possible method.
Can you suggest me any clue because the plan changes?
Best regards,
Roberto
Roberto,
Sorry for the delay in replying (I’ve been a bit swamped lately). It’s true that outlines don’t always work. The idea is for Oracle to come up with a set of hints that keep the optimizer from doing anything except the current plan. Unfortunately, occasionally there is still room for the optimizer to pick more than one plan. A good example is the new form of the index hint that specifies the table and the column that you want to use an index on (as I mentioned in my previous comment). This hint does not force the use of a single index, it will allow the optimizer to pick any index that contains that combination. So if you have the same column in more than one index, you can get a different plan. Or if you add a new index, the outline might actually pick it up. This is more likely in later releases and obviously in more complicated SQL statements. So as always, testing is important to make sure it’s doing what you want. I do continue to believe that outlines are more stable than SQL profiles. Of course the best way to deal with the issue is to give the optimizer the info it needs using literals where histograms are needed, so we’re really talking about situations where we can’t directly affect the code.
On a side note, I have created a few outlines in an 11g production environment now, and for simple statements they seem to behave themselves. I am currently experimenting with baselines, as I believe that is a much better approach (actually capturing and saving the execution plan). I hope to write something up on that shortly.
Kerry
[…] forcing the optimizer’s hand such as Outlines (see my previous posts on Unstable Plans and on Outlines). This option provides a quick method of locking in a single plan, but it’s not fool proof. […]
Kerry,
I’ve recently published a blog post where I compare Stored Outlines and SQL Profiles in 10g when attempting to lock down execution plans having the “good” one already in the shared pool (or e.g. AWR, see blog). The method described in my blog post using SQL Profiles offers some additional functionality/flexibility you don’t have with Stored Outlines, so it might be worth a try.
In passing I’ve noticed an odd behaviour of the DBMS_OUTLN.CREATE_OUTLINE procedure in my 10.2.0.4 test runs (apart from the bug in the previous releases) since it didn’t pick up the hints stored along with the plan in the shared pool, but obviously created an outline using the present statistics, which might be irritating.
For more details, see the blog post.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Excellent post. I had not run across it. I had been thinking about writing something to pull a plan from the awr tables but hadn’t gotten around to it yet. So that will save me some work!
I do agree with you that SQL Profiles have the advantage of being able to force matching of statements using literals. Outlines, SQL Profiles, and now Baselines all can provide similar functionality, each with their own little twist. I have primarily used SQL Profiles in conjunction with the SQL Tuning Advisor where by SQL Profiles are created based on it’s analysis. These SQL Profiles often make use of the OPT_ESTIMATE hint to scale an estimate for a particular step one direction or the other. These are the hints that make SQL Profiles less attractive to my way of thinking, because these scaling factors stay fixed although the statistics and data in the object can and do change. So a scaling factor that made sense last month may not make any sense this month. That’s why I said that they tend to sour over time. Again, I believe this is specific to using the SQL Tuning Advisor and the SQL Profiles that it generates, so I should probably re-word my post to be a little more clear on that issue.
I must say, that trying to come up with a set of hints that will only allow the optimizer to choose one plan seems like an approach that is doomed to failure (at least some of the time). That said, the approach has worked pretty well for quite a while, at least on relatively simple queries where the optimizer doesn’t have that many choices to begin with. And it is really the only option prior to 11g. I am working on a post on Baselines now, as I have been able to work with a production 11g system for a while now where we are using them. It will be interesting to see how the hint based mechanism has matured with 11g.
Thanks for your comments and the heads up on your post.
Kerry
[…] give credit to Randolf Geist for making me take a second look at SQL Profiles. He commented on my Outlines post last week and recommended I give his post on SQL Profiles a look. I did and it really got me […]
[…] latest evolution in Oracle’s efforts to allow a plan to be locked. The previous efforts were Outlines and SQL Profiles. Both of those efforts were based on the idea that hints could be applied to limit […]
[…] 关于stored outline还有以下相关资料可以对照阅读下: Oracle Outlines – aka Plan Stability By Kerry Osborne Plan stability in 10g – using existing cursors to create Stored Outlines and SQL […]
[…] Kerry Osborne blog explained more detail about outline http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/ […]
[…] members): http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/ http://www.oracle-base.com/articles/misc/Outlines.php […]
Great post, thank you Kerry.
I am working on creation of stored outlines to resolve an issue.
Indeed, they work. Query time decreased from 1.45 hours to 16 minutes.
A few questions.
1) I have a batch which contains a large package. Is it possible to create an outline for the entire package as in “create outline package for exec njuser.package”?
Or do we have to go over specific select statements to create outlines for each executable SQL separately? The reason I am saking is that these select statements use bind variables and we dont know ahead of time what literal statements they will use. So, I cannot create a SO with literals.
2) Is it possible to run a statement to execute the package and start another session, get the first session’s hash id and create an outline for everything the first session runs? It looks like this is NOT possible in 9i. Only a precanned statement can be used in an outline with literal values.
SQL> CREATE OUTLINE myoutline FOR CATEGORY temp ON select * from dual;
Outline created.
But passing a session’s hash fails in 9i:
SQL> EXEC DBMS_OUTLN.CREATE_OUTLINE(‘4282642546′,0,’GRIGORIANV_TEST_BATCH’);
BEGIN DBMS_OUTLN.CREATE_OUTLINE(‘4282642546′,0,’GRIGORIANV_TEST_BATCH’); END;
*
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component ‘CREATE_OUTLINE’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Did I misunderstood and there is another procedure in 9i, or is DBMS_OUTLN.CREATE_OUTLINE only available in 10g and later?
Thanks.
Vlad.
I don’t have a 9i DB available at the moment to check but I’m pretty sure the CREATE_OUTLINE procedure didn’t exist until 10g. Prior to that you had to create an outline with the syntax as you showed.
Outlines, SQL Profiles, Baselines and Patches all are used to apply hints to individual SQL statements, not PL/SQL blocks.
Hi
Kindly share the complete script — > @find_sql
i am getting below error out..??
SQL> @create_outline.sql
Session altered.
Enter value for sql_id: 89udvcm48zrj9
Enter value for child_number: 0
Enter value for outline_name: new_outline
old 8: name2 := ‘&&o_name’;
new 8: name2 := ‘new_outline’;
old 11: where sql_id like ‘&&sql_id’;
new 11: where sql_id like ’89udvcm48zrj9′;
old 16: child_number => &&child_number);
new 16: child_number => 0);
old 18: select ‘alter outline ‘||name||’ rename to ‘||’&&o_name’, name into sql_string, name1
new 18: select ‘alter outline ‘||name||’ rename to ‘||’new_outline’, name into sql_string, name1
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9
Are you sure you have the correct sql_id and child number?
find_sql.sql