RE: Do missing binds prevent creation of baseline?

> 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',

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