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).

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.

79 Comments

  1. 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/

  2. Greg Rahn says:

    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.

  3. osborne says:

    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

  4. Roberto says:

    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

  5. osborne says:

    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

  6. [...] 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 [...]

  7. Sarala says:

    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

  8. Yuval Arnon says:

    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

  9. osborne says:

    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

  10. Sarala says:

    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

  11. osborne says:

    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

  12. osborne says:

    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

  13. osborne says:

    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

  14. osborne says:

    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

  15. John says:

    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

  16. osborne says:

    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

  17. John says:

    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

  18. John says:

    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

  19. osborne says:

    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

  20. John says:

    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

  21. osborne says:

    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

  22. John says:

    Great.

    Thanks again Kerry.
    Enjoyed the thread.

    John

  23. osborne says:

    No problem John. Good luck!

    Kerry

  24. RAMACHANDRAN says:

    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

  25. osborne says:

    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

  26. 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.

  27. Aurora says:

    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

  28. osborne says:

    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 feature
    

    Try it without the bulk collect line and see what happens. What version are you on by the way?

    Kerry

  29. Freedy Mercy says:

    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

  30. osborne says:

    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

  31. Fredy Mercy says:

    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

  32. osborne says:

    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

  33. Fredy Mercy says:

    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

  34. Brian Motzer says:

    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.

    TIMESTAMP       #PLANS                     #EXEC        ROWS_PROC    DISKREADS        GETS/EXEC        IOWAIT   SECS/EXEC    TOTSECS
    --------------- --------------- ---------------- ---------------- ------------ ---------------- ------------- ----------- ----------
    24-JAN-10 19:00 1                             12            4,111    1,236,962          141,876      28967057      109.34       1312
    24-JAN-10 20:00 1                             39           17,606    3,896,737          137,358      18416723      138.30       5394
    24-JAN-10 21:00 1                              5                0      388,688           81,958       1040703       68.50        342
    24-JAN-10 22:00 1                              8               17      675,832          116,810      16315202       81.39        651
    25-JAN-10 00:00 1                             24           12,503    2,419,987          144,586      20789949      104.10       2498
    25-JAN-10 04:00 1                              8                1      661,590          105,532       4596278       87.86        703
    25-JAN-10 05:00 1                             26            1,234    2,536,143          124,302      12789703      109.04       2835
    25-JAN-10 06:00 1                             24           38,856    2,655,137          239,436      28886629      126.63       3039
    25-JAN-10 07:00 1                              5               23      442,966          238,767       6298353       75.51        378
    25-JAN-10 08:00 1                              2              340      357,109          139,357      51294885      104.54        209
    25-JAN-10 10:00 1 (plan change)               27              446      130,702       81,869,479      79355932      356.71       9631
    25-JAN-10 11:00 1                              7            1,268    1,765,948      570,996,611    2281600856    3,380.09      23661
    25-JAN-10 12:00 1                              7              934    1,506,704      500,435,949    1813018245    6,662.60      46638
    25-JAN-10 13:00 1                              3            1,493       65,852    1,283,852,131      99695415   16,010.25      48031
    25-JAN-10 14:00 1                              1            1,247       34,879    4,227,746,967      19951869   48,673.53      48674
    25-JAN-10 15:00 1                              5            2,020       63,546      800,010,218     100884565    9,538.32      47692
    25-JAN-10 16:00 1 (plan change)                1                1      247,574          141,598      44470601      127.12        127
    
  35. osborne says:

    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

  36. Shruti says:

    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?

  37. osborne says:

    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

  38. Yuvaraj says:

    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

  39. osborne says:

    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

  40. Erwin says:

    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

  41. osborne says:

    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

  42. Amit says:

    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

  43. osborne says:

    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

  44. osborne says:

    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.

  45. Raja says:

    Can I know if there is any view available to check whether a SQL_profile being used or not?

  46. osborne says:

    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

  47. Eric Harper says:

    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

  48. osborne says:

    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!

  49. [...] 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 [...]

  50. Marcel says:

    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.

  51. Paul says:

    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

  52. business intelligence…

    [...]Kerry Osborne’s Oracle Blog » Blog Archive » SQL Profiles – Kerry Osborne’s Oracle Blog[...]…

  53. [...] decided to use Kerry Osborne’s method of creating a SQL profile from the AWR repository to force Oracle to use a known good [...]

  54. [...] 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 [...]

  55. joshua says:

    Hi Kerry,
    Was wondering if you have used the plan_table to create sql profile before

  56. osborne says:

    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

  57. [...] 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 [...]

  58. tarun says:

    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

  59. osborne says:

    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

    • tarun says:

      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

  60. osborne says:

    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.

  61. Lynn Sattler says:

    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.

  62. osborne says:

    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

  63. 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

  64. Mario Rocha says:

    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?

  65. osborne says:

    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

  66. Bill says:

    Great stuff, added more to my understanding of profiles. Cool

  67. Syltrem says:

    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 !

  68. osborne says:

    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

  69. max says:

    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″)

  70. osborne says:

    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

  71. max says:

    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.

  72. miguel says:

    That coe.sql save my day!!

    Thanks Kerry

  73. Girish Sharma says:

    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

  74. osborne says:

    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

Leave a Reply