I seem to have spent a lot of time during the last week having conversations about why Oracle is not locking plans, even when an Outline or SQL Profile has been created. I mean, their whole purpose in life is to keep the optimizer from changing plans, right? Here’s a bit of an email I sent with my thoughts on the issue during a conversation on the Oracle-L list.

First, I think you need to convince yourself as to whether the profile is being used or not. There is a column in v$sql (sql_profile) which will have the name of the profile if one is being used. Also xplan will show that a profile is being used at the bottom of the output.

If the profile is being used, but the plan is not what you expected, there are a couple of likely culprits.

First, if it’s a profile generated by SQL Tuning Advisor it’s quite possible that it will have one or more OPT_ESTIMATE hints – which apply scaling factors to various operations. These types of profiles can and do switch plans fairly easily, because they are not even attempting to lock anything. As the stats change, the plans can change.

The second common possibility is that Oracle decided to use an index hint that doesn’t specify the index name, but rather the columns that it would like to use an index on. For example:

This hint does not specify an index, but rather columns to use an index on. I’m not sure when this form became a preferred approach but it certainly seems to show up pretty often. So that leaves the optimizer with the flexibility to pick an index based on stats. In systems with lots of indexes, this makes it more likely that a statement will switch plans even though it’s using an Outline or Profile. In fact, you may have a statement with multiple plans in the shared pool, pick one to create an Outline or Profile with, enable it, and have the new child use a plan that’s different from the plan of the child you used to create it with. Very frustrating.

So as I said in the email, the most likely cause for SQL Profiles not working is the non-specific form of index hints that are being used. However, as I was doing some research for this post, I found a couple situations where SQL Profiles just flat don’t work as advertised. I’ll show you an example, but first here are a few scripts that I will use (I sometimes don’t set “echo on” because it makes the examples so messy – so just hit the links to view the scripts):

Updated: 6/14/13
Note that I have rewritten the create_sql_profile.sql script so that it is not dependent on the rg_sqlprof1 script and to add additional functionality. Please use the newer one. Also I have rewritten the sql_profile_hints.sql script so that it works with 10g and 11g. I have disabled the links to the old versions in the list below.

sql_hints.sql – shows the hints stored in the v$sql_plan.other_xml columnsql_profile_hints.sql – shows the hints associated with a SQL Profile
sql_profile_hints11.sql – shows the hints associated with a SQL Profile (for 11g)
create_sql_profile.sql – creates a profile for a statement in the shared pool (wrapper for rg_sqlprof1.sql)
rg_sqlprof1.sql – does the real work to create a profile for a statement in the shared poolfix_sql_profile_hint.sql – replaces a hint in a profile with whatever you want

Here’s the set up: a simple single table query that wants to do a full table scan and a second version that uses a hint to force an index. A profile created on the hinted statement should keep it from changing, but instead, it changes the plan back to a full table scan. Here’s the example:

So let’s see if we can work around the problem. To do that, all we need to do is replace the bad index hint (or hints) with one that will work. In this case we’ll probably need to avoid the INDEX_RS_ASC hint because it probably won’t work since we’re not specifying the leading part of the index. Here’s an example:

This particular example behaved consistently from 10.2.0.3 through 11.1.0.7 (that is to say, it doesn’t work in any of those versions, but it behaves in a consistent manner).

This particular example behaved the same for Outlines as it did for SQL Profiles.

I believe that the best replacement for the INDEX_XXX (table_name.column_name) hint is the simple INDEX(alias index_name) hint.

The syntax of these hints is very particular, so the best approach in my opinion is to keep all the query block and alias bits and simply replace the (table_name.column_name) bit with the index_name.

Remember that hints don’t give any error messages, they are just silently ignored if something isn’t right (that’s always been a bit frustrating, seems like there ought to be a switch that that could be set which would allow an error to be thrown if a hint has invalid syntax).

Outlines, SQL Profiles, Baselines (and Hints themselves for that matter) are a bit flakey (partly because they are not well understood due to the small amount of documentation, and partly because of the lack of error messages when hints are poorly formed). So you should always verify what they are doing by looking at the hints that are being applied. 10053 traces can help – there is a section at the very bottom of the trace file where it indicates if any hints have generated errors. This included hints applied via Outlines or Profiles.

It’s clear that a syntax error on one of the hints in a Profile will not invalidate the whole Profile. The optimizer will attempt to honor any hints that are valid. (if you’re interested, you can play with the fix_sql_profile_hint.sql script to verify this for yourself)

All these options are extreme measures that allow manipulation of execution plans when you can’t touch the actual SQL statement. While they can provide very dramatic results in a very short time frame, generally speaking, I do not consider them to be the best long term solution.

43 Comments

I like the idea of an “enhancement” to the Oracle code base so that it could somehow be determined if a hint is syntactically correct. What are the chances Oracle would act on such an enhancement request, in your opinion? On your last point – it has always been frustrating to me, especially with the amount of time it takes to devise a set of hints, or outlines or profiles, that the results are not gauranteed long term (of course, nothing is). Perhaps, pretty soon, when Oracle owns the major code base for every third party app, this will no longer be an issue (ha ha)!

Another reason for ignored profiles/outlines, etc. that I found recently which I had never seen (or at least never knew I had seen) was a differing bind type. Our java developers used a java.util.Date() object rather than a java.sql.Date(). The database took it as a timestamp, and wouldn’t use the index on the DATE column (whose datatype was DATE).

As soon as the changed their code, we didn’t need the profile anymore. Go figure…

That’s a classic. I had that exact situation about a year ago. 1.5 billion row table, all queries by date range, using java date variable that caused Oracle not to be able to use the index. Actually it was more tricky than that. Because the index had multiple columns, the plan actually used the index, but only on the much less selective other columns. So the index showed up in the plan, but didn’t work very well. They hadn’t noticed that the date was being used as a filter as opposed to an access method.

I’d be interested to hear more details on the steps you went through to create the Outline (or Profile) and then how it behaved. Any details you can share?

[…] often necessary to fix one or more of the hints (see this post for the most common reason: Why Isn’t Oracle Using My Outline / Profile / Baseline?). The preceding steps allow the Profile to be tweaked before attaching it to the target statement. […]

[…] on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to […]

a) A profile and an outline are different things – roughly speaking an outline lists actions and strategies, a profile supplies statistical corrections to the optimizer.

b) The code you’ve used to “create a profile from the shared pool” doesn’t, it copies an outline from the shared pool but tells the optimizer it’s a profile.

c) When you run (or explain) the query with the “profile” in place, the optimizer detects the profile and disables the embedded hints (i.e. the ones in the actual text) and tries to derive an execution plan based on what ought to be statistical content in the profile combined with the basic object stats. But the profile supplies no statistical information.

d) In the absence of statistical information, and after disabling the embedded hints, the optimizer has “used” the profile but produces the original, unhinted, tablescan plan.

Wow – I had to think about this one for a while (and go back to check my work) – here’s my thoughts:

a) A profile and an outline are different things – roughly speaking an outline lists actions and strategies, a profile supplies statistical corrections to the optimizer.

I see Outlines and Profiles (and Baselines for that matter) as basically the same thing. They apply a set of hints to a statement. The standard (documented) way of generating a SQL Profile is to use the the SQL Tuning Advisor – which does calculate scaling factors to apply to various steps and implements them via the OPT_ESTIMATE, TABLE_STATS, COLUMN_STATS hints (and occasionally it adds some strategic type hints…). But Profiles are nevertheless just applying hints (I think).

b) The code you’ve used to “create a profile from the shared pool” doesn’t, it copies an outline from the shared pool but tells the optimizer it’s a profile.

That comment made me laugh – you’re absolutely right of course! But just to be clear to anyone else that might stumble across this dialog, every plan has a set of hints that Oracle thinks will recreate the plan (stored in the other_xml column of v$sql_plan). These hints are the exact hints that are used when you create an Outline. The code in my create_sql_profile.sql script does exactly what you said, it creates a SQL Profile using that same set of hints that would be in the Outline if you created one for the statement.

c) When you run (or explain) the query with the “profile” in place, the optimizer detects the profile and disables the embedded hints (i.e. the ones in the actual text) and tries to derive an execution plan based on what ought to be statistical content in the profile combined with the basic object stats. But the profile supplies no statistical information.

I believe SQL Profiles apply the hints. I don’t think they expect any specific statistical information (i.e. I don’t think they care whether there are any OPT_ESTIMATE, TABLE_STATS, or COLUMN_STATS hints or not) I could be wrong, but I haven’t seen any situation where a Profile was ignored because it didn’t have statistical information.

As a side note, I don’t think the SQL Profiles that are created by the SQL Tuning Advisor disable embedded hints the way Outlines do. I say this because Outline hints include the IGNORE_OPTIM_EMBEDDED_HINTS hint and SQL Profiles created by the Tuning Advisor do not. And if you think about it, that makes sense. Since the latter are basically only supplying statistical information, disabling embedded hints would not make sense. (Note: I have not done a specific test to prove this to myself)

d) In the absence of statistical information, and after disabling the embedded hints, the optimizer has “used” the profile but produces the original, unhinted, tablescan plan.

I came up with a little test case. See if you think this is a valid test.

1. create a SQL Profile (based on hints from other_xml)
2. check hints – there will be no statistical hints
3. manually modify one of the hints in a way that forces a different plan
4. see if it works

If the plan changes as expected, despite the absence of any statistical hints, then that implies to me that Profiles just apply whatever hints are there. Thoughts?

a) Outlines vs. profiles: I think these can be viewed on two levels. On one hand they are both represented by a set of hints; on the other hand they exist for different reasons. In principle an outline fixes an execution plan so that it can’t change whereas a profile fixes the optimizer’s understanding of the data distribution pattern so that it can still pick the right plan as the data size varies.

(Other than optimizer_features_enable() and ignore_optim_embedded_hints, I’ve not seen anything but statistical hints in a profile generated by the tuning tool – do you have any examples to demonstrate otherwise ?).

b) One aspect of the difference between outlines and profiles is that the profiling information doesn’t get into the outline when the statement runs. A statement with a profile can produce many different execution plans – which means many different outlines: which, I’d say, is an argument against copying an outline into the profile table.

c) disabling embedded hints – I think I was wrong, but I’m not 100% sure. I’ve just run up an example in 11.1.0.6 to demonstrate the point – and realised that the profile included the ‘optim_ignore_embedded_hints’ hint, so perhaps there are cases where this isn’t included and embedded hints are still obeyed. (In fact, since you were copying from the outline, you automatically copied in the optim_ignore_embedded_hints hint anyway – and then the index_rs_asc() hint was ignored because it is invalid in this context (as you suggested), and shouldn’t have been genereated by the optimizer).

d) That’s a valid test that the code for applying profiles doesn’t care what type of hint you’ve put in the table – and it certainly seemed to be true for a couple of examples I’ve just created. It certainly makes sense – especially in view of the fact that sql-baselines are written into the same table in 11g (Why have two different pieces of code when one will do ?)

a) I agree that Profiles appear to have been built with statistical modifications in mind. It looks to me like they “borrowed” the code from Outlines though. Adding the ability to do the “force matching” thing that let’s them apply to multiple statements that only differ due to literals. My experience with the Profiles generated by SQL Tuning Advisor has not been very good in terms of plan stability though. It does not appear that they have any concept of changes to stats and just apply their scaling to what ever the optimizer comes up with. So if (I should say when) the stats change, the scaling factors may not be correct anymore. I can see them working well for situations where the stats gathering is managed tightly and the optimizer just has no way to deal with a particular situation (like correlated columns or example), but for the general case, they seem to “sour” over time due to changing stats.

Yes, I have seen other hints. We have a client that has created many Profiles with the Tuning Advisor (they are in the process of getting rid of them by the way, due to this tendency to “sour” over time). But back to question about additional hints, I just did a quick review of about 40 Profiles and the vast majority have only statistical hints (OPT_ESTIMATE, COLUMN_STATS, TABLE_STATS, INDEX_STATS). A few have some variation of the (ALL_ROWS, FIRST_ROWS(X)) hint and most have the OPTIMIZER_FEATURES_ENABLE hint as well. To my surprise, I found a couple that had IGNORE_OPTIM_EMBEDDED_HINTS as you were expecting. Even more surprising was that one of the statements had no embedded hints while the other did. (something else to look at I guess) I did find one that had a whole slew of hints. Again, I’m not sure why occasionally we get ones that look like this, but at any rate here’s the list of hints:

b) Agreed. I am using Profiles in a way that the designers may not have intended. The argument for using Profiles in this manner would be that Profiles can match multiple statements that differ only by literals, while Outlines can’t. Profiles are a little cleaner as well. No need for a database trigger to enable them for example. Also, I have to admit that the documentation saying Outlines have been deprecated is starting to make me nervous about using them.

c) Looks like sometimes Profiles include IGNORE_OPTIM_EMBEDDED_HINTS and sometimes they don’t. My first thought was maybe the ignore hint was included only when there are embedded hints and it’s smart enough to leave the hint out when there aren’t any. But I found one that didn’t have any embedded hints that still had the ignore hint, so at this point it is unclear to me as to why sometimes it is there and not other times …

d) Yep, my tests showed that the plan can be changed by changing the hints as well, regardless of whether there are any statistical hints there or not. And 11g baselines look to me like enhanced Profiles. Although the Baselines have a plan_hash_value – so they can determine if they got the plan that generated the Baseline to begin with – a giant step forward in my opinion – although I wish they had just saved the plan itself and been done with it. It means they can throw out the whole plan and re-optimize (or pick another baseline), instead of just ignoring a single hint.

On the bug issue, if I understand you correctly you’re saying that creating an Outline also results in the buggy behavior (that being that the plan changes due to the invalid hint). I agree with that observation. Creating an Outline or a Profile in 10g or 11gR1 will change the plan due to this bug. Creating a Baseline in 11gR1 does not change the plan (even though the hint is still messed up) because Baselines are smart enough to know if the plan generated by the hints matches the plan that was originally used to generate the hints. And so, it throws the whole thing out and re-optimizes the statement from scratch.

[…] regarding SQL Profiles (in a very nice collegial sort of way). You can see the original dialog here. One of his main points was that SQL Profiles were not meant to be a generic mechanism for forcing […]

Kerry,
Is it me or did the explain plain for the index-hinted query appear to have MUCH higher cost in terms of rows processed and CPU? Perhaps this is the reason the optimizer chose to ignore the profile.

Once a Profile has been created, its hints will be applied (as long as they are valid, and they don’t contradict each other, etc…). Since this was a simple Profile with a single directive (INDEX_RS_ASC), when that hint was found to be invalid, it went back to the Full Table Scan. The reason the hint was invalid is because an index range scan cannot be done on an index where the leading columns are not part of the predicate. A skip scan or a full index scan are possible, but not a normal range scan. And since the IGNORE_OPTIM_EMBEDDED_HINTS hint is also included in the Profile, the inline hint in the text is ignored. So basically it’s back to picking a plan without any directive hints. At least that’s what I believe is happening.

with, sql_hints.sql , have you ever seen this:
extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 2:
ORA-00904: \D\: invalid identifier
?
Maybe I’m missing something obvious as I get a similar error with the COE script you posted recently.

I have been using SQL hints on sql profiles with good degree of success. Recently, i am facing an issue where after applying the SQL hint (/*+INDEX(Table_ALias Index_Name)*/)to an SQL profile, the plan changes for certain child numbers only. For example, a sql_id had child numbers 0, 2, 4, 6 and dbms_xplan indicated that the child 2 has the hinted plan (good) whereas the others don’t. This is on Oracle 11.1.0.7.

I there a way to apply a sql profile to different child numbers of the same sql_id? Would appreciate if u can explore further?

SQL Profiles can apply to multiple SQL statements via the FORCE_MATCHING parameter, but there is no finer grained way of applying them than to a single SQL_ID. So no, there is no syntax for applying them to individual children. That would not help you anyway because as soon as you create a SQL Profile, the existing cursors will be invalidated and a new cursor will be created at the next execution. As to why you got the plan you wanted on one child and not others, I am not sure how you created your SQL Profile, maybe with my create_1_hint_sql_profile.sql script? At any rate, the syntax you are using “index (Table_Alias Index_Name)” would need to be modified to include a query block in order for it to work inside a SQL profile like this:

index(Table_Alias@Query_Block Index_Name)

The default query block name is SEL$1 for a simple query (you can see them by doing an XPLAN with ‘alias’)

I have a query that is doing a full table scan. I ran the tuning optimizer task, and it recommended an SQL profile which I accepted, that promised 90%+ improvement. When comparing the plans, it proposed using an index. Good, so far!

Well, as in your case, when running the query, it still did the full table scan even though it said it used the profile.

The index hint for the profile used INDEX_RS_ASC. I believe that is the culprit.

When I run my query specifically with the /*+ INDEX (table_name my_index) */ hint, then the index is used.

If I run it with /*+ INDEX_RS_ASC (table_name my_index) */ then a full table scan is done.

Is there a way, without using your scripts to change the profile hint, for me to get Oracle to do use the INDEX hint rather than an INDEX_RS_ASC hint?

When trying to understand why a hint based mechanism is not doing what you expect, it’s best to look directly at the hints. You say you implemented a SQL profile suggested by the Tuning Advisor. Generally those hints look like OPT_ESTIMATE or statistics modification type hints. Have you run the sql_profile_hints.sql script to see exactly what’s being applied? If so can you paste the output here?

Also – I used fix_sql_profile_hint.sql to change it to INDEX(@SEL$1 NAMES@SEL$1 UPPER_NAME_INDEX), and now I get the promised 90% improvement! However, it feels like I’m cheating (read “Oracle doesn’t support/recommend this”).
I’m hoping maybe I’m missing a setting or something that would cause the hint to be INDEX instead of INDEX_RS_ASC.

This looks like a profile created manually (by my create_sql_profile.sql script for example). At any rate, I don’t see any problem with what you’ve done. I recently found a reference on MOS to using dbms_sqltune.import_sql_profile to create manual profiles. So I think it is not something to worry about. See this post on that topic:

Yes, the profile was created manually. I’m not sure why INDEX works, but INDEX_RS_ASC doesn’t. But, I can’t argue with my query that used to take 63 seconds, now takes 9 seconds. And, it sounds like Oracle is fine with manually created profiles, so I’m good to go. Thanks for the great info!

Kerry,
In what scenarios are force_matching_signature=0? I created a sql tuning set of 30 statements to load into the SQL Management Base (dba_sql_plan_baselines) But, notice that not all of them get loaded into this table from the sql tuning set. I see the ones that had a value of 0 for the force_matching_signature column in the sql tuning set are the ones that didnt make it into the dba_sql_plan_baselines. Can you explain why these didnt make it to the dba_sql_plan_baselines?

[…] few statements they were not picked up on subsequent executions. So I used the process described here (specifically the create_sql_profile.sql script) to manually create a SQL Profile from these hints. […]

Thank you for your article…it is quite interesting and is helping me to learn alot. I realize this was posted a few years back and using 11.1.0.7, but we are seeing similar behaviour in our database and we are at 11.2.0.3. Was hoping you could shed some light…basically the problems is that the profile will work for a few days and the statement completes within seconds, then for some reason (we have an Oracle SR logged and possible bug fix coming) the optimizer will choose a poor performing plan and take hours to complete.

The statement is a MERGE and the sql_profile_hints11.sql output is below. Do you see anything in our output that points to the same problem you were seeing in 11.1.0.7? I’m a bit new to digging into the optimizer hints and am wondering if the presence of the INDEX_RS_xxxx hint is in itself and indication of a problem, especially if there is not a specific index listed. Also, I noticed with this query, that all of the INDEX hints specify column names rather than the actual index name… is this a new default for the 11.2.0.3 optimizer?

SQL > @sql_profile_hints11
Enter value for name: PROFILE_g2d5h97yjspgs
old 11: and p.name like nvl(‘&name’,name)
new 11: and p.name like nvl(‘PROFILE_g2d5h97yjspgs’,name)

The only way I’ve been able to reproduce that error is to not have the d at the end of the statement. Can you set echo on and run the script and post the output here so I can see what the statement looks like?

Also, I have since rewritten that script so as not to depend on the rg_sqlprof1.sql script. Here’s a link to the new version of the create_sql_profile.sql script.

Sorry for the delay in replying to your comment. As you know, SQL Profiles are just a collection of hints that “try” to influence the optimizer enough to limit it to a single plan. In complex queries, the set of hints may not be complete enough to limit the choices to a single plan, Also something in the environment could change that makes a hint invalid (dropped index for example). The new column based INDEX hint syntax also allows more flexibility for the optimizer to pick different plans. You could try using my pif.sql statement to transform the INDEX hints into the more specific form which uses the index name to get a more restrictive SQL Profile. See this post for more info: Fixing Bad Index Hints in SQL Profiles (automatically)

I can’t seem to reproduce that error. I do not have access to a 10.2.0.1 database but am suspecting there is an issue with the xml syntax on that version (although I tested it on 10.1 at one point). Anyway, you might try changing up the query to a different syntax, even using substring or something. You know where the data is now. :)

Thanks Kerry, i understand you do not have access to 10.2.0.1 But here is the one that Jonathan Lewis helped me with….This is for other people who might need it, as unfortunately quite a few ppl still on the old version….

we noticed same issue..created sql profile from sql tuning advisor but after creating, query using it but plan still old one without change.

so what about to take good plan which generated via index and put in sql plan baseline instead of working with sql profile.
like “Worked Example of Replacing the Current Existing Plan with a Different Plan Using a SPM SQL Plan Baseline (Doc ID 1580762.1)”