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> 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 </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)2) Second, the profile Used indirect hints, like OPT_ESTIMATE,
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.
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:-
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.
