New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

Why is that, I have different execution plans although sql profile has been picked up?

Today I learnt something new, on sql profiles.

Thanks to Kerry Osborne’s blog that let me understand on this stuff

Before going forward have a look at the below plans.

SQL> select * from table(dbms_xplan.display_awr('2pg20pzpc6yb7'));
PLAN_TABLE_OUTPUT-
-----------------------------------------------------------------------------------------------------

SQL_ID 2pg20pzpc6yb7
--------------------
SQL Statement removed intentionally, and it is irrelevant to subject

Plan hash value: 526584108

-----------------------------------------------------------------------------------------------------------------------------
|Id  | Operation				| Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop
|-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			|                      |       |       |    10 (100)|          |       |       |
|   1 |  NESTED LOOPS           		|                      |       |       |	    |          |       |       |
|   2 |   NESTED LOOPS          		|                      |     1 |   287 |    10   (0)| 00:00:01 |       |       |
|   3 |    PARTITION LIST SINGLE		|                      |     3 |   456 |     7   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID	|XXXOBJECT_HDR         |     3 |   456 |     7   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                	|XXXOBJECT_HDR_2       |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|   6 |    PARTITION LIST SINGLE             	|                      |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   7 |     INDEX RANGE SCAN                 	|XXXOBJECT_CONTENT_PK  |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   8 |   TABLE ACCESS BY LOCAL INDEX ROWID  	|XXXOBJECT_CONTENT     |     1 |   135 |     1   (0)| 00:00:01 |     1 |     1 |
|-----------------------------------------------------------------------------------------------------------------------------

Note
-----   
- SQL profile "SYS_SQLPROF_0138294c151e0002" used for this statement



SQL_ID 2pg20pzpc6yb7
--------------------

SQL Statement removed intentionally, and it is irrelevant to subject

Plan hash value:1359745422


----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				| Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop
|----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			|                     |       |       |     6 (100)|          |       |       |
|   1 |  NESTED LOOPS        	         |                     |       |       |		   |          |       |       |
|   2 |   NESTED LOOPS                       	|                     |     4 |  1212 |     6   (0)| 00:00:01 |       |       |
|   3 |		PARTITION LIST SINGLE       |                     |     1 |   168 |     4   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID	|XXXOBJECT_HDR        |     1 |   168 |     4   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                	|XXXOBJECT_HDR_2      |     1 |       |	    3   (0)| 00:00:01 |   KEY |   KEY |
|   6 |    PARTITION LIST SINGLE             	|                     |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   7 |		INDEX RANGE SCAN           |XXXOBJECT_CONTENT_2  |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   8 |   TABLE ACCESS BY LOCAL INDEX ROWID  	|XXXOBJECT_CONTENT    |     4 |   540 |     2   (0)| 00:00:01 |     1 |     1 |
|----------------------------------------------------------------------------------------------------------------------------
Note
-----   
- SQL profile "SYS_SQLPROF_0138294c151e0002" used for this statement

Tables names also edited for safe guard the originality

Okay, what you have observed? (Let me tell you both statements are same using bind variables)

1. Both statements are same but having different plan hash values, means different plans, Absolutely right, the plan hash value 526584108 says its using index XXXOBJECT_CONTENT_PK where in the later plan hash value says its using XXXOBJECT_CONTENT_2 , perfectly right.

But sigh, check the note section, it says SQL profile “SYS_SQLPROF_0138294c151e0002” has been used. 

There you are, that’s the catch,

??? if a SQL profile has been used is not that same plan with same name (object) should be used?

So what happened here?

First observation:-

This profile is not manually created, the name shows SYS_SQLPROF* , means created by Automatic SQL Tuning advisory

Second observation:-

Search google :(, really I have no second thought in searching this, fortunately came across kerry osborne’s 2009 post come to rescue to understand this behavior.

Final things:-

1) First, verify what are the hints that appended to this profile

Using Kerry’s script:-

sql_profile_hints.sql

OPT_ESTIMATE(@"SEL$1", TABLE, "C"@"SEL$1", SCALE_ROWS=0.0536172171)

OPT_ESTIMATE(@"SEL$2", JOIN, "C"@"SEL$1",  SCALE_ROWS=4)

OPT_ESTIMATE(@"SEL$3", INDEX_RANGE_SCAN, "H"@"SEL$3",  SCALE_ROWS=4)

OPTIMIZER_FEATURES_ENABLE(default)

Manipulated hints , Just for your understanding pasted here.

2) Second, the profile Used indirect hints, like OPT_ESTIMATE,

Note:- When sql profiles created automatically, these indirect hints will be placed and they really do not see the namespace (object names) instead go with aliases (here in my case C) and estimated that INDEX_PK would be cheaper to gain the data (in reality there may be many reasons, stats blah blah etc)

3) Third, to use direct hints like INDEX or something else, you have to create the sql profile manually on top of this SYS_SQLPROF**

Create SQL Profile manually.

create_sql_profile.sql

create_sql_profile_awr.sql

As per Kerry Osborne:-

  • 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)  ** but not in our case
  • 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. ** Worked in our case (I will update the output)

    • So when it appeared that SQL Profile has been picked but you have different plans shown, you might have hitting that indirect hints are in place as that’s common design for SQL profiles (most possibly by automatic tuning advisor), we have to still create a manual sql profile which appeared to fix the plan irrespective of estimations that optimizer choose to do.

    Hope this helps

    -Thanks

    Geek DBA

    February 26th, 2013 | Tags: , | Category: Performance Tuning

    Comments are closed.