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 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 - his scripts tend to expect parameters, I like to be prompted and have defaults for a couple of parameters)
create_sql_profile.sql - uses cursor from the shared pool (wrapper for Randolf’s)
create_sql_profile_awr.sql - uses AWR tables (wrapper for Randolf’s)
sql_profile_hints.sql - shows the hints in a SQL Profile for 10g
sql_profile_hints11.sql - shows the hints in a SQL profile for 11g
rg_sqlprof1.sql - called by create_sql_profile.sql (Randolf’s script)
rg_sqlprof2.sql - called by create_sql_profile_awr.sql
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_hints11 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_hints11 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.
Randolf Geist:
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):
April 5, 2009, 3:52 pmhttp://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Greg Rahn:
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.
April 5, 2009, 4:57 pmosborne:
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
April 5, 2009, 6:13 pmRoberto:
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,
April 6, 2009, 8:02 amRoberto
osborne:
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
April 6, 2009, 9:49 amKerry Osborne’s Oracle Blog » Blog Archive Do SQL Plan Baselines Use Hints? - Kerry Osborne’s Oracle Blog:
[...] Oracle Whitepapers / Presentations « SQL Profiles [...]
April 6, 2009, 11:42 pmKerry Osborne’s Oracle Blog » Blog Archive Oracle 11g SQL Plan Management - SQL Plan Baselines - Kerry Osborne’s Oracle Blog:
[...] 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 [...]
April 29, 2009, 3:18 pmSarala:
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’
April 30, 2009, 12:06 pm*
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
Yuval Arnon:
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
April 30, 2009, 12:25 pmosborne:
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
April 30, 2009, 1:58 pmSarala:
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.
April 30, 2009, 2:33 pmPlease 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
osborne:
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
April 30, 2009, 2:50 pmosborne:
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
April 30, 2009, 3:00 pmosborne:
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
April 30, 2009, 3:14 pmosborne:
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
May 13, 2009, 4:47 pmJohn:
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
June 2, 2009, 10:15 amJohn Fak
osborne:
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
June 2, 2009, 10:38 amJohn:
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
June 2, 2009, 12:02 pmJohn:
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
June 2, 2009, 12:22 pmosborne:
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
June 2, 2009, 6:38 pmJohn:
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
June 3, 2009, 8:32 amosborne:
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
June 3, 2009, 9:59 amJohn:
Great.
Thanks again Kerry.
Enjoyed the thread.
John
June 3, 2009, 11:07 amosborne:
No problem John. Good luck!
Kerry
June 3, 2009, 11:17 amRAMACHANDRAN:
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
July 8, 2009, 12:30 pmEnter 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
osborne:
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
July 8, 2009, 8:15 pmKerry Osborne’s Oracle Blog » Blog Archive How to Attach a SQL Profile to a Different Statement - Kerry Osborne’s Oracle Blog:
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.
July 28, 2009, 4:29 pmAurora:
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
August 11, 2009, 3:00 pmosborne:
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
August 11, 2009, 8:19 pmFreedy Mercy:
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
December 10, 2009, 7:09 amosborne:
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
December 10, 2009, 9:48 amFredy Mercy:
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
December 10, 2009, 11:08 amosborne:
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
December 10, 2009, 11:44 amFredy Mercy:
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
December 11, 2009, 1:59 amFredy
Brian Motzer:
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.
January 25, 2010, 6:24 pmosborne:
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
January 25, 2010, 6:53 pmShruti:
Hi
I created SQl profile through EM via advisory central using system .
February 22, 2010, 2:57 pmNow if the same query gets executed via other user profile does not get used. How do i transfer that profile for all the users?
osborne:
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
February 23, 2010, 9:50 amYuvaraj:
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.
July 4, 2010, 7:57 pm# 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
osborne:
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
July 6, 2010, 8:54 am