RE: Do missing binds prevent creation of baseline?
- From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- To: "rjoralist2@xxxxxxxxxxxxxxxxxxxxx" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Apr 2013 21:28:55 +0000
> This is what doesn't work for me. LOAD_PLANS_FROM_SQLSET is what returns
> the message (under trace) about other_xml being null.
How did you load the plans into the sqlset?
> The DBMS_SQLDIAG_INTERNAL API is undocumented, which is a sure bet to it
> being unsupported as well.
I believe that the SQL Patch feature is supported but I can't recall seeing
that documented anywhere.
The fact that the optimizer group has blogged about increases confidence:
https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
https://blogs.oracle.com/optimizer/entry/additional_information_on_sql_patches
I've got a couple of posts on the SQL Patch approach here:
http://orastory.wordpress.com/category/sql-patch/
> Profiles would probably be my last option.
Using
the sql profile approach is perfectly viable approach. Some people
prefer the COE_XFR_SQL_PROFILE approach precisely because it uses the
outline-style hints of a baseline without the enforcement of plan hash
value.
If other_xml is empty - be nice to investigate this a bit
further at some point - the baseline mechansim will be a problem because the
outline_data hints from other_xml are used for the baseline plan and the
plan_hash_2 from other_xml is used as the plan id in the baseline to
enforce that the specific plan is reproducible.
Under the hood,
the COE_XFR_SQL_PROFILE script uses DBMS_SQLTUNE.IMPORT_SQL_PROFILE so
you could get the full set of hints for the PK access and apply them via
a profile.
Also, more on the transfer of plan from one statement to another in a baseline
here:
http://orastory.wordpress.com/2011/10/13/no-need-to-change-source-code-hint-it-using-a-baseline/
Dominic.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
