if it also applies to 11
Sokrates, June 19, 2008 - 5:09 pm UTC
..., it seems to be exactly what I was looking for.
Thanks !
Muhammet, January 13, 2009 - 9:35 am UTC
I could not find this method in 10g Release 1. Is there a
method for this release ?
Randomized order of attributes in SQL Profile
Ian, September 21, 2010 - 4:14 am UTC
Tom
I have been using DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF and have recently found that it randomizes the order of the attribute values. This is because they are transported in the staging table as a nested table of type sqlprof_attr. And as we all know nested tables have no order.
So when you unpack them the attributes can come out in any order.
Does this matter? I have seen a SQL Profile (Complete capture of Outline Data from DBMS_XPLAN) that was in the correct order in Development - but when it was unpacked in Production the attributes were in a random order.
So my question is - does the order of the attributes matter? And are the BEGIN_OUTLINE_DATA and END_OUTLINE_DATA significant?
Regards
Ian
September 21, 2010 - 3:50 pm UTC
why do you call one "correct" and the other incorrect? They are just sets of data, unless you query data with an ORDER BY, you cannot reasonably expect the data to come out in any order.
Re Randomized order of attributes in SQL Profile
Ian, September 23, 2010 - 4:04 am UTC
Tom
This is the query I use:
select h.attr_val as outline_hints
from dba_sql_profiles p
,sys.sqlprof$attr h
where p.signature = h.signature
and p.category = h.category
and p.name like ('MY_SQL_PROFILE')
order by h.attr#;
Note the order by.
This is the output on the source system where the SQL Profile was created:
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('optimizer_index_cost_adj' 1)
FIRST_ROWS(10)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T6"@"SEL$1" ("S_ASSET_EMP"."EMP_ID"))
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("S_ASSET"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T5"@"SEL$1" ("S_PROD_CFGVER"."PRODUCT_ID" "S_PROD_CFGVER"."VERSION_NUM" "S_PROD_CFGVER"."CONFLICT_ID"))
INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("S_ASSET_OM"."PAR_ROW_ID" "S_ASSET_OM"."CONFLICT_ID"))
INDEX_RS_ASC(@"SEL$1" "T7"@"SEL$1" ("S_ORG_EXT"."PAR_ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("S_ORG_EXT"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T9"@"SEL$1" ("S_ASSET"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T4"@"SEL$1" ("S_PROD_CFGVER"."PRODUCT_ID" "S_PROD_CFGVER"."VERSION_NUM" "S_PROD_CFGVER"."CONFLICT_ID"))
INDEX_RS_ASC(@"SEL$1" "T10"@"SEL$1" ("S_USER"."PAR_ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T8"@"SEL$1" ("S_PROD_INT"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("S_PROD_INT"."ROW_ID"))
LEADING(@"SEL$1" "T6"@"SEL$1" "T11"@"SEL$1" "T5"@"SEL$1" "T3"@"SEL$1" "T7"@"SEL$1" "T2"@"SEL$1" "T9"@"SEL$1" "T4"@"SEL$1" "T10"@"SEL$1" "T8"@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$1" "T11"@"SEL$1")
USE_NL(@"SEL$1" "T5"@"SEL$1")
USE_NL(@"SEL$1" "T3"@"SEL$1")
USE_NL(@"SEL$1" "T7"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T9"@"SEL$1")
USE_NL(@"SEL$1" "T4"@"SEL$1")
USE_NL(@"SEL$1" "T10"@"SEL$1")
USE_NL(@"SEL$1" "T8"@"SEL$1")
USE_NL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
And this is the output on the target system after export/import using dbms_sqltune:
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
FIRST_ROWS(10)
INDEX_RS_ASC(@"SEL$1" "T6"@"SEL$1" ("S_ASSET_EMP"."EMP_ID"))
INDEX_RS_ASC(@"SEL$1" "T5"@"SEL$1" ("S_PROD_CFGVER"."PRODUCT_ID" "S_PROD_CFGVER"."VERSION_NUM" "S_PROD_CFGVER"."CONFLICT_ID"))
INDEX_RS_ASC(@"SEL$1" "T7"@"SEL$1" ("S_ORG_EXT"."PAR_ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T9"@"SEL$1" ("S_ASSET"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T10"@"SEL$1" ("S_USER"."PAR_ROW_ID"))
USE_NL(@"SEL$1" "T9"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T7"@"SEL$1")
USE_NL(@"SEL$1" "T3"@"SEL$1")
USE_NL(@"SEL$1" "T5"@"SEL$1")
USE_NL(@"SEL$1" "T11"@"SEL$1")
LEADING(@"SEL$1" "T6"@"SEL$1" "T11"@"SEL$1" "T5"@"SEL$1" "T3"@"SEL$1" "T7"@"SEL$1" "T2"@"SEL$1" "T9"@"SEL$1" "T4"@"SEL$1" "T10"@"SEL$1" "T8"@"SEL$1" "T1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("S_PROD_INT"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T8"@"SEL$1" ("S_PROD_INT"."ROW_ID"))
END_OUTLINE_DATA
USE_NL(@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$1" "T8"@"SEL$1")
USE_NL(@"SEL$1" "T10"@"SEL$1")
USE_NL(@"SEL$1" "T4"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T4"@"SEL$1" ("S_PROD_CFGVER"."PRODUCT_ID" "S_PROD_CFGVER"."VERSION_NUM" "S_PROD_CFGVER"."CONFLICT_ID"))
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("S_ORG_EXT"."ROW_ID"))
INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("S_ASSET_OM"."PAR_ROW_ID" "S_ASSET_OM"."CONFLICT_ID"))
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("S_ASSET"."ROW_ID"))
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM('optimizer_index_cost_adj' 1)
Both are ordered by attr#. I am not that daft!
Note the END_OUTLINE_DATA tag halfway down the second profile.
So my original questions still remain:
1) Does the order of the attributes matter?
2) Are the BEGIN_OUTLINE_DATA and END_OUTLINE_DATA tags significant? Or to put it another way - will everything after the END_OUTLINE_DATA tag be ignored?
The reason that I ask is that this particular SQL Profile is being picked up (Appears in SQL_PROFILE column of v$sql) but is not always being honoured.
Regards
Ian
September 23, 2010 - 10:56 am UTC
you are ordering by an internal ID, if you want things sorted alphabetically - sort by what you want to see things sorted by.
no one called you daft, I can only respond to things I see on screen. Clear your mind for a minute (pretend you are not you), then read your review above - the one where you don't show any work, don't say "i used order by", don't give us anything to work with as far as what the query looked like - then you might see how I could easily think "you need order by". That and pretend for a minute that you are me and you easily get 10-20-30 times a week "why is this query returning the data correctly in test but not production" questions whereby the answer is always "because you have NO ORDER BY"...
If the sql profile is being used - ever - then it is ok the way it is formed.
Still not making my self clear. Mea culpa!
Ian, September 23, 2010 - 11:34 am UTC
Sorry - missed the smiley after the "I am not that daft!" comment.
OK - I am ordering by the internal id. My question is (And was) does that internal id order have any bearing on how the CBO uses the profile.
Is the CBO doing a bit like "Read all the attributes in this SQL Profile ordered by attr# and stop* when you get to an END_OUTLINE_DATA attribute"?
If it is then that would explain why our "out of internal id order" SQL Profile works intermittently - not all of the SQL Profile is being read.
Does that make things any clearer? I would put together a test case - but I cannot reproduce with a simple example.
And a 10053 sheds no light on the issue.
*"stop when you get to an END_OUTLINE_DATA attribute" - That reminds me of the joke about programers hunting for Elephants in Africa. Experienced COBOL guys would put a known elephant in Cape Town to make sure their search ended. :-)
Regards
Ian
Got it.
Ian, September 24, 2010 - 5:39 am UTC
Tom
I managed to replicate the issue and now know that the CBO does not care about the order of the attributes of a SQL Profile and ignores the position of the END_OUTLINE_DATA attribute.
Which just leaves me with the problem of the SQL Profile that is sometimes ignored. Same SQL_ID - same exact_matching_signature - two different plans - both using the same SQL Profile.
Thanks anyway.
Regards
Ian
Migrate sql profiles
pranav, July 12, 2012 - 4:35 pm UTC
Hi Tom,
Could you please let me know how to migrate the sql profiles from one database to other without using export/import utilities? Not sure how to write a custom script.
Appreciate all your help.