SQL Profiles
Well I was wrong! SQL Profiles are better than Outlines. For a while now I have been saying that I didn’t like SQL Profiles because they were less stable than Outlines. Turns out that the OPT_ESTIMATE hint used by SQL Profiles which are created by the SQL Tuning Advisor is what I really didn’t like. I just didn’t know it.
Let me back up for a minute. I posted about Oracle’s SQL Tuning Advisor a while back. It’s a feature that was added to Oracle in version 10g. It basically looks a SQL statement and tries to come up with a better execution plan than the one the optimizer has picked. Since it is allowed as much time as it wants to do it’s analysis, the advisor can sometimes find better approaches. That’s because it can actually validate the optimizer’s original estimates by running various steps in a given plan and comparing the actual results to the estimates. When it’s all done, if it has found a better plan, it offers to implement that new plan via a SQL Profile. Those offered Profiles often have a lightly documented hint (OPT_ESTIMATE) that allows it to scale the optimizer estimates for various operations – essentially it’s a fudge factor. The problem with this hint is that, far from locking a plan in place, it is locking an empirically derived fudge factor in place. This still leaves the optimizer with a lot of flexibility when it comes to choosing a plan. It also sets up a commonly occurring situation where the fudge factors stop making sense as the statistics change. Thus the observation that SQL Profiles tend to sour over time.
I’ve been saying for some time that I don’t like buying generic Viagra online. Since it is very convenient and effective.
I have to 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 thinking. One of the things I liked the best about the post was that he created a couple of scripts to pull the existing hints from a statement in the shared pool or the AWR tables , and create a SQL Profile from those hints using the DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure. This makes perfect sense because the hints are stored with every plan (that’s what DBMS_XPLAN uses to spit them out if you ask for them). Unfortunately this procedure is only lightly documented. Also he had a nice script for pulling the hints from V$SQL_PLAN table which I have made use of as well.
So as always I have created a few scripts (borrowing from Randolf mostly).
create_sql_profile.sql – uses cursor from the shared pool
create_sql_profile_awr.sql – uses AWR tables
sql_profile_hints.sql – shows the hints in a SQL Profile for 10g
So here’s little example:
Note: unstable_plans.sql and awr_plan_stats.sql are discussed here: Unstable Plans (Plan Instability)
SQL> @unstable_plans
Enter value for min_stddev:
Enter value for min_etime:
SQL_ID SUM(EXECS) MIN_ETIME MAX_ETIME NORM_STDDEV
------------- ---------- ----------- ----------- -------------
0qa98gcnnza7h 4 42.08 208.80 2.8016
SSQL> @awr_plan_stats
Enter value for sql_id: 0qa98gcnnza7h
SQL_ID PLAN_HASH_VALUE EXECS ETIME AVG_ETIME AVG_LIO
------------- --------------- ------------ -------------- ------------ --------------
0qa98gcnnza7h 568322376 3 126.2 42.079 124,329.7
0qa98gcnnza7h 3723858078 1 208.8 208.796 28,901,466.0
SQL> @create_sql_profile_awr
Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value: 568322376
Enter value for category:
Enter value for force_matching:
PL/SQL procedure successfully completed.
SQL> @sql_profiles
Enter value for sql_text:
Enter value for name: PROFIL%
NAME CATEGORY STATUS SQL_TEXT FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_0qa98gcnnza7h DEFAULT ENABLED select avg(pk_col) from kso.skew NO
SQL> set echo on
SQL> @sql_profile_hints
SQL> set lines 155
SQL> col hint for a150
SQL> select attr_val hint
2 from dba_sql_profiles p, sqlprof$attr h
3 where p.signature = h.signature
4 and name like ('&profile_name')
5 order by attr#
6 /
Enter value for profile_name: PROFILE_0qa98gcnnza7h
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
SQL> @sql_hints_awr
SQL> select
2 extractvalue(value(d), '/hint') as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 dba_hist_sql_plan
10 where
11 sql_id = '&sql_id'
12 and plan_hash_value = &plan_hash_value
13 and other_xml is not null
14 )
15 ) d;
Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value: 568322376
OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
A couple of additional points:
- Outlines and SQL Profiles both take the same approach to controlling execution plans. They both attempt to force the optimizer down a certain path by applying hints behind the scenes. This is in my opinion an almost impossible task. The more complex the statement, the more difficult the task becomes. The newest kid on the block in this area (in 11g) is called a baseline and while it doesn’t abandon the hinting approach altogether, it does at least store the plan_hash_value – so it can tell if it regenerated the correct plan or not.
- It does not appear that Outlines are being actively pursued by Oracle development anymore. So while they still work in 11g, they are becoming a little less reliable (and they were a bit quirky to begin with).
- SQL Profiles have the ability to replace literals with bind variables similar to the cursor_sharing parameter. This means you can have a SQL Profile that will match multiple statements which use literals without having to set cursor_sharing for the whole instance.
- Outlines take precedence over SQL Profiles. You can create both on the same statement and if you do, the outline will be used and the SQL Profile will be ignored. This is true in 11g as well, by the way.
- Outlines don’t appear to use the OPT_ESTIMATE hint. So I believe it is still a valid approach to accept a SQL Profile as offered by the SQL Tuning Advisor and then create an Outline on top of it. It seems to work pretty well most of the time. (be sure and check the hints and the plan that gets produced)
- Manually created SQL Profiles also don’t appear to use the OPT_ESTIMATE hint. So I believe it is also a valid approach to accept a SQL Profile as offered by the SQL Tuning Advisor and then create a SQL Profile on top of it. Note that you’ll have to use a different category, then drop the original, then enable the new SQL Profile. Which means this approach is a bit more work than just creating an Outline in the DEFAULT category.
Have a look at the difference between SQL Tuning Set generated hints and those created by a manual SQL Profile or an Outline (note that I have tried to change the object names to protect the innocent and in so doing may have made it slightly more difficult to follow) :
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no',''));
Enter value for sql_id: fknfhx8wth51q
Enter value for child_no: 1
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fknfhx8wth51q, child number 1
-------------------------------------
SELECT /* test4 */ col1, col2, col3 ...
Plan hash value: 3163842146
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1778 (100)| |
| 1 | NESTED LOOPS | | 1039 | 96627 | 1778 (1)| 00:00:33 |
| 2 | NESTED LOOPS | | 916 | 57708 | 1778 (1)| 00:00:33 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TABLE_XXXX_LOOKUP | 446 | 17840 | 891 (1)| 00:00:17 |
|* 4 | INDEX RANGE SCAN | INDEX_XXXX_IS_CPCI | 12028 | | 18 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TABLE_XXXX_IDENT | 2 | 46 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | INDEX_XXXXIP_17_FK | 2 | | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_TABLE_XXXX_ASSIGNMENT | 1 | 30 | 0 (0)| |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((
...
4 - access("L"."COL1"=:N1)
6 - access("L"."COL2"="I"."COL1")
Note
-----
- SQL profile SYS_SQLPROF_012061f471d50001 used for this statement
85 rows selected.
SQL> @sql_profile_hints
Enter value for name: SYS_SQLPROF_012061f471d50001
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=0.0536172171)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=4)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL1", scale, length=6 distinct=1234 nulls=0 min=1000000014 max=1026369632)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL2", scale, length=12 distinct=2 nulls=0)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)
7 rows selected.
SQL> -- no direct hints - only stats and scaling on the profile created by the SQL Tuning Advisor
SQL> -- (i.e. the dreaded OPT_ESTIMATE hints and no directive type hints like INDEX or USE_NL)
SQL>
SQL> -- now let's try an outline on top of it
SQL> @create_outline
Session altered.
Enter value for sql_id: fknfhx8wth51q
Enter value for child_number: 1
Enter value for outline_name: KSOTEST1
Outline KSOTEST1 created.
PL/SQL procedure successfully completed.
SQL> @outline_hints
Enter value for name: KSOTEST1
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
USE_NL(@"SEL$1" "A"@"SEL$1")
USE_NL(@"SEL$1" "I"@"SEL$1")
LEADING(@"SEL$1" "L"@"SEL$1" "I"@"SEL$1" "A"@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" ("TABLE_XXXX_ASSIGNMENT"."COL1" "TABLE_XXXX_ASSIGNMENT"."COL2" "TABLE_XXXX_ASSIGNMENT"."COL3"))
INDEX_RS_ASC(@"SEL$1" "I"@"SEL$1" ("TABLE_XXXX_IDENT"."COL1"))
INDEX_RS_ASC(@"SEL$1" "L"@"SEL$1" ("TABLE_XXXX_LOOKUP"."COL1" "TABLE_XXXX_LOOKUP"."COL2"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
11 rows selected.
SQL> -- no OPT_ESTIMATE hints on the outline
SQL> -- directive type hints - INDEX, USE_NL, etc...
SQL>
SQL> -- now let's try creating a manual profile
SQL> @create_sql_profile.sql
Enter value for sql_id: fknfhx8wth51q
Enter value for child_no: 1
Enter value for category: TEST
Enter value for force_matching:
PL/SQL procedure successfully completed.
SQL> @sql_profile_hints
Enter value for name: PROFILE_fknfhx8wth51q
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "L"@"SEL$1" ("TABLE_XXXX_LOOKUP"."COL1" "TABLE_XXXX_LOOKUP"."COL2"))
INDEX_RS_ASC(@"SEL$1" "I"@"SEL$1" ("TABLE_XXXX_IDENT"."COL1"))
INDEX(@"SEL$1" "A"@"SEL$1" ("TABLE_XXXX_ASSIGNMENT"."COL1" "TABLE_XXXX_ASSIGNMENT"."COL2" "TABLE_XXXX_ASSIGNMENT"."COL3"))
LEADING(@"SEL$1" "L"@"SEL$1" "I"@"SEL$1" "A"@"SEL$1")
USE_NL(@"SEL$1" "I"@"SEL$1")
USE_NL(@"SEL$1" "A"@"SEL$1")
11 rows selected.
SQL> -- no OPT_ESTIMATE with the SQL Profile we created manually !
SQL> -- again it's directive - USE_NL, INDEX, LEADING, etc...
SQL>
So I apologize to all you SQL Profiles out there who have been lumped together by my prejudiced view, just because of the acts of a few of your brethren (i.e. the ones created by the SQL Tuning Advisor). SQL Profiles do indeed have all the capabilities of Outlines and probably are a better choice in most cases than Outlines.
Thanks again to Randolf Geist for his comments and his ideas on creating manual SQL Profiles.
Kerry,
nice to see that you had a second look at SQL profiles.
By the way, I really had a good time reading your “Oracle Performance For Developers” post, in particular the “Jeff/Kevin” part was fun :-))
Regarding 11g SQL plan baselines and actual execution plan: I’ve recently played around shortly with 11g baselines and it looks to me that they still simply store the hints along with the statement. Do you know where the “actual” execution plan would be stored in the underlying baseline tables? I could only identify the hints in the base tables that are used e.g. in the DBA_SQL_PLAN_BASELINES view.
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/
The main difference between an Outline and a SQL Profile is an Outline contains a full set of query execution plan directives where a SQL Profile (created by the Tuning Adviser) only contains adjustments (OPT_ESTIMATE / COLUMN_STATS / TABLE_STATS) for cardinality allowing the optimizer the option to choose the operation based on the additional information. This means an Outline always has exactly the same execution plan, but a SQL Profile may not.
To use an analogy, an Outline is a complete set of turn by turn directions, where a SQL Profile contains only the (adjusted) estimated driving times for portions of the trip.
Randolf,
I’m not completely sure about whether Baselines are using hints or not. So I probably should not have said anything about it. But, I haven’t seen anything in the docs about them using hints. And, the DBA_SQL_PLAN_BASELINES view is based on the SQLOBJ$, SQLOBJ$DATA and SQLOBJ$AUXDATA objects which expose info for SQL Profiles as well as Baselines. The SQLOBJ$ table has a column (PLAN_ID) which contains a plan hash value. Interestingly, that column is not populated for the SQL Profiles but is populated for the Baselines. But it also has a column containing the hints, which is populated for both Baselines and Profiles. So I need to do some more research into the issue, but this point I’m guessing/hoping Baselines do not use hints. (I haven’t seen a place where they would be stored yet though)
I’m glad you enjoyed the humor about my cross wired brain with regards to Jeff Holt.
Greg,
Yep. I agree. The Profiles created by the SQL Tuning Advisor do appear to be completely different animals from the manually created ones. The manually created SQL Profiles appear to have the “turn by turn” hints like the Outlines do. And I will say, the ones that the SQL Tuning Advisor builds are the ones that seem to go sour. (not too surprising since a lot of systems gather stats nightly)
I have to admit that I have changed my mind about Outlines really having the ability to completely lock a plan by the way. I believe that trying to limit the optimizer to picking a single plan with hints is a difficult task at best. That’s why I’m hopeful that Baselines are taking a totally different approach (maybe you can help shed some light on that question). That said, Outlines have done a very good job for many years now. I’m just thinking Baselines will be better.
Kerry
On Christian Antognini’s book: “Troubleshooting Oracle Performance”, Christian gives the following query which provides the hints list:
SELECT extractValue(value(h),’.’) AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h
WHERE so.name = ‘SYS_SQL_PLAN_8fb2691f3fdbb376’
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
I don’t know if publishing this query implies fee issues.
Best regards,
Roberto
Roberto,
Yes, that’s basically the same as my sql_profile_hints11 script (link in the post), except that mine joins to the dba_sql_profiles view – so obviously limits to profiles. As I mentioned above, the sqlobj$ table contains records for both SQL Profiles and Baselines. The Baselines also have a plan_hash_value associated with them, while the Profiles do not. So I’m still not certain exactly what’s happening with Baselines. They certainly do have hints associated with them, I’m just not sure yet if they are used or not. I’m thinking they are not – but need to look a little closer.
I have been meaning to get a copy of Christian’s book for a while. Maybe I’ll do that today!
Thanks for your comments.
Kerry
[…] Oracle Whitepapers / Presentations « SQL Profiles […]
[…] 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 the optimizers […]
Hi, I have sql id and plan hashvalue. When i run the script to create profile, it’s failing with the following error. I copied the script from this website.
I am running Oracle 10.1.0.5
xmltable(‘/*/outline_data/hint’
*
ERROR at line 11:
ORA-06550: line 11, column 9:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
Kerry,
I have observed that a profile (created by Sqltune) which has been used by Oracle for a while suddenly stopped being used- Once I disabled and than enabled the same profile Oracle resumed using it.
Any idea why this behavior occurres??
Yuval
Sarala,
Sorry, but this functionality is limited to 10gR2. The xmltable function is a 10gR2 feature. It could actually be worked around by using the older table(xmlsequence(extract())) syntax, but unfortunately, the column that exposes the hints (other_xml) in v$sql_plan doesn’t exist until 10gR2 (at least it doesn’t exist in my 10.1.0.3 database). So I’m afraid this is not available for your current release.
Kerry
Hi Kerry,
Thanks for replying. I dont need to use any hints. All i need to lock the explain i am getting now without stats. I cant afford to keep this table stats deleted for long time. Other processes are suffering because of lack of stats.
Please let me know the way if i can just use sql profile with planhash value and sql id. Right now, the situation is, if i gather stats and run plan, it is going for FTS and without stats it’s using PK index. Thanks!! -Sarala
Yuval,
I have not actually experienced that behavior. I will say, that generally speaking, I view SQL Profiles (and Outlines and Baselines for that matter) as expedient methods to influence Oracle’s behavior (i.e. bandaids), while a more long term solution is arrived at (fixing the stats, modifying the code, etc…).
Out of curiosity, was this SQL Profile created by the SQL Tuning Advisor or did you directly create it via the import_sql_profile procedure? The ones created by the Tuning Advisor often have hints that scale the costing calculations – these are the type that tend to “sour” over time as the stats change. I did a post a while back about that here: http://kerryosborne.oracle-guy.com/2008/09/sql-tuning-advisor/
Also, 11g has a lot more to consider when optimizing a statement due to SPM. Are you on 11g by any chance? You could always “Wolfgang” the statement. A 10053 trace will give you a lot of information on why it chose not to use the SQL Profile, if you can get it to reproduce the behavior. The trace file should have a little blurb at the top saying it found the Profile in the SMB and then it will attempt to validate it later in the trace file. A search for 10053 on this site should turn up some examples of the kind of information available in the trace files.
Kerry
Sarala,
Try creating an Outline. They have been available since at least 9i. You can create one easily on any statement in the shared pool. Then you can change the stats any way you like and other statements will use them, but the statement with the Outline should stay not change. Note that Outlines are starting to get dated and all of these approaches to locking a plan are at best, stop gap measures. But for what you described, I would create an Outline. Look at this post for a script to create an Outline for a given statement: http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/
Kerry
Sarala,
One last thing. Keep in mind that Outlines (and SQL Profiles and Baselines for that matter) are all somewhat flakey, in my opinion. So if you are going to employ one of these things you should verify that’s it’s doing what you want. (i.e. – after creating an Outline you should look at the hints to make sure they make sense, and then verify that the statement actually uses the Outline, and then verify that it actually comes up with the correct plan by using those hints, etc…)
Kerry
Note: I have updated the rg_sqlprof1.sql script to use sql_fulltext instead of sql_text. The sql_text column is truncated at 100o characters, while the sql_fulltext column has the whole statement. If you get any errors when attempting to create a SQL Profile saying that there is already a Profile for the specified text with the original version, this is probably the reason.
Kerry
HI Kerry.
Just to confirm. You are suggesting that the automatic SQL Tuning Advisor will create a SQL profile using the OPT_ESTIMATE parameter and that will create a fudge factor together with SQL hints.
Using a manual call to dbms_sqltune will result in a sql profile that has the hints but not the fudge factor and therefore more stable across data changes and stats changes ?
Regards
John Fak
John,
Yes that is correct. The Tuning Advisor often uses the OPT_ESTIMATE hint to apply a scaling factor to various operations. The scripts on this post that create SQL Profiles use the hints exposed in v$sql_plan or dba_hist_sql_plan to create the SQL Profile. These hints are there for every statement and are used to attempt to reproduce an existing plan (not always successfully – but it works most of the time). The OPT_ESTIMATE is used to try to get a different plan. Unfortunately, as the stats change, these scaling factors (which will remain constant) can wreak havoc. That’s why SQL Profiles of this type tend to “sour” over time. I don’t believe you’ll ever find an OPT_ESTIMATE hint associated with a statement that is not being manipulated – via hints, or SQL Profiles for example.
This post has a script that dumps the hints associated with a statement in the AWR tables called sql_hints_awr.sql. You can see how it’s used above. You can get the same info from v$sql_plan via XPLAN with the hints option or directly like this:
!cat sql_hints.sql
select
extractvalue(value(d), ‘/hint’) as outline_hints
from
xmltable(‘/*/outline_data/hint’
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id like nvl(‘&sql_id’,sql_id)
and child_number = &child_no
and other_xml is not null
)
) d;
I think you will find that SQL Profiles of this type will be much more stable.
Kerry
Cool.
1 more question as I have not found much on oracle DOCO and have not thought about how to build a testcase.
When would a parsed pice of SQL have an identical SQL_ID but a different PLAN_HASH_VALUE – eg as per your ouput in unstable plans ??
We have the same situtation where a SQL_PROFILE is not always being used – and we have 4 x PLANS for same piece of SQL (SQL_ID) in shared_pool. 3 are good, 1 is not. Thinking maybe to remove SQL_PROFILE and add stored outline ?
thanks
Additionally, the valuesin V$SQL_SHARED_CURSOR are all set to ‘N’. So doesn’t this mean that the child cursors (4) should all be shared and use the same plan ?
ta
It’s very common to have multiple child cursors for a single sql_id. Usually they have the same plan, but it is not uncommon to have different plans. This happens often due to bind variable peeking. A common scenario would be that a cursor gets invalidated by gathering stats. The next time it gets executed, a new cursor gets created and the statement gets re-optimized. If the bind variables are different, a new plan can result. I’m sure there are other ways this can happen as well, but this one is pretty common (particularly in 10g which is overactive in it’s histogram creation, and under active in the sample size it uses to create them).
If you have successfully created a SQL Profile on a statement, it should continue to get used unless something changes the text of the statement, which of course would change the sql_id as well, or something changes to prevent the SQL Profile hints from working. In that case I think the Profile would still be used but the non-valid hint would be ignored (I haven’t tested that though).
dbms_xplan.display_cursor will tell you if a Profile or Outline or Baseline is being used by a cursor by the way. I have not seen a situation where some executions use a Profile and others do not. Most likely you are seeing children that are no longer valid but still hanging around. You can monitor the executions in v$sql to determine if more than one is actually being used. (I’m assuming by the way that you are on 10g – if you’re on 11g then Adaptive Cursor Sharing can kick in and create child cursors with different plans and bounce among them based on the value of the bind variables)
I had not noticed that you could get multiple children with none of the flags in v$sql_shared_cursor being set to ‘Y’. I verified that you can, but I am not sure why that would occur. Probably some condition that they haven’t gotten around to creating a flag for yet. That view is just doing a bitand on a bitvector column which means there are probably other values that are not exposed by that view definition.
Kerry
Hey Kerry.
Very insightful and useful.
Is there a way to determine if its is a result of bind variable peeking ?
The system here used to have ‘_optim_peek_user_binds ‘ set. However I am not sure if they have since lost this setting over the comming months since they upgraded.
I am also wondering if they can set the ‘_optim_peek_user_binds’ at a session level before the call of this procedure they are having problems with.
To summarise – with investigation it appears they generate 4 plans. Some are invalidated probably due to stats. They then get hard parse and execution oplan gets set. Somedays this is good and fine. Others its not.
Where I am heading is that I believe by default oracle collects histograms (method_opt) auto in 10g. I am going to guess they have histograms set on these partitions. If we remove the histograms I think we can remove the effect of the bind variable peaking ???
However what would be approach if its not histograms – use the ‘alter session’ in the proceedure I mentioned ??
thanks and sorry for taking up so much space on the blog.
John
So it sounds like I need to have them turn off bind variable peeking for this query only. Else rewrite as ‘literal’ – but not sure how achievable this will be.
Any thoughts on action plan in this situation ??
Thanks
John
John,
Sounds like the classic BVP situation. Using bind variables with histograms on skewed columns is just a bad idea. To convince yourself that you have BVP, just create a script that you can run from SQL*Plus and replace the bind variables with literals – if you can get the different plans by changing the values of the literals, you’ve got it.
If you have just a handful of statements that are problematic, creating a SQL Profile (as discussed in this post) can be very helpful and extremely quick to implement. That assumes that there is one plan that is acceptable, regardless of the bind variables – which is often the case. This can give a great deal of relief while the developers change the code to use literals appropriately. And by the way, the developers may be well aware of the skewed data but just not know what to do about it, so it might be worth a discussion with them on the issue.
As for, _optim_peek_user_binds: using it to turn off BVP is tempting, but there is no guarantee which plan you will get. I would tend to lean that way only if the problem was rampant (lot’s of statements).
Histograms can be problematic in 10g (too many and small sample sizes). So a thorough review of these (probably getting rid of many and increasing the sample size on the ones you keep) is in ordered. But this can be a lengthy endeavor.
I’ve done a couple of other posts that you may want to look at. Just search for “bind variable peeking” and “unstable plans”.
Kerry
Great.
Thanks again Kerry.
Enjoyed the thread.
John
No problem John. Good luck!
Kerry
I have ran the below script “@create_sql_profile”, i have face the following error
Kinldy give your suggestions
———————————————————————————
passing (
*
ERROR at line 12:
ORA-06550: line 12, column 9:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 6, column 65530:
PL/SQL: SQL Statement ignored
———————————————————————————
SQL> @create_sql_profile
Enter value for sql_id: 767pug2dbpqpc
Enter value for child_no: 0
Enter value for category: Test
Enter value for force_matching: TRUE
old 18: sql_id = ‘&&1’
new 18: sql_id = ‘767pug2dbpqpc’
old 19: and child_number = &&2
new 19: and child_number = 0
old 31: sql_id = ‘&&1’
new 31: sql_id = ‘767pug2dbpqpc’
old 32: and child_number = &&2;
new 32: and child_number = 0;
old 37: , category => ‘&&3’
new 37: , category => ‘Test’
old 38: , name => ‘PROFILE_&&1’
new 38: , name => ‘PROFILE_767pug2dbpqpc’
old 43: , force_match => &&4
new 43: , force_match => TRUE
passing (
*
ERROR at line 12:
ORA-06550: line 12, column 9:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 6, column 65530:
PL/SQL: SQL Statement ignored
RAMACHANDRAN,
Looks like you didn’t get the full rg_sqlprof1.sql script or inadvertently introduced a blank line. The script should have the following lines after the line that is throwing the error for you:
…
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id = ‘&&1’
and child_number = &&2
and other_xml is not null
)
) d;
select
sql_fulltext
into
cl_sql_text
from
v$sql
where
sql_id = ‘&&1’
and child_number = &&2;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => ‘&&3’
, name => ‘PROFILE_&&1’
— use force_match => true
— to use CURSOR_SHARING=SIMILAR
— behaviour, i.e. match even with
— differing literals
, force_match => &&4
);
end;
/
Hope that helps.
Kerry
Script to create a SQL Profile on one statement using the hints from another statement. Allows Profiles to be created on statements using direct manipulation of statement with embedded hints.
Hey Kerry,
I keep running into the following error:
SQL> select extractvalue(value(d), ‘/hint’) as outline_hints
2 bulk collect into ar_profile_hints
3 from
4 xmltable(‘/*/outline_data/hint’
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 dba_hist_sql_plan
10 where sql_id = ’23g21uwtcdu2u’
11 and plan_hash_value = 1430279444
12 and other_xml is not null
13 )
14 ) d;
select extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 1:
ORA-00904: “D”: invalid identifier
Any hints on what I am missing here?
Thanks!
-Aurora
Hi Aurora,
That statement (from rg_sqlprof2.sql) should be inside a pl/sql block for the bulk collect to work. However, when I execute that statement in SQL*Plus, I get an error about the bulk collect being an unimplemented feature, not the error message you are getting. Like so:
SQL> select extractvalue(value(d), '/hint') as outline_hints 2 bulk collect into ar_profile_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 dba_hist_sql_plan 10 where sql_id = '&sql_id' 11 and plan_hash_value = &hash_value 12 and other_xml is not null 13 ) 14 ) d; Enter value for sql_id: gy2jwr7vcghwb Enter value for hash_value: 973669434 bulk collect into ar_profile_hints * ERROR at line 2: ORA-03001: unimplemented featureTry it without the bulk collect line and see what happens. What version are you on by the way?
Kerry
Hi Kerry,
Do you know if there are any restrictions on SQL Profiles?
I have one complex query (which is generated by Oracle Warehouse builder) and I can’t
persuade Oracle to use it.
It’s multitable insert SQL (more then 100 lines) with subqueries and db_links. I can send you code if you like.
Regards
Fredy Mercy
Hi Fredy (or is it Freedy?),
There are not any restrictions that I’m aware of. But it’s possible. When you say that you can’t persuade Oracle to use it, if you mean that you create a SQL Profile on a statement, but when it is executed the next time, the Profile is not used at all, then this should only happen if the text doesn’t really match. If you mean, it’s used but you don’t get the plan you expect, there are all kinds of things that can cause that to happen, especially on complex statements. If you send a few more details I’ll be happy to try to help.
Kerry
Hi Kerry,
When I execute DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE, I get signature value of 15926012898303819840,
and when I query dba_sql_profiles table, I get in signature field value of 1,59260128983038E19.
That means that it should work, but accepted SQL Profile is still not used
(I can see it from SELECT * FROM table(dbms_xplan.display(NULL,NULL,’basic +note’)); after I do
explain plan for my SQL statement).
I’ve also tried to store my SQL in CLOB in table, and pull the stored SQL text back as imput in
DBMS_SQLTUNE.CREATE_TUNING_TASK procedure, but still with no success.
I’ve tried to create and accept SQL Profile for my SQL statement from AWR, but without success too.
My conclusion is that it must be some restriction in Oracle SQL Profile functionality that prevents
Oracle engine to use my complex DML SQL statement (multi-table insert which includes db_links)
which is generated by Oracle Warehouse builder.
I know that there are a lot of restrictions in Stored Outlines (multi table insert, remote tables…). Maybe is the same with SQL Profiles.
For less compex statements, SQL Profile works fine.
Regards,
Fredy
Fredy,
Sorry, I completely missed your point on the first post – skipped over the multi-table insert part. Yes, that restriction appears to have been carried over to SQL Profiles. I expect they took the Outline code as a baseline and modified it to come up with SQL Profiles and Baselines in 11g. It doesn’t appear that SQL Profiles or Baselines will work for multi-table inserts. If you look in the other_xml column of v$sql you won’t see any hints (which is normally what’s used when you create an Outline or Baseline).
Note, you can use this script to see the hints that Oracle will use to create an Outline:
http://kerryosborne.oracle-guy.com/scripts/sql_hints.sql
I did a quick test to see if a one line SQL Profile could be created and applied that didn’t depend on the Outline Hints and even that did not work. It created the Profile, but did not apply it to the statement. So I believe you are correct in that you can’t apply a SQL Profile to a multi-table insert.
Kerry
Kerry,
Thanks for your reply.
I’ve solved plan instability by manually correcting statistics (DBMS_STATS.SET_TABLE_STATS) that caused the execution plan to change.
Regards
Fredy
Kerry,
Wanted to drop a line to say thanks for the create_sql_profile_awr.sql script. I found a situation today that allowed me to solve a production issue using it. I have a script that shows sql execution stats over time from awr. Note that on the 25th of Jan at 10am there was a plan change and buffer gets shot through the roof, at 16:00 we ran your sql script picking a previous hash plan value and the timings returned to normal.
Wow. That’s quite a change – going from 200K lio’s to 4 Billion lio’s per exec. Glad the script was able to help. Good job getting it fixed that quickly. Thanks for letting me know. 😉
Kerry
Hi
I created SQl profile through EM via advisory central using system .
Now if the same query gets executed via other user profile does not get used. How do i transfer that profile for all the users?
Hi Shruti,
If a Profile exists for a given statement it should be used regardless of which user is executing the statement. There are a couple of things you can check to make sure it should be used. Look at the signature columns in v$sql and dba_sql_profiles. They should match. Also, if the statement is using the Profile, the sql_profile column in v$sql will have the name of the profile in it. If the signatures match but it’s not being picked up, that’s probably a bug. I have seen this happen with force_matching=true. So you might try without setting that option. By the way, there are many posts on this site with information on how Profiles work “under the covers”. Good luck.
Kerry
Thanks for the excellent blog. Very useful information. I am getting following error message while trying to create a SQL profile.
Enter value for sql_id: a0j23muc76nuz
Enter value for plan_hash_value: 2627696582
Enter value for category:
Enter value for force_matching:
old 18: sql_id = ‘&&1’
new 18: sql_id = ‘a0j23muc76nuz’
old 19: and child_number = &&2
new 19: and child_number = 2627696582
old 31: sql_id = ‘&&1’
new 31: sql_id = ‘a0j23muc76nuz’
old 32: and child_number = &&2;
new 32: and child_number = 2627696582;
old 37: , category => ‘&&3’
new 37: , category => ‘DEFAULT’
old 38: , name => ‘PROFILE_&&1’
new 38: , name => ‘PROFILE_a0j23muc76nuz’
old 43: , force_match => &&4
new 43: , force_match => false
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 24
Two hash values exists for a SQL_id.
# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
1 3347154448 68,154,693 31,359 981 1060
2 2627696582 31,026,788 185 1061 1101
Yuvaraj,
The script is prompting for a child number as opposed to a plan hash value. The child_number should be 2 in your example.
Kerry
hi kerry,
This is a great article. by the way, I tried loading the hints from the AWR and it gives me this error
declare ar_profile_hints sys.sqlprof_attr;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 12
do you think the hints that im loading is quite long?
how do i resolve this?
Erwin
Hi Erwin,
Try running this script: You’ll need to know the sql_id and plan_hash_value.
select
extractvalue(value(d), ‘/hint’) as outline_hints
from
xmltable(‘/*/outline_data/hint’
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = ‘&sql_id’
and plan_hash_value = nvl(‘&plan_hash_value’,plan_hash_value)
and other_xml is not null
)
) d;
If that blows up, try just selecting the other_xml field from dba_hist_sql_plan – it will be ugly but you may be able to see what’s causing the issue.
Kerry
Hi Kerry,
This is one of the best posts I have found on using sql profiles in an effective way, thanks for sharing this. Most of other scripts work fine on our environment but I get similar error as mentioned by Aurora earlier when I run create_sql_profile.sql or following SQL with no bulk collect. I am running 10.2.0.2.0 database. What could be the reson.
1 select
2 extractvalue(value(d), ‘/hint’) as outline_hints
3 from
4 xmltable(‘/*/outline_data/hint’
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 sql_id like nvl(‘&sql_id’,sql_id)
12 and child_number = &child_no
13 and other_xml is not null
14 )
15* ) d
enc_db1> /
Enter value for sql_id: 1g3gahk3j6fdx
Enter value for child_no: 0
extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 2:
ORA-00904: “D”: invalid identifier
Thanks,
Amit
Amit,
I do not have access to a 10.2.0.2 database. It’s possible that query doesn’t work correctly on that version. It works fine for me on 10.2.0.3. Can you try it on another newer version to see if the script works for you there? I suspect the xml functions may have changed between 10.2.0.2 and 10.2.0.3. If so you may be able to rewrite that statement to work with you’re version (or upgrade ;).
Kerry
Note: I have replaced the references to sql_profile_hints11.sql with sql_profile_hints.sql which has been modified to work on 10g and 11g. Also, I have replaced the create_sql_profile.sql and create_sql_profile_awr.sql scripts with more robust versions that no longer call Randolf’s original scripts.
Can I know if there is any view available to check whether a SQL_profile being used or not?
Raja,
Yes, look in v$sql. There is a column called sql_profile that will have the name of the Profile associated with the statement. Or just use my find_sql_using_profile.sql script.
Kerry
Kerry,
Thank you many times over for the code! It’s helped us add hints to not only errant SQL that we can hint on the fly and verify the performance before spending the weeks of paperwork and regression testing for a code change. And we leave it on, til the new code is promoted. For your readers, it also overrides vendor hints in their SQL and puts in the ones we specify, when a new index is advantageous, or the table needs a HJ vs. an NLJ. Amazing, a real KO.
Now for the posting \ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind\, we hit the error today. It’s the length of the hint, sys.sqlprof_attr VARRAY(2000) OF VARCHAR2(500); however, one of my hints was (502). I’m working on a resolution, post when I find it.
Run this SQL to verify the Bulk Bind error, if greater than 500, that’s the problem:
select Length(extractvalue(value(d), ‘/hint’))
from xmltable(‘/*/outline_data/hint’
passing ( select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = ‘&sql_id’
and child_number = &child_no
and other_xml is not null ) ) d;
Thanks again!
Eric
Hi Eric
I’m glad to hear you found this useful. 500 characters is a really long hint. I assume it’s probably one of those new-fangled INDEX hints that specifies all the columns instead of the index name. As a work around, I tried just defining my own varray with longer varchar2’s instead of using the sys.sqlprof_attr type definition, but the import procedure didn’t like it. Another possibility is to use a script provided on Metalink as part of the SQLT package. The script is called coe_xfr_sql_profile.sql and it produces a file with the hints inline so you can edit them before you create the Profile. This would allow you to change the hint to something under 500 characters. (changing to specific index name instead of columns if that’s the issue as I suspect)
I did a post on using the coe_xfr_sql_profile.sql script a while back:
Oracle Support Sanctions Manually Created SQL Profiles!
[…] sendo possível, buscar dados no AWR. Uma das melhores formas foi demonstrada por Kerry Orbone, clique aqui para ver ou esta versão, que eu mais utilizo […]
Hi,
I have 2 PLAN_HASH_VALUE for the same sql_id , the cost is the same , but the Elapsed Time (ms) and CPU Time ( ms )
is diferent , the optimizer is choosing the wrong PLAN_HASH_VALUE ( with diferent sql plan )
How can I force ( may be HINT ) the sql to go with the correct sql plan?
thanks.
[…] by SQL Tuning Advisor may change plans when stats change. Also SQL profile requires a license. http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/ http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/ […]
Here’s one way to get around those annoying ora-22992 errors when you try to create a profile in a local database using sql text and outline hints from a remote database:
drop table perfstat.junk_pjk_olhints
/
create table perfstat.junk_pjk_olhints
as
select
other_xml
from
dba_hist_sql_plan@&&3
where
sql_id = ‘&&1’
and plan_hash_value = &&2
/
drop table perfstat.junk_pjk_sqltext
/
create table perfstat.junk_pjk_sqltext
as
select
sql_text
from
dba_hist_sqltext@&&3
where
sql_id = ‘&&1’
/
Then run a local create profile script that references the two local junk tables to get hints and sql text to be used in your profile. I did this a few minutes ago, worked ok, the sql profile is created locally, now I need to get developers to run the problem form to see if the problem query is fixed by the profile.
Thanks for this blog.
Paul
business intelligence…
[…]Kerry Osborne’s Oracle Blog » Blog Archive » SQL Profiles – Kerry Osborne’s Oracle Blog[…]…
[…] and Kerry Osborne’s post http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/. If using SQL Plan Management in Oracle Database 11g you can use the procedure provided in the […]
[…] decided to use Kerry Osborne’s method of creating a SQL profile from the AWR repository to force Oracle to use a known good […]
[…] considered our options and decided to use Kerry Osborne’s method of creating a SQL profile from the AWR repository to force Oracle to use a known good […]
Hi Kerry,
Was wondering if you have used the plan_table to create sql profile before
Hi Joshua,
Yes – the create_sql_profile.sql script in the post uses the hints stored in the other_xml field of v$sql_plan to create a SQL Profile that usually forces a statement to use the plan from which the hints were extracted.
Kerry
[…] brainstormed our options and decided to use Kerry Osborne’s method of creating a SQL profile from the AWR repository to force Oracle to use a known good plan. Note that SQL Profiles require a license for Tuning […]
Kerry,
Thanks a lot for sharing the insight on OPT_ESTIMATE hint and yes the coe_xfr by oracle script is definitely a flexible script since it lets you edit the hints or sql_text in the generated script that you can run anywhere.I see there is so much talk on the pinning a good explain plan and it works most of the time, but im having an issue for which i think it would help to take your advice or any good scripts that you have.
So i have this SQL_ID which has been running fine and recently customer started complaining about the slowness. so i went in to check the change of explain plan and turns out the explain plan hasn’t changed, but the corresponding avg_etime has spiked badly (avg_lio also). now obviously something has changed (optimizer_env, storage ) ie causing the same explain plan to take longer.
now i know i should be looking at dba_hist_sqlstat to find out which part (i/o, cpu etc) is taking longer than before , but i cant seem to write a sql which gives me a clear picture.
do you have anything at hand that you use when you have such scenario.
thanks
tarun
tarun,
I use a couple of scripts (which you can modify to look at whichever stats you want).
awr_plan_change.sql – lists execution stats for a SQL_ID for each snapshot in historical order
awr_plan_stats.sql – aggregates the data by plan (this is probably not useful since you have the same plan)
By the way, the plan_hash_value is calculated based on a subset of the plan data (basically the operation and object). It ignores predicate information, so although the plan_hash_value is the same the actual plan may be slightly different (but may perform very differently). You may be able to detect this by looking at the plan_hash_2 value in the OTHER_XML field in DBA_HIST_SQL_PLAN. (I have not written a script to identify this situation yet – maybe soon)
Kerry
yes i agree with you that plan_hash_value will not change if the filter/access predicates changed or their order. so let me ask u two questions…
1. assuming the filter predicates were also same then how can we figure out where exactly was the SQL spending time more time for that particular execution… (enq wait for another session, storage, cpu, network, any new v$session_wait wait_event)… dba_hist_sqlstat has these absolute and delta columns for various attributes …i just cant seem to interpret them…and thats why i am here…..i guess i want to say “for that execution the sql spent 10 secs on cpu but now it is spending 120….rest all wait times are same….hence its the cpu….”
for example i want to figure out the 108.289 secs (last line) in the following
199642 1 22-FEB-12 11.50.44.468 AM gxn7ry17dh5sq 5 14.269 29,178.4
199643 1 22-FEB-12 12.00.59.733 PM gxn7ry17dh5sq 2 16.362 22,507.5
199644 1 22-FEB-12 12.10.09.047 PM gxn7ry17dh5sq 3 16.753 19,809.7
199645 1 22-FEB-12 12.20.18.393 PM gxn7ry17dh5sq 3 18.509 56,736.7
199646 1 22-FEB-12 12.30.30.690 PM gxn7ry17dh5sq 5 108.289 33,629.8
2. if filter predicates are different how can we force an older explain plan (same plan_hash_value but diff predicates) onto the sql_id
tarun,
1. I would check to see if ASH data shows any unusual wait events associated with that statement for the snap shots of interest. If that doesn’t show anything I’d look at O/S logs to see if the system was having any issues that might account for the long run time (overloaded i/o or cpu or memory shortage). If on 11g you can look at SQL Monitor data to see exactly what happened for an individual execution of a long running statement (this is the best way if you are on 11g and can have a look before the data is SQL Monitor dataa is flushed).
2. I don’t know of a way to do this for sure. There may be some set of hints that would do it and if so a SQL Profile or Baseline should be able to do the job.
Kerry,
Thanks for your posts, they have been great. Regarding oracle performance, I am wondering if I could get your input. I have been studying / working on tuning issues at our shop.
I’ve read a lot of the blogs / oracle doc regarding the changing oracle features (outlines to awr to bind variable peeking to sql_profiles to cardinality feedback to sql_baselines / sql plan management).
A few months ago I came up with an idea / desire which I now realized may be doable without much engineering on my part.
My thinking was that it would be great if oracle stored off the historical performance on queries (which I now realized it does for maybe 10 days in dba_hist_sqlstat, dba_hist_sqlplan and dba_hist_sqltext ) so that after an upgrade (or optimizer parameter change) we could deal with the sql that is not performing well and see how it did in the past.
Then maybe we could use an oracle technology (like sql_profiles) to get the optimizer to change gears and use the plan it was using before the change.
I have experience activating sql_profiles that the sql tuning advisor built. I’ve seen your post about creating a sql_profile from awr and / or the shared pool views.
Do you think this is doable or am I wasting my time because you really can’t point oracle back to a plan but only provide hints to try to get it to use a plan that you want.
I would want to be able to get oracle to use an old plan under these conditions.
1. cardinality feedback issues: query runs bad on run1 and good on run2 and beyond, but not across bounces. Want it to run good on run1 also. I had one that ran 2 days then 8 minutes. After a bounce the first run was 2 days again. This was no simple query. With a bunch of union statements it ended up to be 197 plan lines. Note also, the vendor written query has literals for dates that change all the time.
We’ve seen them vice verse where they run ok on run1 then bad on further runs.
2. Upgrade issues from let’s say 11.1.0.7 to 11.2.0.3, or from 10.2.0.4 to 11.2.0.3.
It would be nice to get the system to run as it did before. We had a vendor supplied all_objects query that went from .05 seconds to 4.75 seconds. Oracle messed it up (all_objects queries but we had to revert back to 10.2.0.4 because we could not get performance. We had code that was running the query 27 times to kick off a report where customers were standing in line waiting for a receipt. Could we get a sql_profile built and accepted from a plan from a different version?
3. Parameter change on Version 11.2 of oracle. Initially using optimizer_features_enable=10.2.0.4 after cutover issues discovered. Now wanting to cutover to optimizer_features_enable=11.2.0.3. Would be nice to know we could fix any query that ran ok before but now became slow. Could we get a sql_profile built and accepted from a plan from a different optimizer parameter.
We deal with a lot of vendor applications.
My understanding is most techniques deal with providing hints to push the optimizer to a plan. I am not sure this will work in these conditions. It would be nice if you could direct the optimizer to a good plan from the past. Not sure why Oracle does not natively allow that option.
I have read about sql_baselines / sql plan management but am concerned that the lack “sharing” limits its abiltiy to help ie., when there are literals it is unusable. And I believe the good plan has to come from your currrent environment.
After writing most of the above I just reread some of your postings and find in2009 you said:
“I have to admit that I have changed my mind about Outlines really having the ability to completely lock a plan by the way. I believe that trying to limit the optimizer to picking a single plan with hints is a difficult task at best. That’s why I’m hopeful that Baselines are taking a totally different approach (maybe you can help shed some light on that question). That said, Outlines have done a very good job for many years now. I’m just thinking Baselines will be.”
Unless you now have new knowledge in the area, you likely will say that in dealing with more complicated statements and environments ( like my list of desires above) you likely won’t be able to get the optimizer to make a plan that matches one from the past.
Thanks for your time.
Lynn,
Wow – I think you get the prize for the longest comment.
Let me answer your specific questions first then I’ll make a few comments:
1. Cardinality Feedback is a new feature and it is still a bit immature. The is a parameter to turn it off if is causing you lot’s of issues. Manually created SQL Profiles based on hints in OTHER_XML column of v$sql_plan or dba_hist_sql_plan can definitely be used force (or come close to) forcing a desired plan. Those hints are not always complete enough to force one and only one plan but most times they are.
2. Yes – SQL Profiles can restore an old plan even from an older version in most cases.
3. Yes – SQL Profile do their magic with hints so if you have the plan you want (based on the optimizer parameters in place at the time it was parsed) those hints should generally give you the plan the plan that was being used when the profile was created, even if you change optimizer parameters. In fact that’s a technique that I have used many times in the past. (i.e. manipulate a test version of a statement until I get the plan I want using whatever means I can – including embedded hints and changing optimizer and even hidden parameters. Then create a SQL Profile on the new statement, test to make sure it still gets the desired plan after creating the profile, and then attach the SQL Profile to the original statement).
So I think the answer to all your specific questions is yes.
Now a couple of comments:
1. It doesn’t always work for the same reason you quoted me on, so you need to test after create on to ensure that it has been picked up, and that it hasn’t caused the plan to change to something you weren’t expecting (this would be a bug but it does happen occassionally).
2. AWR retention can be increased to however long a periord you are willing to store. See this post: Tracking Parameter Changes
Kerry
I came to appreciate sqlprofiles during one of my first migrations from 9i to 10g. Smiles i still have best of memories of that project. Awsome post , ty so much for it.
Mathijs
[…] to Kerry Osborne’s blog that let me understand on this […]
I need help to create a procedure for monitoring of all procedures in the database with the runtime individual based on a table of historical parameters. Do you have any sample script?
Hi Mario,
No I’m sorry I don’t have a script like that. Usually I’m digging into SQL underlying procedures, so most of my stuff is targeted at individual SQL statements. Since statistics for procedures are recorded in a similar manner to SQL statements you can certainly pull information that is captured in AWR to see how they perform over time. awr_plan_change.sql is a simple script that is used in this post to track some performance stats by SQL_ID (which each procedure is assigned). So maybe that could provide a starting point for you.
Kerry
Great stuff, added more to my understanding of profiles. Cool
Hi Kerry
Your article was instrumental in helping me create 3 SQL profiles today (and learning a lot at the same time).
We initially had problems after migrating to 10g with some queries being fast one day and slow another, due to _optim_peek_user_binds. I finally set it to FALSE which resolved the problems for the last 5 years or so.
Yesterday a programmer called and asked that I look at a query of his which would take 20 seconds to run.
I found out that in a session with _optim_peek_user_binds=TRUE it was only taking 0.01 seconds… So I went on and created a profile from the query execution on that other session. Works like a charm. The root cause is possibly the stats, but it now works and it is good.
BUT in testing, I found out I could not copy-paste the query from Grid Control into SQLplus, and execute it to achieve the same goal. Oracle does not see this as being the same query at all, and will not use the profile created based on the SQLPlus run.
Is there any way to create a profile but somehow make it relate to a different query “text” for which the code resides in the shared pool ?
For example, query X is slow. I have the query in V$SQL.
I paste that into SQLPlus and add some hints. It is now fast. This modified query is also in V$SQL now.
I want to create an outline with the new query, but that will NOT be effective when the original query is run. Is there a way to assign a different SQL_TEXT to the profile ? I would pick up the text from the original query in V$SQL…
I read that you can change stuff (hints) in sqlprof$attr so there might be a way to change the SQL_TEXT in dba_sql_profiles ?
Thanks a lot !
Hi Syltrem,
Yes I have a script for that. 🙂 It’s called move_sql_profile.sql.
See this post for more info on it’s use: How to Attach a SQL Profile to a Different Statement – Take 2
Kerry
Hi Kerry,
i have been using your script for creating SQL profile for very long time but unfortunately i hit an error today(which has already been talked about here) but i am unable to find any solution for it…any ideas?
09:01:08 kblrdba@b1p> @CREATE_SQL_PROFILE
Enter value for sql_id: b7ptr6hnac413
Enter value for child_no: 0
Enter value for category: mytest
Enter value for force_matching: TRUE
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 5
and as you and “Eric Harper” mentioned that as has to do something with the length of the hint and you suggested the workaround as below
“I assume it’s probably one of those new-fangled INDEX hints that specifies all the columns instead of the index name. As a work around, I tried just defining my own varray with longer varchar2′s instead of using the sys.sqlprof_attr type definition, but the import procedure didn’t like it.”
my problem is not with the column name being specified for the index but more of all the tables…we have a query that doing a a join on about 55 different table(SAP system)…below are the hints for it…i have removed quite a few lines, but the problem comes with the “LEADING” part that is a length of 795 char….any workaround you can suggest for this ? looks like there is nothing on oracle support either and was not able to find anything good(except your site) pointing towards a possible solution but not sure how…
OUTLINE_HINTS
————————————————————————–
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)
DB_VERSION(‘11.2.0.3’)
OPT_PARAM(‘_fix_control’ ‘5705630:1 6626018:1 6440977:1 6972291:1 6399597:1 6430500:1 5099019:1 9495669:1 9196440:1 8937971:1 6055658:0 13077335:1’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1DBDD868″)
MERGE(@”SEL$13754B19″)
OUTLINE(@”SEL$53″)
OUTLINE(@”SEL$13754B19″)
MERGE(@”SEL$52129FC6″)
OUTLINE(@”SEL$52″)
OUTLINE(@”SEL$52129FC6″)
MERGE(@”SEL$631833A3″)
OUTLINE(@”SEL$51″)
………..
………
……….
……….
OUTLINE(@”SEL$3″)
OUTLINE(@”SEL$58A6D7F6″)
MERGE(@”SEL$1″)
OUTLINE(@”SEL$2″)
OUTLINE(@”SEL$1″)
INDEX_RS_ASC(@”SEL$1DBDD868″ “S50″@”SEL$30” (“TABLE1″.”SID”))
FULL(@”SEL$1DBDD868″ “S55″@”SEL$50″)
FULL(@”SEL$1DBDD868” “DT”@”SEL$49″)
FULL(@”SEL$1DBDD868” “F”@”SEL$1″)
INDEX_RS_ASC(@”SEL$1DBDD868” “D7″@”SEL$29” (“TABLE2″.”SID_TYPE”))
INDEX_RS_ASC(@”SEL$1DBDD868″ “D6″@”SEL$48” (“TABLE3″.”SID_VALUATION”))
FULL(@”SEL$1DBDD868″ “D4″@”SEL$4″)
INDEX_RS_ASC(@”SEL$1DBDD868” “S54″@”SEL$7” (“TABLE4″.”SID”))
INDEX_RS_ASC(@”SEL$1DBDD868″ “D1″@”SEL$16” (“TABLE5″.”DIMID”))
INDEX_RS_ASC(@”SEL$1DBDD868″ “S74″@”SEL$22” (“TABLE6″.”SID”))
………
………
………
………
INDEX(@”SEL$1DBDD868″ “S58″@”SEL$37” (“/BI0/SFM_AREA”.”SID”))
LEADING(@”SEL$1DBDD868″ “S50″@”SEL$30” “S55″@”SEL$50” “DT”@”SEL$49” “F”@”SEL$1” “D7″@”SEL$29” “D6″@”SEL$48” “D4″@”SEL$4” “S54″@”SEL$7” “D1″@”SEL$16” “S74″@”SEL$22” “S44″@”SEL$18” “S48″@”SEL$6” “S67″@”SEL$13” “S66″@”SEL$12” “S73″@”SEL$15” “S65″@”SEL$11” “S61″@”SEL$8” “D2″@”SEL$23” “S46″@”SEL$24” “S57″@”SEL$52” “S51″@”SEL$21” “S43″@”SEL$17” “S47″@”SEL$20” “DU”@”SEL$26” “S77″@”SEL$28” “S49″@”SEL$27” “S42″@”SEL$5” “S45″@”SEL$19” “S64″@”SEL$10” “S56″@”SEL$51” “D3″@”SEL$31” “S79″@”SEL$34” “S78″@”SEL$33” “S72″@”SEL$14” “S53″@”SEL$25” “S63″@”SEL$9” “S52″@”SEL$32” “D8″@”SEL$36” “S60″@”SEL$38” “S70″@”SEL$40” “S68″@”SEL$39” “S75″@”SEL$41” “D9″@”SEL$42” “S59″@”SEL$43” “S69″@”SEL$44” “D5″@”SEL$45” “S62″@”SEL$46” “S71″@”SEL$47” “S80″@”SEL$35” “DP”@”SEL$1” “S41″@”SEL$2” “S76″@”SEL$3” “S58″@”SEL$37”)
Hi Max,
Yeah those long hints are a pain. The problem is that the DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure expects hints to be passed in 500 character strings. My script isn’t smart enough to break long hints into pieces (yet), but Carlos’s is.
Try this one – coe.sql
It’s not quite as convenient, because it creates a script that must be executed to create the Profile. But it’s useful as a quick way of moving a Profile from one system to another and it lets you edit the hints if you want (and it handles long hints!). Let me know if that works for you and maybe I’ll get around to updating my script to be a little more robust.
Kerry
Hi Kerry,
Thanks for pointing me in the right direction. That worked like a charm. i found 2 other SQL statements that had a hint as long as 900 character and it worked for them as well. Thanks again, your help is much appreciated it.
That coe.sql save my day!!
Thanks Kerry
Hi Kerry,
Can I have all the scripts which you have written for my testing and learning purpose please; I mean, I shall create a db (11.2.0.1.0) name “Kerry” on windows and linux VM and wish to test and learn them. If possible and feasible then please let me know.
Thanks and Regards
Girish Sharma
Hi Girish,
I have not published a comprehensive set (nor documented most of my scripts). I will consider zipping all the scripts that have been published on my blog and posting a link to that.
Kerry
HI Osborne can you add option to your create_sql_profile_awr.sql script that generates tuning report as in @?/rdbms/admin/sqltrpt.sql
@?/rdbms/admin/sqltrpt.sql this sql creates sql_profile from cursor cache but not from awr cache but your scripts does not genaretes report that we cannt see what advantages to use sql_profile?
thanks
The script suits my purposes as is. You should be able to easily add that functionality if you want.
Kerry
[…] http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/ […]
Hi Kerry,
I am getting error below when i try to create sql profile
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 6
Look further up in the comments to see a discussion of this issue.
Kerry
Hi Kerry,
Excellently written and very informative from the plan stability standpoint.
Could you please shed some light on the the below concerns :
1) I ran SQL Tuning Advisor on a particular query and the optimizer came up with the better execution plan for that query. The proposed plan by the optimizer was with parallel execution, and when this parallel execution plan was enabled for that query the optimizer calculated the DOP very high and the query ran with high parallelism, then the available cores on the system. In such a case how to embed the hints manually so that we can pass the hints with less DOP.
2) At times the report was running absolutely fine suddenly the users start yelling suddenly that they are unable to generate the report. After running the STA on the query the optimizer comes up with a good plan. Is is due to BPV or anything else.
In such a scenario what could be the possible reason.
Regards,
Shadab
See this post: http://kerryosborne.oracle-guy.com/2010/11/how-to-lock-sql-profiles-generated-by-sql-tuning-advisor/
Hi Kerry,
your scripts helped me so often, thanks for it.
Will they also work on 12c? Today I tried the script and got trouble, but I think the problem is that the old (better) execution plan can’t be found in “dba_hist_sql_plan” anymore. (strange, because the better plan was working since yesterday)
used script: create_sql_profile_awr.sql
Thanks & best regards,
Martin
Hi, can you pls provide script @sql_profiles as mentioned above. couldn’t find in any of the links
It’s linked to on a previous post. Here’s a link: http://kerryosborne.oracle-guy.com/scripts/sql_profiles.sql
Easiest way to find a script on the blog is to use the search box at the top of the screen.
I am genuinely thankful to the holder of this site who has shared this wonderful post at at
this place.