Re: Do missing binds prevent creation of baseline?

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Tue, 16 Apr 2013 13:59:10 -0500
Message-Id: <70F5700B-31C2-4A58-B85D-580C2FF1FC6D_at_enkitec.com>

Hi Rich,

  I know I don't contribute here much, but I do still lurk a bit. I saw your email and having an interest in this area I thought I'd add a couple of points of clarification.

1. In 11g, baselines use the same hint based mechanism as profiles and patches. That is to say that there is no guarantee with baselines that the plan will reproduced, just as with the other hint based options. There is not a true stored plan with baselines, at least as of 11g. I think it might be a coming attraction of some future release.
2. On bind variables, I presume you are talking about the peeked bind values in other_xml. These are quite often not available. The story I heard was that not all bind variables are peeked. Only bind variables that can be used in selectivity estimates are peeked. For example, variables in the select list are not peeked, nor are ones that are used in a comparison to a complex expression. I haven't looked in detail at this but it is clear that many bind variable values are not stored in other_xml. v$sql_bind_capture should have all bind variables but it's sampled, not recorded for every execution. So I often fall back to set from v$sql_bind_capture if I can't find what I want in other_xml.
3. Not updating 1.8M records per night is probably the the correct fix. :)

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
twitter: https://twitter.com/KerryOracleGuy

On Apr 16, 2013, at 9:24 AM, Rich Jesse wrote:

> Dominic writes:
>

>> How did you load the plans into the sqlset?

>
> BEGIN
> DBMS_SQLTUNE.CREATE_SQLSET(
> sqlset_name => 'STUFF_STUFF_UPDATE_STS',
> description => 'SQL Tuning Set for loading stable plan for SQLID
> 0z9gj42q67wm7 into SQL Plan Baseline');
> END;
>
> DECLARE
> cur sys_refcursor;
> BEGIN
> OPEN cur FOR
> SELECT VALUE(P)
> FROM TABLE(
> dbms_sqltune.select_workload_repository(begin_snap=>122689,
> end_snap=>122691,basic_filter=>'sql_id =
> ''0z9gj42q67wm7''',attribute_list=>'ALL')
> ) p;
> DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STUFF_STUFF_UPDATE_STS',
> populate_cursor=>cur);
> CLOSE cur;
> END;
> /
>
> The STS is created as expected and I can see the optimal plan for the SQL_ID
> listed above.
>

>> 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.

>
> Agreed! There are two items unique to my troublesome statement that
> differentiate it from the (very few) others that I have created baselines
> for. First, it's DML. But that shouldn't really matter, should it?
> Second, not all of the binds are captured, specifically the values being
> updated. This is very curious to me, but I didn't find much information on
> the specific effects of a lack of bind captures on SQL tuning.
>
> As far as profiles go, the problem I have with it for my case is that I
> don't know what caused the new suboptimal plan in the first place. Given
> that, it seems reasonable to me that there is some degree of uncertainty
> that the hinted profile would work in the future. I also believe that a
> baseline locking in a plan forever in this case would be ideal. Since the
> WHERE clause contains exactly every column of the PK, I do not see how any
> other plan involving something other than a unique scan of the PK's index
> could be more optimal. That being said, I'm still pushing for our Apps
> group to look at the job (do we really need to update all 1.8+M rows every
> night?).
>
> Finally, I couldn't afford the time to work on a good answer, so I went the
> brute-force method and recollected stats on the table. The result is that
> it worked, but now of course I still don't know why (stats issue? forced
> reparse?). Which means I won't be able to predict this scenario in the
> future. GRRRRRR.
>
> Thanks much for your ideas! I have a lot to look at...when there's time!
>
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 16 2013 - 20:59:10 CEST