RE: Do missing binds prevent creation of baseline?
From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Tue, 16 Apr 2013 21:27:54 +0000
Message-ID: <DUB115-W126218A243EF998F90EE0D5A1CD0_at_phx.gbl>
> So "plan" in the context of the article's reference to "SQL Plan Baseline"
> is not really the SQL execution plan viewed in V$SQL_PLAN? And if the
> optimizer is only able to choose between the plans available via baselines,
> wouldn't that absolutely prevent any alteration of the execution path,
> regardless of other changes (e.g. missing index, structure changes, etc.)?
>
> Poking around the data dictionary, are you referring to the COMP_DATA column
> of SYS.SYSOBJ$DATA? I am unable to find a reference to a true stored
> plan... :\
Baseline stores the outline hints from v$sql_plan.other_xml. Plan_id in the underlying sqlobj$* views is the plan_hash_2 seen in v$sql_plan.other_xml.
Date: Tue, 16 Apr 2013 21:27:54 +0000
Message-ID: <DUB115-W126218A243EF998F90EE0D5A1CD0_at_phx.gbl>
> So "plan" in the context of the article's reference to "SQL Plan Baseline"
> is not really the SQL execution plan viewed in V$SQL_PLAN? And if the
> optimizer is only able to choose between the plans available via baselines,
> wouldn't that absolutely prevent any alteration of the execution path,
> regardless of other changes (e.g. missing index, structure changes, etc.)?
>
> Poking around the data dictionary, are you referring to the COMP_DATA column
> of SYS.SYSOBJ$DATA? I am unable to find a reference to a true stored
> plan... :\
Baseline stores the outline hints from v$sql_plan.other_xml. Plan_id in the underlying sqlobj$* views is the plan_hash_2 seen in v$sql_plan.other_xml.
If, at parse time when applying the outline hints from the baseline, the plan_hash_2 is not reproduced, the baseline is rejected.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 16 2013 - 23:27:54 CEST