RE: Do missing binds prevent creation of baseline?

> 1. If the plan is in AWR then transfer into a baseline using
> DBMS_SQLTUNE.LOAD_SQLSET with DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY then
> sqlset to baseline using DBMS_SPM.LOAD_PLANS_FROM_SQLSET

This is what doesn't work for me. LOAD_PLANS_FROM_SQLSET is what returns the message (under trace) about other_xml being null.

> 2. Run the SQL manually with a hint and then transfer the plan into a
> baseline for the original SQL using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
> (Can be done in a non-prod environment and exported)

Interesting! I hadn't considered that approach, although I suspect that I wouldn't even have to hint it. It's unknown why the PK's index isn't being used as the explain using the known binds will, and a 10053 may not be a viable option for this UPDATE statement in Production.

> 3. Use a SQL Patch using SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH.

The DBMS_SQLDIAG_INTERNAL API is undocumented, which is a sure bet to it being unsupported as well.

> 4. Use a SQL Profile to apply outline style hints via
> COE_XFR_SQL_PROFILE.SQL - See Oracle Support doc id 215187.1

Profiles would probably be my last option. I think the pitfalls of hints are the reason Oracle prefers SPM now.

Thanks much for the suggestions! I'll be looking more into #2...

Rich

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