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

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

    Suresh

    About these ads
    1. No comments yet.
    1. No trackbacks yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    BigData - Oracle-info.com

    Sureshgandhi's Blog : Its time to learn BigData

    Oracle Alchemist

    Steve Karam's Oracle Blog

    ishudba

    The King's .... "Joy of Life"..

    the dba diary !!!

    Naga's Blog : Oracle DBA experiences

    ORACLE-INFO

    Sureshgandhi's Blog: It's all about Oracle..Just Learn,Apply & Share.

    SureshGandhi

    All about Oracle.......

    All about Oracle.......

    DB Optimizer

    database performance, SQL tuning and data visualizatoin

    jarneil

    The thoughts of Jason Arneil

    All Things Oracle

    All about Oracle.......

    Dion Cho - Oracle Performance Storyteller

    We are natural born scientists

    All about Oracle.......

    All about Oracle.......

    All about Oracle.......

    Miladin Modrakovic's Blog: Oraclue

    Oracle internals, debugging and undocumented features

    Coskan's Approach to Oracle

    What I learned about Oracle

    The Arup Nanda Blog

    All about Oracle.......

    OraStory

    Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

    Martins Blog

    Trying to explain complex things in simple terms

    All about Oracle.......

    Oracle database internals by Riyaj

    Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

    All about Oracle.......

    Kevin Closson's Blog: Platforms, Databases and Storage

    Platform, Database and Storage Topics

    Iordan Iotzov's DBA blog

    The opinions here may not necessarily reflect that of my employers - both past and present. The comments left by the reviewers are theirs alone and may not reflect my opinion. None of the advice is warranted to be free of errors and omissions. Please use at your own risk and after thorough testing in your environment.

    Doug's Oracle Blog

    All about Oracle.......

    Oracle related stuff

    All about Oracle.......

    All about Oracle.......

    Charles Hooper's Oracle Notes

    Miscellaneous Random Oracle Topics: Stop, Think, ... Understand

    Richard Foote's Oracle Blog

    Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

    Oracle E-Business Suite Technology

    All about Oracle.......

    Tanel Poder's blog: Responsible data management

    Linux, Oracle, Exadata and Hadoop.

    Oracle DBA - Tips and Techniques

    Knowledge Is Power - Share IT ....

    Guenadi N Jilevski's Oracle BLOG

    Oracle RAC, DG, EBS, DR and HA DBA BLOG

    Laurent Schneider

    All about Oracle.......

    Kerry Osborne's Oracle Blog

    All about Oracle.......

    Hemant's Oracle DBA Blog

    All about Oracle.......

    Oracle Enterprise Manager

    All about Oracle.......

    Structured Data

    Thoughts on: Big Data, Hadoop, Databases, Platform, Performance & Scalability

    Oracle Scratchpad

    Just another Oracle weblog

    Karl Arao's Blog

    Just another weblog about Oracle,Linux,Troubleshooting,Performance,etc..etc..

    The Data Warehouse Insider

    All about Oracle.......

    Online Apps DBA

    One Stop Shop for Apps DBA's

    All about Oracle.......

    Microsoft SQL Server

    All about Oracle.......

    Follow

    Get every new post delivered to your Inbox.

    Join 83 other followers

    %d bloggers like this: