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.

<font size="2">SQL&gt; 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 &quot;SYS_SQLPROF_0138294c151e0002&quot; 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 &quot;SYS_SQLPROF_0138294c151e0002&quot; used for this statement

</font>

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

    Suresh


    Rs. 150 .Com at GoDaddy.com!

    Leave a Reply