RE: Do missing binds prevent creation of baseline?
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Apr 2013 15:48:51 -0500 (CDT)
Dominic writes:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
